Using the Web SQL Database API in HTML 5

September 14, 2010

The HTML 5 specification has introduced a number of new elements, attributes and APIs to increase the quality, speed and richness of Web applications, and a few browsers including Google Chrome have already implemented many of these exciting features. One of the HTML 5 specifications, Web SQL database enables offline data storage by defining an API for storing structured data within the client.

Unlike HTML 5's Web Storage API, which persists data in key-value pairs on the client, the Web SQL database persists the data in a database on the client. The Web SQL database is asynchronous and can be queried and manipulated using the API through JavaScript.

In this article, I introduce the new Web SQL database feature in HTML5 and its API. We then dive into using Web SQL database with a demo for implementing a shopping cart.

The Interfaces of the Web SQL Database API

In this section, we explore some of the interfaces and methods that are available in the Web SQL database API.

Database API

The Database API of Web SQL database has the WindowDatabase and WorkerUtils interfaces. The interfaces have the openDatabase() method, which creates the database within the browser. If the user agent is not configured to allow the page to open the database locally, the openDatabase method will result in a SECURITY_ERR exception. If there is no database with the given name, then the openDatabase method will create a new database.

Here is the syntax:

Database openDatabase(databaseName, databaseVersion, displayName, 
estimatedSizeOfDataInBytes, databaseCreationFailureCallback) 

Here is a code example:

              var shortName = 'productdatabase';
              var version = '1.0';
              var displayName = 'Product Database';
              var maxSize = 65536; // in bytes
              db = openDatabase(shortName, version, displayName, maxSize);

The last statement in the above code would create a database.

Asynchronous Database API

The Asynchronous database API has an interface called Database. The Database interface has the transaction and readTransaction methods, which take up to three arguments. The transaction method opens the database in read/write mode, whereas the readTransaction method opens the database in read mode.

Here is the syntax:

void transaction(sqlTransactionCallback, transactionErrorCallback, transactionSuccessCallback)

void readTransaction(sqlTransactionCallback, transactionErrorCallback, transactionSuccessCallback)

Because the method is asynchronous, it would immediately return and then asynchronously invoke sqlTransactionCallback. Based on the result of sqlTransactionCallback, transactionErrorCallback or transactionSuccessCallback would be executed.

Here is a code example:

      db.transaction(
         function (transaction) {
            transaction.executeSql('CREATE TABLE IF NOT EXISTS Product(productid INTEGER NOT 
NULL PRIMARY KEY AUTOINCREMENT, productname TEXT NOT NULL, price INTEGER, qoh
 INTEGER);', [], nullDataHandler, killTransaction);
         }
      ); 

In the above code, the transaction is invoked by defining only sqlTransactionCallback. The code does not have either transactionErrorCallback or transactionSuccessCallback in it.

executeSql

The executeSql method should be invoked as part of sqlTransactionCallback, transactionErrorCallback or transactionSuccessCallback of transaction or readTransaction.

Here is the syntax:

void executeSql(sqlStatement, argumentsWithinSquareBrackets, sqlStatementCallback,
 sqlStatementErrorCallback)

The sqlStatement in the executeSql argument would be preprocessed using arugmentsWithinSquareBrackets.

Here is a code example:

transaction.executeSql("INSERT INTO product values(?,?,?,?);",[ productId,
 productName, price , qoh ], nullDataHandler, killTransaction);

The nullDataHandler and the killTransaction are the sqlStatementCallback and sqlStatementErrorCallback functions, respectively.

 /* This is the data handler which would be null in case of table creation and record insertion */
   function nullDataHandler(transaction, results)   {
   }
   /* This is the error handler */
   function killTransaction(transaction, error) {
   }

Synchronous Database API

The Synchronous database API has the interface DatabaseSync, which has the transaction and readtransaction methods.

Here is the syntax:

void transaction(sqlTransactionSyncCallback)
void readTransaction(sqlTransactionSyncCallback)

