Twitter: raymondcamden

Address: Lafayette, LA, USA

A database utility class for PhoneGap

01-26-2012 8,465 views Mobile, Development 7 Comments

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:

view plain print about

Where your XML would look like so:

view plain print about
3create table foo if not exists foo(....)
6create table moo if not exists foo(....)

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:

view plain print about
1dbController.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:

view plain print about

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

view plain print about
1var DBController = function() {
3    var db,success,failure;
5    return {
7        init:function(name,importscript,successHandler)    {
8            //todo - allow for version
9            db = window.openDatabase(name,"1.0",name,100000);            
10            if(typeof importscript !== "undefined") {
11                console.log("being asked to run a script");
12                if(typeof successHandler === "undefined") throw "Invalid call - must pass success handler when importing data";
13                this.executeBatch(importscript,successHandler);
14            }
15        },
17        executeBatch:function(path,successHandler,errorHandler) {
18            success=successHandler;
19            failure=errorHandler;
21            $.get(path, {}, this.gotFile, "xml");
22        },
24        //sql, successHandler, errorHandler are required
25        executeSql:function(sql,args,successHandler,errorHandler) {
26            console.log('going to run '+sql+ ' '+arguments.length);
27            //Don't like this - but way to make args be optional and in 2nd place
28            if(arguments.length == 3) {
29                successHandler = arguments[1];
30                errorHandler = arguments[2];
31                args = [];
32            }
33            db.transaction(
34                function(tx) { tx.executeSql(sql,args,function(tx,res) {
35                    //todo - figure out fraking scoping rules and why line below didnt work, nor this.X
36                    //res = translateResultSet(res);
37                    var result = [];
38                    for(var i=0; i<res.rows.length; i++) {
39                        result.push(res.rows.item(i));
40                    }
41                    successHandler(result);
42                })}
43            , errorHandler)    
44        },
47        gotFile:function(doc) {
48            var statements = [];
49            var statementNodes=doc.getElementsByTagName("statement");
50            for(var i=0; i<statementNodes.length; i++) {
51                statements.push(statementNodes[i].textContent);
52            }
53            if(statements.length) {
54                db.transaction(function(tx) {
55                    //do nothing
56                    for(var i=0;i<statements.length;i++) {
57                        tx.executeSql(statements[i]);
58                    }
59                }, failure,success);
60            }
61        },
63        translateResultSet:function(res) {
64            var result = [];
65            for(var i=0; i<res.rows.length; i++) {
66                result.push(res.rows.item(i));
67            }
68            return result;
70        }
72    }


These comments will soon be imported into Disqus. To add a comment, use Disqus above.
  • Mikel #
    Commented on 01-31-2012 at 6:46 AM
    This looks like a really cool way to ship your app with some data. Thanks!
  • Kelly Jones #
    Commented on 02-24-2012 at 12: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
  • Artem #
    Commented on 05-15-2012 at 2: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
  • Commented on 05-15-2012 at 3:00 PM
    In the example above (first code block), you see:


    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.
  • Artem #
    Commented on 05-15-2012 at 3:23 PM
    Thanks Raymond! You are the best!

    May the force be with you! (c)
  • Peter Andres #
    Commented on 11-16-2012 at 2:05 AM
    i've got a question: Will the database automatically be stored somewhere? If yes, where?
  • Commented on 11-16-2012 at 10:57 AM
    It is stored in the file system automatically.