HTML5 Web SQL Databases

Web SQL Databases

The HTML5 specification is not a part of the Web SQL Database API but is separate specification that introduces a set of APIs manipulating the client-side databases by using SQL.

Core Methods

There are three core methods that are defined in SQL. They are as follows:

1. openDatabase – This method helps in creating a database object which is either used for the existing database or creating a new one.

2. transaction – It is a method which gives an ability for controlling a transaction and performing either the commit or the roll back operation.

3. executeSql – It is used for executing the actual SQL query.

Opening a Database

The openDatabase method will open the database.

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

Where,
     mydb - Database name
     1.0 - Version number
     Test DB - Text description
     2*1024*1024 -  Size of database
     Creation callback – called if the database is created. Even if this feature is not available the database will be still created.

Executing the queries

The database.transaction() function is used for executing the query. A single argument is required by this function.

Example : To create a table Employee in the database

var db = openDatabase ('mydb', '1.0', 'Test DB', 2*1024*1024);
db.transaction(function (tx)
{
     tx.executeSql(' CREATE TABLE IF NOT EXISTS EMPLOYEE (id unique, name)');
});

INSERT Operation

Making entries into the table can be done in the following manner:

var db = openDatabase ('mydb', '1.0', 'Test DB', 2*1024*1024);
db.transaction(function (tx)
{
     tx.executeSql ('CREATE TABLE IF NOT EXIXTS EMPLOYEE (id unique, name)');
     tx.executeSql ('INSERT INTO EMPLOYEE (id, name) VALUES (1, “Rahul”)');
     tx.executeSql ('INSERT INTO EMPLOYEE (id, name) VALUES (2, “Prajakta”)');
     tx.executeSql ('INSERT INTO EMPLOYEE (id, name) VALUES (1, “Sakshi”)');
});


Dynamic values can also be added at the time of creation as follows:

var db = openDatabase ('mydb', '1.0', 'Test DB', 2*1024*1024);
db.transaction(function (tx)
{
     tx.executeSql ('CREATE TABLE IF NOT EXIXTS EMPLOYEE (id unique, name)');
     tx.executeSql ('INSERT INTO EMPLOYEE (id, name) VALUES (?,'?'), [e_id, e_log];
});


In the above example, e_id and e_log are the external variables. The executeSql will map each item in the array argument to the “?”.

READ Operation

A READ operation is used for capturing existing records for a callback.

Example : Demonstrating a READ Operation

var db = openDatabase ('mydb', '1.0', 'Test DB', 2*1024*1024);
db.transaction(function (tx)
{
     tx.executeSql(' CREATE TABLE IF NOT EXIXTS EMPLOYEE (id unique, name)');
     tx.executeSql ('INSERT INTO EMPLOYEE (id, name) VALUES (1, “Rahul”)');
     tx.executeSql ('INSERT INTO EMPLOYEE (id, name) VALUES (2, “Prajakta”)');
     tx.executeSql ('INSERT INTO EMPLOYEE (id, name) VALUES (1, “Sakshi”)');
});
db.transaction(function (tx)
{
     tx.executeSql('SELECT * FROM EMPLOYEE', [], function (tx, results)
     {
          var len = results.rows.length, i;
          msg = "<p>Number of rows found: " + len + "</p>";
          document.querySelector('#status').innerHTML +=  msg;
          for (i = 0; i < len; i++)
          {
               alert(results.rows.item(i).log );
          }
     }, null);
});


Example : Demonstrating a complete program on HTML5 WEB SQL

<!DOCTYPE HTML>
<html>
<head>
     <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 SUBJECTS (id unique, log)');
               tx.executeSql('INSERT INTO SUBJECTS (id, log) VALUES (1, "Java")');
               tx.executeSql('INSERT INTO SUBJECTS (id, log) VALUES (2, "C Programming")');
               tx.executeSql('INSERT INTO SUBJECTS (id, log) VALUES (3, "HTML5")');
               msg = '<p>Log message created and row inserted.</p>';
               document.querySelector('#status').innerHTML =  msg;
          });
          db.transaction(function (tx)
          {
               tx.executeSql('SELECT * FROM SUBJECTS', [], function (tx, results)
               {
                    var len = results.rows.length, i;
                    msg = "<p>Number of rows found: " + 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:
Log message created and row inserted.
Number of rows found: 3
Java
C Programming
HTML5
▲