Because the method is synchronous, the method would be executed immediately.

Here is the executeSql syntax:

SQLResultSet executeSql(sqlStatement, argumentsWithinSquareBrackets)

Database Query Results API

The database query results API has the interface SQLResultSet. A successful execution with the executeSql method would invoke sqlStatmentCallback with SQLResultSet as an argument.

SQLResultSet has three attributes for insertRowId, rowsAffected and rows.

When the corresponding executeSql inserts a record into the table, the insertRow would reflect the row ID of the last row to be inserted into the table. If the corresponding executeSql is affecting any records, because of operations such as update or delete, rowsAffected would reflect the number of rows being affected.

The rows (third attribute) of type SQLResultSetRowList has length and item.

As an example, consider the following code to understand the usage of SQLResultSet and SQLResultSetRowList.

       transaction.executeSql('select * from product where productId=?;', [productId],
          function(transaction, results){
      for (var j=0; j<results.rows.length; j++) {
        var row = results.rows.item(j);
        alert(row[‘productId’]);
      }
         });

In the above code, executeSql has an inbuilt sqlStatmentCallback. The callback method takes two arguments, namely transaction and results. Results is of type SQLResultSet, so results.row.length would return the length of the SQLResultSet. The product details from the result set can be retrieved by using results.rows.item(j) as demonstrated in the above code.

Implementation of Shopping Cart Using Web SQL Database

In this section, we walk through implementing a shopping cart application. When requested, the Web application will send the details of the products available to the client. To reduce the interaction between the client and the server, the client should store the details of the products, which include product ID, product name, qoh (quantity on hand), and price.

When the user selects a particular product, the validation for the availability of the required quantity is carried out on the client. If the required quantity is not available, the application will pop up an alert. If the required quantity is available, the product will be added on to the local session variable, which acts as the shopping cart (see Figure 1).

Figure 1. Shopping Cart Product Details

When the user confirms for billing, the products available in the local session variable can be further processed.

Here are the procedures to be implemented for the problem:

1. When loaded into the browser, the page should create a Web SQL database on the client to store the product details sent by the server.
2. The page should create a table within the Web SQL database to store the product details from the server and load them into the table.
3. The page should display the list of products available to the user.
4. When the user selects the products and clicks the "Shop" button, the products selected -- along with the quantity required -- should be validated against the quantity available in the local database. If the required quantity is available, the product ID and the required quantity of all the chosen products should be stored in the Web storage session variable.

The code to implement the above steps is explained below.

Step 1

The code below validates whether the browser supports the Web SQL database. If it does, the code uses the openDatabase method to create a database with the name productdatabase.

<script>
var db;
   function initDB()
   {
      try {
          if (!window.openDatabase) {
              alert('not supported');
          } else {
              var shortName = 'productdatabase';
              var version = '1.0';
              var displayName = 'Product Database';
              var maxSize = 65536; // in bytes
              db = openDatabase(shortName, version, displayName, maxSize);
              // invoke the createTables function explained below
          }
      } catch(e) {
          // Error handling 
          alert("Error creating the database");
      }
}

The initDB in the above given script should be invoked during onload of the <body> of the page.

Step 2

The code below creates the table to store the product details provided by the server.

   function createTables(db)   {
      // creates the product table to store the product details
      db.transaction(
         function (transaction) {
            transaction.executeSql('CREATE TABLE IF NOT EXISTS Product
			(productid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
			productname TEXT NOT NULL, 
			price INTEGER, qoh INTEGER);',
			 [], nullDataHandler, killTransaction);
         }
      );

      // populates the product details in to the Web storage
      db.transaction(
        function (transaction) {
          // retrieve product details from the server 
    /* the below given code retrieve the product details from productList which is an 
         Arraylist available within the same JSP ! */
          <%
         for(int i=0;i<productList.size();i++){
          %>
         var productId = <%=productList.get(i).getProductId()%>;
         var productName = '<%=productList.get(i).getProductName()%>';
         var price = <%=productList.get(i).getPrice()%>;
         var qoh = <%=productList.get(i).getQoh()%>;
         transaction.executeSql("INSERT INTO product values(?,?,?,?);",[ productId, 
            productName, price , qoh ], nullDataHandler, killTransaction);
          <%
         }
          %>
        }
      );
    }

