Thursday, February 16, 2012

Where's My Data? SQLite via PhoneGap

The Perceived Behavior
While writing an app, I was syncing massive amounts of data down from a web service using JSONP. As the data comes in (in chunks), I write it out to the SQLite database on the device. With larger data sets I started to notice an odd behavior whereby after performing a large number of tx.executeSql() statements in a row, the data that should have been there was not. In one instance, I ran 700+ inserts and saw zero records in the database table when done.

The Issue
When running a massive number of tx.executeSql() statements as I was, you essentially begin a queue in the framework. Instead of immediately processing what you are sending in, the system seems to take a step back and say, "I will wait until you are done before I do this." So, if you are queuing up a bunch of inserts unknowingly and then you think that you are good to move on to a new page, you might experience the behavior I was whereby those records never make it to the database table.

What No One Tells You
The good news is that there is an easy fix to this that I implemented successfully. Every kind of tx.executeSql() will take a success and failure callback as a parameter. While you only see examples of this in SELECT type statements, it is equally imperative that you include those callbacks in INSERT, DELETE, and UPDATE statements as well.

The callback piece is fine and well, but that still doesn't solve the issue completely. What we really need is a way to track the number of database operations we have called and compare that against the number of success or failure callbacks we get to see if there are any outstanding calls that haven't processed yet. Again, leaving before they are all processed will likely result in data loss.

My solution is to use a simple variable that increments (++) each time a database operation call is made and decrements (--) every time the framework calls back to tell us it completed one. Periodically, then, we chef to see if the number has leveled out to 0 again - meaning all the calls have processed - and then can move on. Some might recognize this as having a close similarity to reference counting.

So, you would take the normal example of this:

function doInserts() {
   for(var i=0; i<50; i++) {
       tx.executeSql("INSERT INTO mytable (thing) VALUES (" + i + ")");

And turn it into:

var expectedDBCallbacks = 0;
var dbCheckIntervalId = 0;

function doInserts() {
   for(var i=0; i<50; i++) {
       tx.executeSql("INSERT INTO mytable (thing) VALUES (" + i + ")", [], successInsert, failureInsert);



   dbCheckIntervalId = setInterval("dbCheck()", 1000);

function successInsert() {

function failureInsert(e) {

function dbCheck() {
   if(expectedDBCallbacks == 0) {

As you can hopefully tell from the code, I would call doInserts() to initiate the process. After looping through the for loop (causing a queue of transactions), I create an interval timer using setInterval to check on the number of outstanding database requests and store the handle to that interval timer in a global variable (dbCheckIntervalId). Once zero (0) is hit, the timer self-destructs with clearInterval and then is ready to move on - demonstrated by calling allDone().

You could, alternatively, make a call to dbCheck() on each success or failure callback and avoid using the interval timer altogether. I set mine this way because I am running highly asynchronously and transactions are happening all over the place and this gives me one easy place to monitor what is going on.

I hope this code at least serves as inspiration for your own solution should you find yourself experiencing the same type of behavior in your SQLite calls. If you have a question or comment, I am always open to hear them.
Post a Comment

About Me

My photo

Firecracker, father, friend. Honorable, humorist, heathen. Performer, programmer, producer. Seeking, social, sarcastic. Loyal, logical, lasting. Bold, belying, benevolent. Gamer, genuine, grinning. Reader, redeeming, ridiculous.

On and off blogger, film producer, and programmer. Keurig addict. Frequent moviegoer. 

Voice Comments