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
    

var tags = [adobe air, javascript];

• share this post:
• 
• 
• 
• 
• 
•