Published: 2010-02-18
Here is a demo of the key functions required to use a LOCAL SQL database available in HTML5. This is NOT storage methods that store simple key/value pairs but rather a true SQL database. This is a critical fuction needed for offline applications and those that need to save more data than cookies allow. This only works on Safari and Chrome at the moment but other browsers will support the functionality soon. This demo is not complete by any means and could easily be achieved using other methods. The purpose is to show local SQL database functions as simply as possible. You would obviously have more complex requirements than shown here. The demo is enough so you can see how to do these functions yourself. I suggest going to http://SQLite.org/ to see more on the database that Chrome uses. Go to http://www.sqlite.org/lang.html for SQL syntax and compatibility issues with SQL92. View the source of this page to see the code. All JavaScript code for the demo is in one file called html5db.js. The code is designed into small functions that perform specific tasks to avoid confusion. The demo has simple comments and written to reduce confusion(hopefully).
The LOCAL SQL database demo is a very simple contact database and shows the following concepts/functions...

• Validate Internet connection
• Open/Create a local database
• Create table(if required)
• Populate table with data(if no data)
• Display database table on page.
• Insert, Modify, or Delete a record
• Delete/Drop a table(not used)

Below is an example of a call to an open local SQL database from JavaScript using HTML5.

	db.transaction(function (tx) {  
	  tx.executeSql(SQLtxt[3], myParms, function(tx,result){/* nop */},
	    function(tx,error){Error_DB(0,error)});
	})

The first line "db.transaction(function (tx) {" starts a transaction. The second line actually issues the command with parameters and defines functions for success or failure actions of the transaction. The format of second line is...
tx.executeSQL(SQL, Parameters, SuccessFunction, Failedfunction);
So in our example "SQLtxt[3]" is the SQL, "myParms" is Parameters, "function(tx,result){/* nop */}" is the SuccessFunction that is triggered if SQL command was successful, and "function(tx,error){Error_DB(0,error)}" is the FailedFunction that is triggered if SQL fails. You will see several of these transactions in the demo. Technically all that is needed for line 2 is the SQL command for this to work - "tx.executeSql(SQLtxt[3])" will work fine but no actions are taken on success or failure and no parameters are passed. However, place markers must be implemented if you want to include right most parms(if you want to have a failure action you have to define dummy parms([]) and success funtions(like in the example code above) so the failure action is recognized)

Click here to see demo

Please note: 5 "fake" contacts will be created if database is blank on page load to ensure there are current contacts defined for demo. So, if you have only the original contacts listed, then clear the table, and refresh the page, it would appear that the contacts are still in the database where in fact they were deleted and then recreated when page reloaded. Remember this is a demo, not production!

Current Contacts