Thursday, February 4, 2010

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

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:
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\'',[],
t.executeSql('CREATE TABLE mytablename(id INTEGER PRIMARY KEY AUTOINCREMENT,mytextfield TEXT)');
alert(err.message+" .Creating table.");

  • Insert some records:
callback callback errorcallback errorcallback

t.executeSql("INSERT INTO mytablename VALUES(NULL,?)",["some text"],function(t,resultset){
  • 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

  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)

No comments:

Post a Comment