Working with WebSQL in an HTML5 mobile app

I wrote some days ago that I now work on a BlackBerry 10 app using BlackBerry WebWorks, which is basically Apache Cordova (Phonegap).

The app needs to read data from JSON sources and store it locally, so that the app works offline. So I need local storage, and although the WebSQL specification came to an end, it’s supported in WebWorks, Chrome and other platforms.

Working with WebSQL is relatively straightforward because in the end it’s the well known SQLite in the backend. But, you have to deal with callbacks. For example, to write stuff you’re using this kind of code:

// size in bytes, for example 1024*1024*1
// params may be an array of data for the sql statement
var size = 1024*1024*1;
var db = openDatabase("mydb", '1.0', "mydb", size);
var f = function(tx) {
  tx.executeSql(sql, params, function(transaction, result) {
	// success, do something with the result
  }, function(tx, e) {
     // failure, error is in e.message
  });
};
db.transaction(f);	

So there are two callbacks, one in case of success and one in case of error. If you want to execute multiple statements at once, you can put them all into one transaction. In that case you have to do some work to handle the results and errors. Something like this:

f = function(tx) {
	var hasError = false;
	var errors = [];
	var results = [];
	_.each(sql, function(singlesql) {
		tx.executeSql(singlesql, params, function(transaction, result) {
			results.push(result);						
		}, function(tx, e) {
			// failure
			hasError = true;
			errors.push(e.message);						
		});	
	});
	// do something with the result
};

(Note: _.each() is a method of underscore.js framework.)

Unfortunately this does not work reliable. Do you spot the error?

The function iterates though an array of sql statements, and each statement is being executed and in the result callback the result of that statement is added to a result array.

But what happens if you want to use the result array right after the _.each() loop? You’re using the result array before all the callbacks from the sql statements has been executed! So it may be null or contains only some entries, but not all.

You need to work on the results only when all sql statements have been executed:

f = function(tx) {
	var hasError = false;
	var errors = [];
	var results = [];
	var len = sql.length;
	for (var i = 0; i < len; i++) {
		tx.executeSql(sql[i], params, function(transaction, result) {
			results.push(result);
			if (i == (len-1)) {
				// do something with the result HERE!
			}			
		}, function(tx, e) {
			// failure
			hasError = true;
			errors.push(e.message);						
		});	
	};
};

In a real world app that may get cumbersome when you do lot of work with WebSQL. Therefore I started to write a framework, just like I always do when things get complicated 🙂

What about this code:

b = new JBUDatabase("mydb");
// create table if it does not exist yet
db.createTable("tickets", [ {
	name : 'id',
	type : 'TEXT'
}, {
	name : 'title',
	type : 'TEXT'
} ]);
// create a demo array of data for 100 tickets
var data = [];
for (var i = 1; i < 100; i++) {
	data.push(["ID "+i,"ticket title "+i])
}
// write the data to the WebSQL database
db.writeEntries("tickets", data);

All the WebSQL stuff packaged in a simple to use API. Much more clear, isn't it?


Comments are closed.