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.


Raymond Camden said...

Um, why are you doing it this way as opposed to one transaction? You can put all your INSERTS in that and simply run a successhandler at the end of the transaction.

Matthew Ray said...

So, you are saying you would keep this method, but instead of doing counts, use the built-in transaction-level success or failure callbacks, right?

Very valid observation and my only defense is this: in experimenting with that transaction-level callback in past versions of PhoneGap, I have seen some very strange behavior (as others have reported) such as error callbacks even when the transaction has succeeded.

Admittedly, I haven't done any testing with this since then (and it has been a long time) so this is great advice.

Raymond Camden said...

I've not seen bad results like that - I _have_ had issues in the beginning wrapping my head around stuff. Managing the callbacks is a bit complex so every time I use this feature I take it real slow. ;)

Matthew Ray said...

Very well-said, sir. Asynchronous operation, as a whole, is a tough concept to get your head around - especially for those coming from a more linear background where you get a blocker until it executes.

Will Greenberg said...

For what it's worth, I ran into an identical issue as this in my Phonegap application, and I independently came upon the exact same solution. At the time, I thought it was an ugly and kloodgy way of handling things, but forcing blocking on these asynchronous calls was necessary at the time, so I saw it as a required evil.

At the very least, if this is a bad idea, you're not alone in its creation :P

Matt Ray said...

Ha! Great to hear that great minds (mine is arguable) think alike here. Thanks for commenting!

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