Never been to TextSnippets before?

Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world (or not, you can keep them private!)

About this user

« Newer Snippets
Older Snippets »
1 total  XML / RSS feed 

Sqlite helper library for Mozilla

var $sqlite = {
        storageService: [],
        mDBConn: [],
        
        _initService : function(file){
                var db = Components.classes["@mozilla.org/file/directory_service;1"].getService(Components.interfaces.nsIProperties).get("ProfD", Components.interfaces.nsIFile);
                db.append(file);
                this.storageService[file] = Components.classes["@mozilla.org/storage/service;1"].getService(Components.interfaces.mozIStorageService);
                this.mDBConn[file] = (this.storageService[file]).openDatabase(db);
                        
        },
        
        select : function(file,sql,param){
                if (this.storageService[file]== undefined){
                    this._initService(file);
                }
                var ourTransaction = false;
                if ((this.mDBConn[file]).transactionInProgress){
                        ourTransaction = true;
                        (this.mDBConn[file]).beginTransactionAs((this.mDBConn[file]).TRANSACTION_DEFERRED);
                }
                var statement = (this.mDBConn[file]).createStatement(sql);
                if (param){
                        for (var m=2, arg=null; arg=arguments[m]; m++) {
                                statement.bindUTF8StringParameter(m-2, arg);
                        }
                }
                try{
                        var dataset = [];
                        while (statement.executeStep()){
                                var row = [];
                                for(var i=0,k=statement.columnCount; i<k; i++){
                                        row[statement.getColumnName(i)] = statement.getUTF8String(i);
                                }
                                dataset.push(row);
                        }
                        // return dataset;  
                }
                finally {
                        statement.reset();
                }
                if (ourTransaction){
                        (this.mDBConn[file]).commitTransaction();
                }
        return dataset;      
        },
        
        
        cmd : function(file,sql,param){
                if (this.storageService[file] == undefined){
                            this._initService(file);
                }
                var ourTransaction = false;
                if ((this.mDBConn[file]).transactionInProgress){
                        ourTransaction = true;
                        (this.mDBConn[file]).beginTransactionAs((this.mDBConn[file]).TRANSACTION_DEFERRED);
                }
                var statement = (this.mDBConn[file]).createStatement(sql);
                if (param){
                        for (var m=2, arg=null; arg=arguments[m]; m++) {
                                statement.bindUTF8StringParameter(m-2, arg);
                        }
                }
                try{
                        statement.execute();
                }
                finally {
                        statement.reset();
                }
                if (ourTransaction){
                        (this.mDBConn[file]).commitTransaction();
                }
        }    

}

Usage:
<script language="javascript">
//some variables :
//assuming db file is in user's profile directory:
var myDBFile = 'mydb.sqlite';

// some example SQL queries:
var myCreateDBQuery = 'CREATE TABLE IF NOT EXISTS mybooks_tbl (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT);';

var myInsertQuery = 'INSERT INTO mybooks_tbl(title) VALUES("book title1");';
var myInsertQueryParameterized = 'INSERT INTO mybooks_tbl(title) VALUES(?1);';

var mySelectQuery = 'SELECT id,title FROM mybooks_tbl';
var mySelectQueryParameterized = 'SELECT id,title FROM mybooks_tbl WHERE id = ?1 AND title = ?2';



// For anything other than SELECT statement, use $sqlite.cmd() :
 
// creating a DB:
function test_createDB(){
        $sqlite.cmd(myDBFile,myCreateDBQuery);
}

// simple add record:
function test_addRecord(){
        $sqlite.cmd(myDBFile,myInsertQuery);
}

// parameterized add record, add parameters as much as you want:        
function test_addRecordParameterized(){
        // for example, adding 3 records:
        for(var i = 1 ; i < 4; i++){
                $sqlite.cmd(myDBFile,myInsertQueryParameterized,'book title'+i+'');
        }
}

// for SELECT, use $sqlite.select() :

// simple select:
function test_Select(){
        var myArray1 = $sqlite.select(myDBFile,mySelectQuery);
        // Now you can loop through the array:
        for(var j=0;j'title']);
        }
}

// select with bound parameters, add parameters as much as you want:
function test_SelectParameterized(){
        var myArray1 = $sqlite.select(myDBFile,mySelectQueryParameterized,'1','book title1');
        // Now you can loop through the array:
        for(var j=0;j'title']);
        }
}

Create DB | 
Simple Add Record | 
Parameterized Add Record | 
Simple Select | 
Parameterized Select |
« Newer Snippets
Older Snippets »
1 total  XML / RSS feed