Working with SQL databases with Transaction using HTML5

Html 5 has come across with lots of new stuffs, some of them are overwhelming and very interesting in real world. One of such storage APIs that is available with modern browsers are SQL databases on client side. The specifications allows you to open a Database, create tables, maintain relationship, open transactions etc. These are in fact so much appealing that everything that you need in your server can have local copy on the browser itself if the Quota permits and hence releaving so much pain of the server in delivering and executing complex calculations and logic in the server.

Let us look how to deal with SQL databases from Client Side.

var db = openDatabase("DBTest", "1.0", "Sample Description", 200000);
db.transaction(function(tx) {
tx.executeSql("SELECT * FROM Table1Test", [], function(tx, result) {
for (var i = 0, item = null; i &lt; result.rows.length; i++) {
item = result.rows.item(i);
document.getElementById('results').innerHTML +=
'&lt;li&gt;&lt;span&gt;'+
item['text'] + '&lt;/span&gt;&lt;/li&gt;';
}
});
});

Here we open a Database in the client side using javascript and call it as Table1Test. We first try to select the Rows using a select statement inside a Transaction. Such that if transaction is successful, it will show the data on the screen, or otherwise will fail.

Similar to one shown above, you can also create a Table or even run your DML statement directly within a transaction like the code below :

db.transaction(function(tx) {
tx.executeSql("CREATE TABLE Table1Test (id REAL UNIQUE, text TEXT)", [],
function(tx) { log.innerHTML = 'Table1Test created' },
onError);
});

db.transaction(function(tx) {
tx.executeSql("INSERT INTO Table1Test (id, text) VALUES (?, ?)", [num, 'Modify text...'],
function(tx, result) {
log.innerHTML = 'record added';
showRecords();
},
onError);
});

db.transaction(function(tx) {
tx.executeSql("UPDATE Table1Test SET text = ? WHERE id = ?", [textEl.innerHTML, id], null, onError);
});

db.transaction(function(tx) {
tx.executeSql("DELETE FROM Table1Test WHERE id=?", [id],
function(tx, result) { showRecords() },
onError);
});

db.transaction(function(tx) {
tx.executeSql("DROP TABLE Table1Test", [],
function(tx) { showRecords() },
onError);
});

The executeSql takes 4 parameters. The first one is the SQL statement, the second one is an array of all parameters that need to be passed to the sql statement. Generally the parameters are replaced by the ?(question mark) which acts as a wildcard inside the sql statement. And finally the success callback and errorcallback. The transaction is committed before the successcallback is called and rolledback after errorcallback is called.

One thing to note, HTML 5 strictly imposes to open transaction before you call executeSql on the database. So everything you do in client side will be inside a transaction.

To view the database in your browser (I am using chrome), you can point to Tools->Developer Tools and go to Resources.