HTML5 Web Local Database

Web SQL Local Database in HTML5

The "Web Database" feature is new in HTML5. The HTML5 sql function provides a structure for the SEQUENTIAL processing of SQL statements within a single transaction.

Browser Support

A Web SQL Database will work in the latest version of Safari, Chrome and Opera.

Features

It provides the capability to sequentially process SQL in many different ways:

• As a single SQL statement string.
• As an array of SQL statement strings.
• As an array of SQL statement objects.
• As a string containing multiple SQL statements, each of which ends in a semicolon.
• From a completely separate file containing SQL statements. 

Various Methods are:

1. openDatabase
   Creates the database object, either using an existing database or creating a new one.
2. transaction
   Controls a transaction and performs either a commit or rollback on the basis of the situation.
3. executeSql
   Executes a SQL query.

Creating and Opening Database

This method creates the database object, either using an existing database or creating a new one.

In order to create and open a database we can use the following code:

var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);

In the openDatabase method the following arguments are to be ed:

• Database name
• Version number
• Text Description
• Estimated size of database
• Creation callback

The last argument (Creation callback) will be called if the database is being created.

Database Size

Suppose we try to create a database over the size of the default database size. It will ask whether we want to grant the database permission to scale up to the next size of the database to build the database. However using Opera it will build the database without showing an error message.

Version Number 

Since the version number is a required argument we must know the version number before we try to open it. Otherwise,it will throw an exception.

changeVersion is the method to change the database version. It works well in Chrome and Opera, but not in Safari or Webkit. If I can't determine which version of the database the user is on then I can't upgrade the user.

Transactions   

After opening the database we can create the transactions. Transactions give us the ability to do a "rollback". This means that if a transaction which could contain one or more SQL statements fails then the updates to the database are never committed — i.e. it's as if the transaction never happened.

A transaction is similar to a function that contains code.

var db = openDatabase('mydb', '1.0', 'my first database', 2 * 1024 * 1024);

db.transaction(function (tx) {

  // here be the transaction

  // do SQL magic here using the tx object

});

 

executeSql

The executeSql method is used for both read and write statements, which includes SQL injection projection, and provides a callback method to process the results of any queries that you may have written. To execute a query you use the database.transaction() function. This function needs a single argument.

Once we have a transaction object, we can call "executeSql":

var db = openDatabase('mydb', '1.0', 'my first database', 2 * 1024 * 1024);

db.transaction(function (tx) {

  tx.executeSql('CREATE TABLE moon (id unique, text)');

});

 

Note that if the database already exists then the transaction will fail, so any successive SQL wouldn't run. So we can either use another transaction, or we can only create the table if it doesn't exist.

Insert Operation

var db = openDatabase('mydb', '1.0', 'my first database', 2 * 1024 * 1024);

db.transaction(function (tx) {

  tx.executeSql('CREATE TABLE IF NOT EXISTS moon (id unique, text)');

  tx.executeSql('INSERT INTO moon (id, text) VALUES (1, "synergies")');

});

We can also dynamic values while creating enteries, as in:

var db = openDatabase('mydb', '1.0', 'my first database', 2 * 1024 * 1024);

db.transaction(function (tx) {

  tx.executeSql('CREATE TABLE IF NOT EXISTS moon (id unique, text)');

  tx.executeSql('INSERT INTO moon (id, text) VALUES (?, ?)', [id, userValue]);

});

id and userValue are external variables, and executeSql maps each item in the array argument to the question marks.

At last, if we want to select or read values that already exist then we use a callback to capture the results:

tx.executeSql('SELECT * FROM moon', [], function (tx, results) {

  var len = results.rows.length, i;

  for (i = 0; i < len; i++) {

    alert(results.rows.item(i).text);

  }

});

Example

<!DOCTYPE html>

 

<html lang="en" xmlns="http://www.w3.org/1999/xhtml">

<head>

    <meta charset="utf-8" />

    <title></title>

    <script type="text/javascript">

        var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);

        var msg;

        db.transaction(function (tx) {

            tx.executeSql('CREATE TABLE IF NOT EXISTS LOGS (id unique, log)');

            tx.executeSql('INSERT INTO LOGS (id, log) VALUES (1, "foobar")');

            tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "logmsg")');

            msg = '<p>Log message created and row inserted.</p>';

            document.querySelector('#status').innerHTML = msg;

        });

 

        db.transaction(function (tx) {

            tx.executeSql('SELECT * FROM LOGS', [], function (tx, results) {

                var len = results.rows.length, i;

                msg = "<p>Found rows: " + len + "</p>";

                document.querySelector('#status').innerHTML += msg;

                for (i = 0; i < len; i++) {

                    msg = "<p><b>" + results.rows.item(i).log + "</b></p>";

                    document.querySelector('#status').innerHTML += msg;

                }

            }, null);

        });

    </script>

</head>

<body>

    <div id="status" name="status">Status Message</div>

</body>

</html>

Output