A database utility class for PhoneGap

This post is more than 2 years old.

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&lt;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&lt;statementNodes.length; i++) {
			statements.push(statementNodes[i].textContent);
		}
		if(statements.length) {
			db.transaction(function(tx) {
				//do nothing
				for(var i=0;i&lt;statements.length;i++) {
					tx.executeSql(statements[i]);
				}
			}, failure,success);
		}
	},
	
	translateResultSet:function(res) {
		var result = [];
		for(var i=0; i&lt;res.rows.length; i++) {
			result.push(res.rows.item(i));
		}
		return result;
		
	}
		
}

};

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by Mikel posted on 1/31/2012 at 5:46 PM

This looks like a really cool way to ship your app with some data. Thanks!

Comment 2 by Kelly Jones posted on 2/24/2012 at 11:34 AM

Fantastic post. Here’s a great tool that lets you build any type of database apps for web and mobile fast and without coding http://www.caspio.com/

Comment 3 by Artem posted on 5/15/2012 at 11:57 PM

Hi Raymond,

Thanks a lot for this solution! I've spent days looking for it.

One question though, where should I put my xml file? I'm not sure I completely understand which folder that should be.

Thanks in advance for your help!

- Artem

Comment 4 by Raymond Camden posted on 5/16/2012 at 12:00 AM

In the example above (first code block), you see:

sql/createtables.xml

This is relative from the file calling it - typically your index.html file for your PhoneGap project.

I recommend a subdirectory. You can call it anything you want.

Comment 5 by Artem posted on 5/16/2012 at 12:23 AM

Thanks Raymond! You are the best!

May the force be with you! (c)

Comment 6 by Peter Andres posted on 11/16/2012 at 1:05 PM

Hi,
i've got a question: Will the database automatically be stored somewhere? If yes, where?

Comment 7 by Raymond Camden posted on 11/16/2012 at 9:57 PM

It is stored in the file system automatically.