This is my Javascript Class for HTML5 Databases connection to SQLite. I use this to build mobile application, including iOS, Android, and BlackBerry. I wrote this class to do the instance query to SQLite, and also some function for delete, insert, and update database records and table creation and deletion.
Here is my class:
/*************************************/
/* Helman, heldes.com */
/* helman at heldes dot com */
/* sqlitedb.js */
/* SQLite Database Class For HTML5 */
/*************************************/
function cDB(confs){
var ret = {
_db: null,
_response: null,
_error: null,
check : function(tbl){
if(!this._db) return false;
var _sql = '', _sqlField='', _field=[];
for(var i=0;i<tbl.length;i++){
_sql = "CREATE TABLE IF NOT EXISTS "+tbl[i].table+" (";
_field = tbl[i].properties;
_sqlField = '';
for (var j=0;j<_field.length;j++){
_sqlField += ',`'+_field[j].name+'` '+_field[j].type;
}
_sql += _sqlField.substr(1)+");";
this.query(_sql,null,null,null);
}
return true;
},
getResult:function(){
return this._response;
},
getError:function(){
return this._error;
},
callback_error: function(tx,_er){
var err = '';
if(typeof(tx) == 'object'){
for(var q in tx){
err += q+' = "'+tx[q]+'"; ';
}
}else{
err += tx+'; ';
}
if(typeof(_er) == 'object'){
for(var q in _er){
err += q+' = "'+_er[q]+'"; ';
}
}else if(typeof(_er) == 'undefined'){
err += _er+'; ';
}
console.log(err);
//if(callback) callback();
return false;
},
query: function(sql,callback,params,er){
if(!this._db) return false;
var self = this;
function _er(tx,__er){
__er = jQuery.extend(__er,{sql:sql});
if(er) er(tx,__er);
else self.callback_error(tx,__er);
};
this._db.transaction(function(tx){
tx.executeSql(sql,(params?params:[]),callback,_er);
}, _er);
},
update:function(tbl,sets,clauses,callback){
var __sql = 'UPDATE '+tbl, _field = null, __set = '', __clause = '',__values=[];
for(var i=0;i<sets.length;i++){0
_field = sets[i];
for(var j=0;j<_field.length;j++){
__set += ',`'+_field[j].name+'`=?';
__values.push(_field[j].value);
}
}
for(var i=0;i<clauses.length;i++){
__clause += ',`'+clauses[i].name+'`=?';
__values.push(clauses[i].value);
}
__sql += ((__set!='')?' SET '+__set.substr(1):'')+((__clause!='')?' WHERE '+__clause.substr(1):'')+';';
this.query(__sql,callback,__values);
return true;
},
remove:function(tbl,clauses){
var __sql = 'DELETE FROM '+tbl, __clause = '';
for(var i=0;i<clauses.length;i++)
__clause += ',`'+clauses[i].name+'`="'+escape(clauses[i].value)+'"';
__sql += ' WHERE '+((__clause!='')?__clause.substr(1):'FALSE')+';';
this.query(__sql);
return true;
},
multiInsert: function(tbl,rows,callback,er){
if(!this._db) return false;
var self = this;
var __sql = '', _field = null, __field = '', __qs = [], __values = [];
this._db.transaction(function(tx){
for(var i=0;i<rows.length;i++){
__qs = [];
__values = [];
__field = '';
_field = rows[i];
for(var j=0;j<_field.length;j++){
__field += ',`'+_field[j].name+'`';
__qs.push('?');
__values.push(_field[j].value);
}
tx.executeSql('INSERT INTO '+tbl+' ('+__field.substr(1)+') VALUES('+__qs.join(',')+');',__values,function(){return false;},(er ? er : self.callback_error));
}
}, self.callback_error, function(){
if(callback) callback();
return true;
});
return true;
},
insert:function(tbl,rows,callback){
var __sql = '', _field = null, __field = '', __qs = [], __values = [], __debug = '';
for(var i=0;i<rows.length;i++){
__qs = [];
__field = '';
_field = rows[i];
__debug += _field[0].name+' = '+_field[0].value+';';
for(var j=0;j<_field.length;j++){
__field += ',`'+_field[j].name+'`';
__qs.push('?');
__values.push(_field[j].value);
}
__sql += 'INSERT INTO '+tbl+' ('+__field.substr(1)+') VALUES('+__qs.join(',')+');';
}
this.query(__sql,callback,__values);
return true;
},
insertReplace:function(tbl,rows,debug){
var __sql = '', _field = null, __field = '', __qs = [], __values = [], __debug = '';
for(var i=0;i<rows.length;i++){
__qs = [];
__field = '';
_field = rows[i];
__debug += _field[0].name+' = '+_field[0].value+';';
for(var j=0;j<_field.length;j++){
__field += ',`'+_field[j].name+'`';
__qs.push('?');
__values.push(_field[j].value);
}
__sql += 'INSERT OR REPLACE INTO '+tbl+' ('+__field.substr(1)+') VALUES('+__qs.join(',')+');';
}
this.query(__sql,null,__values);
return true;
},
dropTable:function(tbl,callback){
var __sql = '';
if(tbl==null) return false;
__sql = 'DROP TABLE IF EXISTS '+tbl;
this.query(__sql,callback);
return true;
}
}
return jQuery.extend(ret,confs);
}
How To Use It
Database Constructor:
/* Create or open database with 'websiteDB' as database name and 'website DB' as title, and database site is 5MB */
/* I'm not using 1024 for the size multiplying because i don't want to be near at the margin size */
var db = new cDB({_db:window.openDatabase("websiteDB", "", "website DB", 5*1000*1000)});
Table Creation:
/* dbTable is database structure in this example, and contains 2 tables 'foo' and 'boo' */
/* and also the table structure in table properties */
var dbTable = [
{table:'foo',properties: [
{name:'foo_id', type: 'INT PRIMARY KEY ASC'},
{name:'foo_field_1', type: ''},
{name:'foo)field_2', type: ''}
]},
{table:'boo',properties: [
{name:'boo_id', type: 'INT PRIMARY KEY ASC'},
{name:'boo_field_1', type: ''},
{name:'boo_field_2', type: ''}
]}
];
/* this line is checking if the database exist or not and then create the database structure. */
/* table will be created if the table is not exist yet, if the table already exist, it will skip the */
/* table and continue with others tables */
if(!db.check(dbTable)){
db = false;
alert('Failed to cennect to database.');
}
Drop Table:
db.dropTable('foo');
Add / Insert Record:
var row = [];
row.push([
{'name':'foo_id','value':1},
{'name':'foo_field_1','value':'value 1 field_1'},
{'name':'foo_field_2','value':'value 1 field_2']}
]);
db.insert('foo',row);
SQLite is not accepting more than 1 line statement, that is the reason why we not able to do more than one statement query, like insertion. If you want to insert more than 1 record at the time, you need to use this function.
Multiline records Insertion:
var rows = [];
rows.push([
{'name':'boo_id','value':1},
{'name':'boo_field_1','value':'value 1 field_1'},
{'name':'boo_field_2','value':'value 1 field_2']}
]);
rows.push([
{'name':'boo_id','value':2},
{'name':'boo_field_1','value':'value 2 field_1'},
{'name':'boo_field_2','value':'value 2 field_2']}
]);
db.multiInsert('boo',rows,function(){alert('insertion done');});
Or, if you want to combine that two functions, just put if condition:
if(rows.length>=2){
db.multiInsert('boo',rows,function(){alert('insertion done');});
}else{
db.insert('boo',rows);
}
Delete record:
db.remove('boo',[{'name':'boo_id','value':1}])
Update record:
db.update('boo',[[
{'name':'boo_id','value':2},
{'name':'boo_field_1','value':'boo value'}
]],['name':'boo_id','value':2])
Query:
var query = 'SELECT * FROM foo';
db.query(query,function(tx,res){
if(res.rows.length){
alert('found '+res.rows.length+' record(s)');
}else{
alert('table foo is empty');
}
});
Just fell free to use it and change it, and don’t forget to ask if you don’t know how to use it, or if there is some bugs. 😀
link of this class on github: https://github.com/helman/jSQLite
Cheers,
Helman
Just curious, do you still use this for your mobile applications? Im learning and want to use html5, css, and jquery, but, mostly learning html5 currently, doesnt seem too hard, but, trying to learn about data storage, right now, creation of categories, basic storage like list of names, and creating new lists under those names.. sounds simple enough, but if you dont even know how to add, edit, delete, and used to mysql database with php…lol.. little more complicated than I thought..lol… So, do you suggest using this for data or anything else???? Thanks
Hi, thanks for your visit.
Depend on what is your usage, i did catalog app using offline database, because my client want the fast loading of data, so using this, my client only need to update the catalog if there are any changes from website.
HTML5 database (SQLite) is a little bit different from server database (mySQL). It is faster because of async processing, and because of async, it is quite defferent from php calling mySQL, we need to use Callback function to get the response of our query.
So, using offline db is very helpfull if you’re considering the loading time. But it is only limited to 5MB only for mobile storage.
Cheers,
Helman
I really like this piece of code, but I seem to have some trouble using it. I can write information out to a database, but I can’t seem to query it. I know originally when I was looking at the code I could. I’m not sure what changed. I see it will return an error code, but I’m not sure how to do that.
Could you show an example of query: function(sql,callback,params,er) using all the fields. For example, show an example using the params and er parameters. I think if I can see the error I can figure out the problem I’m having.
Thanks,
Martin
Hi Martin,
Thank you for considering of using this DB Class. This is the simple code for Query:
// Query with params, to use params, you need to use "?" (Question mark) in your query var sql = "SELECT * FROM foo WHERE name=? AND age=?"; // Use params for automatic escaping string / values. var params = ['John',20]; var callback = function(tx,res){ if(res.rows.length){ alert('found '+res.rows.length+' record(s)'); }else{ alert('table foo is empty'); } } // for the error callback function, i already put the error message on browser console, or if you want to replace the error function just refer to callback_error in the class, there are 2 parameters that you need to pass "tx" and "error" it self, and you need to "return false", this is the example of replacement var error = function(tx,_er){ var err = ''; if(typeof(tx) == 'object'){ for(var q in tx){ err += q+' = "'+tx[q]+'"; '; } }else{ err += tx+'; '; } if(typeof(_er) == 'object'){ for(var q in _er){ err += q+' = "'+_er[q]+'"; '; } }else if(typeof(_er) == 'undefined'){ err += _er+'; '; } alert(err); return false; } db.query(sql,callback,params,error);Just poke me, if you have any question. 🙂
Cheers,
Helman.
Hello,
thanks for the lib! I have a little question: The lib always a new DB, why?
Hello,
thanks for the lib! I have a little question: The lib add always a new DB, why?
Grettings David
Great class!
Can you give an example on using transactions and getting last inserted row id on a table? Notify me via my email .
Thanks and good day.
I notice a “zero” from class file at line 75. Is it a bug?
Hi Janek,
I just create a repository on github, and this is the link https://github.com/helman/jSQLite with the latest update.
cheers,
helman
What in case more than 5 MB is needed. I am developing an app using HTML5 and javascript with SQL server backend (webservices used), but I need to store data offline for sales entry and then sync it back to server when in offline mode.
I have seen tutorials for native development with Sqlite but not while using HTML5 & jquery alone. Do you have any suggestions about htis
Thank you
Nad
Hi Nader, have a look at my new post Solved iOS Database Size Limitation of 5MB (using Plugin)
Cheers,
Helman