Synchronous SQLite Singleton

SQLite is already pretty easy to use, but here's a simple abstraction that makes it even easier to interact with a SQLite database. I developed this to reuse in my own Adobe AIR apps, because AIR uses SQLite as its native databse implementation.

DB = function() {
    var database = air.File.applicationStorageDirectory.resolvePath('database.db');
    var exec = function(query, param) {
        try {
            c.open(database);
            s.clearParameters();
            if (typeof s.text == 'string') {
                s.text = query;
                if (typeof param == 'object') {
                    for (var i in param) {
                        s.parameters[':' + i] = param[i];
                    }
                }
                s.execute();
            }
            return s.getResult();
        } catch (e) {
            air.trace('Database error: ' + e.message);
        } finally {
            c.close();
        }
    };
    var c = new air.SQLConnection();
    var s = new air.SQLStatement();
    s.sqlConnection = c;
    return {
        exec : exec,
        init : function(filename) {
            filename = filename || 'database.db';
            database = air.File.applicationStorageDirectory.resolvePath(filename);
        }
    };
}();
    // here's how to use it
    var employees = DB.exec("SELECT * FROM employees").data;
    for (var i = 0; i < employees.length; i++) {
        alert(employees.firstName + " " + employees.lastName);
    }
    
    // if you're not using SELECT, you don't have to use an assignment
    DB.exec("DELETE FROM employees");
    
    // the second argument is an object for binding SQL statement parameters
    var insert = DB.exec("INSERT INTO employees VALUES (:id, :firstName, :lastName)", {
        id        : 123,
        firstName : 'John',
        lastName  : 'Smith'
    });
    
    // determine how many rows were affected by the last SQL statement
    if (insert.rowsAffected === 0) {
        air.trace("Database error!");
    } else {
        air.trace("Saved!");
    }
    
    // optionally, you can initialize (or REinitialize) it with a new database name
    DB.init('new-filename.db'); // future queries will act on this database
    
Tags




blog comments powered by Disqus
search blog
random posts
  • HTML-XHTML-Definitive-Guide-6th
  • Deploying-HTML5-Aditya-Yadav
categories & tags
about hb stone

I'm a Front-End Engineer at Yahoo! working on the Mail and Messenger teams. I blog about web design and development topics including accessibility, usability, performance, and developing HTML / CSS / JavaScript applications on Appcelerator Titanium and Adobe AIR.

If you're a web developer, you might enjoy Jelo, my JavaScript library.

@hbstone follows:
@hbstone tweets:
  • Building-iPhone-Applications-Titanium-Appcelerator
  • Rocket-Surgery-Made-Easy-Yourself
copyright

All original work on this site is covered by a Creative Commons Attribution 3.0 license unless otherwise specified.

You may share or use any code or images from this site in any manner, for free, so long as reasonable effort has been made to give credit where due.

The views expressed in the posts and comments on this blog do not necessarily reflect the views of Yahoo!