I've done a few projects now that make use of PhoneGap's database support. Like most things in PhoneGap, it just plain works. But I've encountered a few things I thought could be done a bit easier, so I've built a simple utility class for my future projects. I thought I'd share it with folks and see if it would be useful for others.

My utility library has the following features:

executeBatch: Given a file path (via URL), you can have PhoneGap read in an XML file that contains a set of SQL commands. So for example:

myDbController.executeBatch("sql/createtables.xml",successHandler,errHandler);

Where your XML would look like so:

<sql> <statement> create table foo if not exists foo(....) </statement> <statement> create table moo if not exists foo(....) </statement> </sql>

This was done to support the fact that unlike Adobe AIR, the SQLite support in PhoneGap doesn't allow you to ship a pre-populated database. (Although it is possible via a workaround: Prepopulate SQLite DataBase in PhoneGap Application.) The syntax for this was based on work from my fellow evangelist, Christope Coenraets.

executeSql: As you can guess, this simply wraps up executing SQL. While PhoneGap doesn't make this necessarily hard, I found the API a bit awkward in terms of all the callbacks you had to use. ("All" sounds like a lot - it's more like two - but you get the idea.) So for example:

dbController.executeSql("select * from notes", gotNote, errHandler);

The other nice thing this will do is automatically take the result set and return it as a simple array of objects. Again, it's not difficult to work with the normal result set, this is just a bit simpler.

Finally, the class has a simple init() handler that sets up your connections for you. What makes it nice is that it can also automatically call your batch scripts for you. So for example:

dbController.init("main","data/seed.xml",dbReady);

The code is below and is free to use by anyone.

var DBController = function() {

var db,success,failure;

return {

init:function(name,importscript,successHandler) { //todo - allow for version db = window.openDatabase(name,"1.0",name,100000); if(typeof importscript !== "undefined") { console.log("being asked to run a script"); if(typeof successHandler === "undefined") throw "Invalid call - must pass success handler when importing data"; this.executeBatch(importscript,successHandler); } },

executeBatch:function(path,successHandler,errorHandler) { success=successHandler; failure=errorHandler;

$.get(path, {}, this.gotFile, "xml"); },

//sql, successHandler, errorHandler are required executeSql:function(sql,args,successHandler,errorHandler) { console.log('going to run '+sql+ ' '+arguments.length); //Don't like this - but way to make args be optional and in 2nd place if(arguments.length == 3) { successHandler = arguments[1]; errorHandler = arguments[2]; args = []; } db.transaction( function(tx) { tx.executeSql(sql,args,function(tx,res) { //todo - figure out fraking scoping rules and why line below didnt work, nor this.X //res = translateResultSet(res); var result = []; for(var i=0; i<res.rows.length; i++) { result.push(res.rows.item(i)); } successHandler(result); })} , errorHandler) },

gotFile:function(doc) { var statements = []; var statementNodes=doc.getElementsByTagName("statement"); for(var i=0; i<statementNodes.length; i++) { statements.push(statementNodes[i].textContent); } if(statements.length) { db.transaction(function(tx) { //do nothing for(var i=0;i<statements.length;i++) { tx.executeSql(statements[i]); } }, failure,success); } },

translateResultSet:function(res) { var result = []; for(var i=0; i<res.rows.length; i++) { result.push(res.rows.item(i)); } return result;

}

}

};