Web SQL Database: client-side databases using SQL.

There many techniques for storing data on the client side, such as: cookies, sessionStorage, localStorage, and database. For a detailed description of each technique please visit www.w3.org.

In this article we will work with client-side database.

Web SQL Database is a set of APIs to manipulate client-side databases using SQL. These sets of APIs are non-normative, which means that there is no standard. In case of Google Chrome, it implements SQLite.

Key methods:

1. openDatabase(name,version,displayName,estimatedSize, optional Callback)
2. transaction(callback, optional errorcallback, optional voidcallback)
3. readTransaction(callback, optional errorcallback, optional voidcallback)
4. executeSql(SQLStatement,optional arguments,optional Callback, optional errorCallback)

* transaction is read and write.

* readTransaction is read only.

Key objects:

• SQLResultSet
• SQLResultSetRowList

How it works:

• First we create the database:

var myDB = window.openDatabase("myDatabaseName","1.0","my database storage",5*1024*1024);

/*(window.openDatabase: object window implements inteface WindowDatabase)*/

• Next we create our tables, we verify if the table already exists, otherwise we create the table:

myDB .transaction(function(t){

t.executeSql('SELECT name FROM sqlite_master WHERE type=\'table\' and name =\'mytablename\'',[],

function(t,r){

if(r.rows.length==0){

t.executeSql('CREATE TABLE mytablename(id INTEGER PRIMARY KEY AUTOINCREMENT,mytextfield TEXT)');

}

}

);

},function(err){

alert(err.message+" .Creating table.");

}

);

• Insert some records:

callback callback errorcallback errorcallback

//myDB.transaction(function(t){t.executeSql('',[],function(){},function(){})},function(){})

myDB.transaction(function(t){

t.executeSql("INSERT INTO mytablename VALUES(NULL,?)",["some text"],function(t,resultset){

alert(resultset.insertId);

},function(t,e){

alert(e.message);

});

},function(err){

alert(err.message);

}

);

• Read from Database:

myDB.transaction(function(t){ t.executeSql('SELECT mytextfield FROM mytablename ',[],function(t, resultset){

for(var i=0; i<>

result = resultset.rows.item(i);

alert(result. mytextfield ); } }); },function(err){ alert(err.message+" .Restoring database."); } );

resultset is an boject of type SQLResultSet. This object has 3 properties: insertId, rowsAffected, and rows.

the property rows is a type of SQLResultSetRowList

Considerations:

1. The API is asynchronous.
2. use of the ? placeholder feature of the executeSql() method, and to never construct SQL statements on the fly.
3. For accessing items in the result set rows use (): resultset.rows.item(index)

Thanks.