Udo's Techblog

So you’re trying to hack together some offline capability for your browser-based app, good for you! Until HTML 5 came along, there were basically two options to accomplish this: you could either require your users to install Google Gears or you could distribute a local server stack. Enter HTML 5′s offline capabilities!

Now, besides Cookies, there are three new offline capabilities of note: an application caching mechanism (including the option of declaring a self-contained applet using the manifest attribute), local object storage and local SQL databases. We’re gonna be talking about the latter. Offline SQL is currently available in Safari, Google Chrome, on the iPhone and Palm’s WebOS (both for its applications and browser-based content). As of now, Firefox 3.51 seems to have basic support, but its implementation is either nonstandard or so buggy as to be unusable.

Speaking of standards, there is no clear spec on the actual SQL dialect used for HTML 5. Instead SQLite is considered the reference implementation. That means if you got any questions about your actual SQL code, head on over to the SQLite site.

Accessing the local database

There is an easy way to test whether your environment supports HTML5 database:

if (window.openDatabase)
    db = window.openDatabase("app", "", "my app db name", 1024*1024);

Now you can simply test if db is an object or not after this operation. If you got a database object back that means your browser has support built in and it successfully accessed your app’s storage. Here’s how it works:

window.openDatabase( DatabaseName, DatabaseVersion, DisplayName, EstimatedSize )

Be careful with the DatabaseVersion parameter though. If it’s set and it doesn’t match, the operation will fail. This gives you the opportunity to implement an upgrade mechanism but it can obviously also cause a lot of headache.

Executing queries

The database interface provides asynchronous access through a transaction paradigm:

transaction.executeSQL( SQLStatement, SQLParameters, ResultsetCallback, ErrorCallback )

…and this is how it looks in action:

db.transaction(function(tx)
  {
    tx.executeSql('SELECT * FROM MyTable WHERE CategoryField = ?',
      [ selectedCategory ],
      function (tx, rs) { displayMyResult(rs); },
      function (tx, err) { displayMyError(err); } );
  });

This fetching example shows how to get data from the DB, or more generally: how to execute arbitrary SQL statements. In this example displayMyResult(rs) and displayMyError(err) are just placeholders for whatever you want to do with the result once it arrives.

That’s pretty much it for the basics, now you can write your own offline app!

Possibly Related Posts:

1. Current HTML 5 SQL Spec
2. Safari Offline SQL Storage
3. Fatal SQL Injection
4. Fixing Juice
5. Ad-Hoc SQL Wisdom