Click here to Skip to main content
15,867,568 members
Articles / Programming Languages / SQL

HTML5 Web Database and Mobile Web Applications

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
28 Feb 2011CPOL2 min read 25.5K   5   1
HTML5 Web Database and Mobile Web Applications

The Web SQL Storage (based on SQLite) API in HTML 5 is ideal for Mobile Web applications that require a complex data storage support and that work mostly offline. The Web storage remains locally on the device, and it is persisted even after the application or browser is closed. The implementation of the data access is all done using JavaScript and the SQL script format is compliant with the SQLite specifications.

To create or open a database, the following script can be used:

JavaScript
var data=null; //connection handler
var Tables= ['CREATE TABLE IF NOT EXISTS mynotes
	(id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,notes TEXT, date TEXT)'];

if (typeof (window.openDatabase) != 'undefined' && window.openDatabase) {
    var maxSize = 5 * 1024 * 1024; // in bytes = 5MB 
    //name,version,displayName,maxsize to identify the app database
    data = window.openDatabase('appName', '1.0', 'appName_db', maxSize);
    if (data) {//transaction is open
       data.transaction(function (tx) {  $(tables).each(function () 
	{ tx.executeSql(this); });  });
    } 
}

The script above checks the openDatabase property from the windows object. If the value is true, there is Web Storage support, and we can move forward with the API calls to open the database. The openDatabase call opens the connection to the database. If the database does not currently exists, the database gets automatically created, but with no tables. The parameters to open the database uniquely identify the database and its size. Once we have a valid handle to the database, we can use a transaction object to run a SQL statement and build our tables using the executeSql method. This method is also used to execute other SQL statements on the database. The SQL statement to build the tables checks to see if the table does not exist before creating.

CRUD Method

To insert, update, and delete a record, we just need to use the following scripts:

JavaScript
Execute: function (sql, params, dataHandler, errHandler) {
   if (data) {//transaction is open
      data.transaction(function (tx) 
	{tx.executeSql(sql, params, dataHandler, errHandler);});
   }
},
ErrorHandler: function (tx, err) {
    alert('DbError: ' + err.message + ' (Code ' + err.code + ')');
   return false;
},
DataHandler: function (tx, data) { 
  if (typeof (data) != 'undefined') {
       if (data.rows.length == 0) { 
            //TODO CHECK FOR rowsAffected,insertedId FOR updates,deletes, inserts. 
       } 
      else if (data.rows.length > 0) { //selects
          for (i = 0; i < data.rows.length; i++) {
              var row = data.rows.item(i);
             var data = [row['id'] ,row['notes'], row['date']] //sets value in array
             //TODO CONSUME THE DATA HERE 
         } 
     }
  }
}

The execute function executes a SQL statement by calling the transaction executeSql method. This method takes the SQL statement, a parameters array and handlers for successful or failed responses. The DataHandler manages the return recordset (data parameter) or rows affected from an update, delete and insert statement. The data.rows collection is populated when a select statement is executed. For an insert on a table with an identity column, the data.insertedId property is set to the new identity id. The DataHandler method is where the recordset can be iterated to populate the UI with the data.

The CRUD statements look as follows:

SQL
sqlIns: 'INSERT INTO mynotes(notes,date) values(?,?)',
sqlUpd: 'UPDATE mynotes SET notes=?,date=? WHERE id=?',
sqlDel: 'DELETE mynotes WHERE id=?',
sqlSel: 'SELECT * FROM mynotes WHERE id=?'

You should note the question marks. These are the parameter variables that are matched to the values in the params array of the Execute function. For example, to insert a new record, use the following:

JavaScript
var params = [‘my notes here’,’02/20/2011’];
Execute(sqlIns, params,DataHandler,ErrorHandler);

With the ability to open a database and execute SQL statements against the database, simple data providers could be implemented to provide data support for your mobile applications. This can be handy for disconnected mode support in mobile applications.

I hope this is helpful.


og-bit.com

This article was originally posted at http://ozkary.blogspot.com/feeds/posts/default

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect OG-BITechnologies
United States United States
Software engineer, author & speaker who enjoys mentoring, learning, speaking and sharing with others about software development technologies. Microsoft MVP.

My Blog

Comments and Discussions

 
SuggestionWorking Code is missing Pin
Member 83614398-Jul-12 22:42
Member 83614398-Jul-12 22:42 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.