UPDATE statement not always successful - guidance needed

[bengrah-miller]

Hi there,

We have a database table that has a column called image1 - this contains a base 64 string of an image. We then upload the string to a third-party service which creates an image and passes back a URL. We then update image1 with the URL.

This seems to work about 70% of the time, but so far I've not been able to account for the times that the column is not updated. This is the function I wrote:

updatePhotoURL(dbid, photoUrl) {
    console.group("### updatePhotoURL() called, dbid, photoUrl:", dbid, photoUrl);

    let sqlQuery = 'UPDATE table SET image1=? WHERE dbid=?'

    return new Observable<object>((obs) => {
      this.openDatabase()
        .flatMap((db) => {
          return Observable.fromPromise(db.executeSql(sqlQuery, [photoUrl, dbid]))
        })
        .catch((error) => {
          console.info("### error")
          console.table(error)

          let extra = `dbid: ${dbid == undefined ? 'undefined' : dbid}, `

          if (photoUrl == undefined) {
            extra += `photoUrl: undefined`
          } else {
            extra += `photoUrl: ${photoUrl.length > 0 ? photoUrl.substr(0, 20) + "..." : ""}`
          }

          obs.error({ r: "updatePhotoURL() catch()", s: error.code, m: error.message, e: extra })
          obs.complete()

          return error;
        })
        .map((dbUpdate) => {
          if (dbUpdate.rowsAffected == 0) {

            let extra = `dbid: ${dbid == undefined ? 'undefined' : dbid},`

            if (photoUrl == undefined) {
              extra += `photoUrl: undefined`
            } else {
              extra += `photoUrl: ${photoUrl.length > 0 ? photoUrl.substr(0, 20) + "..." : ""}`
            }

            obs.error({ r: "updatePhotoURL() function", s: 0, m: 'Could not update photo URL for this snag in storage', e: extra })

          } else {
            obs.next(dbUpdate)
          }

          obs.complete()

          return dbUpdate
        })
        .subscribe((success) => {

          console.groupEnd()

        });
    });
  }

As I said earlier, this works a lot of the time, but occasionally we're seeing the database is not updating, and I don't understand why. I've added a catch() to the SQL statement execution so if there is any sort of error then we're catching it, but I never seem to get anything from this. After that I've got a map() function which checks to see if the value of rowsAffected equals 0 - so if for some reason we're not seeing an error when executing the SQL statement, then we trigger an error because the database row wasn't affected. I am able to trigger the map() function but it doesn't help me get to the root cause.

I can confirm that the dbid of the row does exist, and the value of photoUrl is definitely a string URL. Is there anything else generally that I can check or should look out for?

Thanks,
bengrah

[brody4hire]

This plugin's API does not return promises. I highly recommend you make or use a wrapper to resolve this.

[bengrah-miller]

This plugin's API does not return promises. I highly recommend you make or use a wrapper to resolve this.

Hi @brodybits - we use Observables (e.g. rxjs) extensively in our apps, which has a fromPomise() function so we can include Promises in our Observable chains. I don't believe that's the issue specifically, as we're using this same approach in many different places within our apps and it works fine.