The method createTables should be invoked from the initDB given under step 1 when the database is created successfully.

Step 3

The code below is for displaying the list of products available to the user dynamically. The productList is of type ArrayList<Product>, where the Product has productId, productName, price, qoh.

      <table>
      <%
         for(int i=0;i<productList.size();i++){
      %>
      <tr>
      <td><input type='checkbox' 
	  	value='<%=productList.get(i).getProductId()%>' 
		name='product'><%=productList.get(i).getProductName() %></td>
      <td><input type="text" name="p"+<%=productList.get(i).getProductId()%> /></td>
      </tr>
      <%
         }
      %>   
      </table>
      <table>
            <tr>
            <td>         
               <input value="Shop" type="submit" onclick="return productValidation();"/>
            </td>
            </tr>
      </table>

Step 4a

The onclick of the button invokes the productValidation() function. The productValidation function in turn invokes the checkProducts function as shown below.

   function productValidation(){
       checkProducts(document.Products.product, 0);
       return false; 
   }

   function checkProducts(products, i){
     var db = systemDB;
     if (i<products.length){
         if (document.Products.product[i].checked==true){
         productId = document.Products.product[i].value;
         qty = document.Products.p[i].value;

     db.transaction(
     function(transaction){
       transaction.executeSql('select * from product where productId=?;', [productId],
          function(transaction, results){
      for (var j=0; j<results.rows.length; j++) {
        var row = results.rows.item(j);
        if (qty>row['qoh']){
          alert(row['productname'] + ' is out of stock. We can serve you only ' + row['qoh'] + ' 
quantities currently!');
          document.Products.product[i].checked = false;
          document.Products.p[i].value = "";
        }
        else{
          document.Products.product[i].checked = false;
           document.Products.p[i].value = "";
          pId = productId;
          pQty = qty;
          pName = row['productname']; 
          updateProduct();
      }
               }
              checkProductsSequential(products,i+1);
         });
       }
     );
    }
    else{
     checkProducts (products,i+1);         
    }
}
}

The checkProducts method uses the asynchronous method of the Web SQL storage API. It is important to make sure that the validations happen one product after the other.

Step 4b

If the required quantity is available, the product and the quantity required should be added on to the Web storage and the quantity on hand (qoh) should be updated accordingly. The updateProduct function delivers this functionality.

   function updateProduct(){

      db.transaction(
         function (transaction) {
            transaction.executeSql("UPDATE PRODUCT SET qoh=qoh-? 
				WHERE productid=?;",[pQty,pId], nullDataHandler, killTransaction);
         }
      );
      var qty = sessionStorage.getItem(pName);
      if (qty){
         sessionStorage.removeItem(pName);
         sessionStorage.setItem(pName,(Number(qty)+Number(pQty)));   
      }
      else
         sessionStorage.setItem(pName,pQty);      
   }

if the product already exists in the session, the function will add the current required quantity to the existing session variable.

Conclusion

In this article, I explored the Web SQL database API and its usage by implementing a shopping cart application.

Acknowledgments

The author would like to thank Raghavendran N, Principal, Education and Research Department (E&R) for his guidance and continuous support. I would like to thank Mr. Satheesha B N (AVP) and SVS (VP), E&R for motivating me to write this article. I would like to thank Mrs. Sangeetha for reviewing this article and providing valuable suggestions.

About the Author

Yuvarani Meiyappan works as a lead in the E&R division of Infosys Technologies Limited. She has over eight years of experience in design and development of Java and Java EE applications.