HTML 5 SQL Tutorial – The Basics

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!

10 thoughts on “HTML 5 SQL Tutorial – The Basics

  1. Andres

    hey, thanks, this is a great information, could you show us a code in which you retrieve a register info from the rs var

  2. Phil Sobolik

    Where is the physical location of the database (.db)? I have tried doing the above and then searching for the .db. What I’m really interested in is where do I put an existing database that I want to use?

  3. Tejprtap singh

    hi
    i have created sqllite database(question) and i save this my desktop. now i want to access(question) using html5 pls help me i am new in html5 its urgent.

    thanks in advance

  4. christi parks

    Hello, i would like to ask that what is the benefits of sql training, what all topics should be covered and it is kinda bothering me … and has anyone studies from this course [URL REDACTED] of SQL tutorial online?? or tell me any other guidance…
    would really appreciate help… and Also i would like to thank for all the information you are providing on sql training.

    Udo: fuck “SQL training” and fuck you
  5. karthi

    I want to access database(SQL server) in HTML page without using ADODB connection.Why means if i access through ADODB connection means i am getting security problems as well as browser support problem.. Is there any way to solve the issues means please guide me.. I am new to programing..

    Thanks to all advance

    by
    KARTHI

  6. udo Post author

    Hey Karthi, from your description it’s difficult to say what it is you’re trying to do. This mini tutorial is supposed to be for client-side DB access. It sounds like you might be talking about server-side DBs but it’s unclear what language/environment you’re using. I would strongly suggest you adopt a standardized environment for learning. Have a look at Ruby on Rails or Symphony, I swear you’ll have an easier time of it for your first few projects!

  7. ravi teja

    hello, i wanna know if anyone could help me out.
    im wanna know how to connect to sql database that is already created in SQL server. for example we use connection string in ado.net. but i don’t know how to do it in html5. please post the best solution for this if anyone have it.

  8. udo Post author

    Dear Ravi and others, I’m not sure why this keeps coming up. You can’t natively interface with DB servers from your browser. The (quite dated) article above deals with an SQLite instance that comes with most browsers, but there is no such thing as connecting to ADO or whatever. There is no “connection string”. It almost looks like you don’t have a clear understanding of the difference between server-side and client-side apps. I strongly recommend you pick a framework of your choice, like Ruby on Rails for example, and just learn the basics from that.

Comments are closed.