Random thoughts & observations

From the mundane to the profound and everything in between here’s what’s rocking our world

Performing batch transactions in WebSQL on iOS & Android

Posted: April 7, 2016
Written by: Saints At Play
Category: HTML5

Despite being a deprecated standard and with certain developer resistance to using WebSQL we still find it to be a fantastic database solution for storing data cross-platform on both iOS & Android.

With a recent App we were developing we needed to be able to run batch transactions on inserting arrays of data into the SQLite database tables. Our solution for iOS worked perfectly but we didn't realise this would NOT work on Android (but fortunately found a workaround to accomplishing the same goal).

Let's have a look at the iOS solution and find out why.

The iOS solution

db.transaction(
  function(transaction)
  {
    var i,
        k,
        sql    = ' INSERT INTO yourAccount(id, firstName, lastName, dateOfBirth, maritalStatus, dateCreated, timestamp) VALUES';

    for(i = 0; i < data.length; i++)
    {
      sql      = sql + '(null, ?, ?, ?, ?, ?, ?)';

      if(i < (data.length - 1))
      {
        sql    = sql + ',';
      }
    }

    var params = [];
    for(k = 0; k < data.length; k++)
    {
       params.push(data[k].firstName);
       params.push(data[k].lastName);
       params.push(data[k].dateOfBirth);
       params.push(data[k].maritalStatus);
       params.push(data[k].dateCreated);
       params.push(data[k].timestamp);
    }
           
    transaction.executeSql(
      sql, 
      params,               
      HandleTransactionSuccess,                           
      HandleTransactionError
    );
  }
);

This works really well as iOS will allow multiple inserts in one single transaction. So in the above we build up the SQL statement for the fields and placeholder data as a concatenated string determined by the length of the data to be inserted. Following from this we then iterate through the data pushing each iterated value into an array which is then, along with the concatenated string of fields and placeholder values, called in the executeSql method shortly after.

The downside to this approach?

WebSQL will only allow up to 500 values to be inserted in any one single transaction.

This means that, with datasets in excess of 500 rows of data, you will need to 'chunk' the data being parsed (break up the total quantity of data into smaller packets to be passed into each transaction).

The above highly optimised solution fails on Android however as the WebSQL implementation on that platform does not support multiple inserts from a single transaction.

So we need to take a different approach with bulk WebSQL transactions on Android.

The Android solution

db.transaction(
  function(transaction)
  {
    var i,
        k,
        sql  = ' INSERT INTO yourAccount(id, firstName, lastName, dateOfBirth, maritalStatus, dateCreated, timestamp) VALUES (null, ?, ?, ?, ?, ?, ?)';

    for(k = 0; k < data.length; k++)
    {
      transaction.executeSql(
        sql,
        [
          data[k].firstName,
          data[k].lastName,
          data[k].dateOfBirth,
          data[k].maritalStatus,
          data[k].dateCreated,
          data[k].timestamp
        ],
        HandleTransactionSuccess,                           
        HandleTransactionError
      );
    }
  }
);

As Android (at least from what we've seen when publishing via a Cordova/Ionic based App) won't allow multiple inserts in one transaction we are forced to use a loop to iterate through our array of data and insert each array item into the database table one iteration at a time.A little more longwinded from a resource point of view but it gets the job done and, ultimately, that's all that matters.

We didn't notice any performance hit with inserting 120 items of data into the WebSQL table via this method but the larger the dataset (and the larger the volume of data contained within each record) the higher the probability there will be some resource lag with the above method.

It sucks that Android won't allow multiple inserts from a single WebSQL transaction but at least we have 2 different approaches to achieving the same end result; one for iOS and one for Android.

« Return to Posts

Comments

There are no comments

Posting comments after three months has been disabled.