Overview

This Article demonstrates usage of database operations in Web Application. All the data and code is taken from open source library html5sql.js.

HTML5SQL Application

html5sql is a light JavaScript module that makes working with the HTML5 Web Database a whole lot easier. Its primary function is to provide a structure for the SEQUENTIAL processing of SQL statements within a single transaction.

Prerequisites

The following open source javascript library has to be included in the html page

<script type="text/javascript" src="./js/html5sql.js"></script>

HTML Page

The web Page content contains a div elements for displaying results on the content page. The page footer contains buttons to show database functionalities like create, delete and show. The html code is shown below.

<div data-role="page">
  <div data-role="header" data-position="fixed">
    <h1>HTML5SQL Demo</h1>
  </div>
  <div data-role="content">
    <div id="successfullStatements"></div>
  </div>
  <div data-role="footer" data-position="fixed">
    <div data-role="tabbar">
      <ul>
        <li><a id="createTable">Create</a></li>
        <li><a id="showTable">Show</a></li>
        <li><a id="deleteTable">Delete</a></li>
      </ul>
    </div>
  </div>
</div>

Javascript

To create database, the following sql query is passed to html5sql library to create database.

function createTable()
{
  var successfullStatements = $("#successfullStatements"),
  errors = $("#errors");
  html5sql.process(
	["DROP TABLE IF EXISTS StarWarsCharacters;",
	 "CREATE TABLE IF NOT EXISTS StarWarsCharacters (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);",
	"INSERT INTO StarWarsCharacters (name) VALUES ('Antony');",
        "INSERT INTO StarWarsCharacters (name) VALUES ('BroadMan');",
	"INSERT INTO StarWarsCharacters (name) VALUES ('Donald');",
	"INSERT INTO StarWarsCharacters (name) VALUES ('Esibella Leia');",
	 ],
	function(){
	   alert("Created Table");
        }, function(error, statement){
            alert("Failed to create Table" + error.msg);
        }
}

The below code is used to retrieve the data from database

function showTable(){

    var successfullStatements = $("#successfullStatements"),
        errors = $("#errors")

    html5sql.process(
         ["SELECT * FROM StarWarsCharacters;"],
         function(transaction, results, rowsArray){
             for(var i = 0; i < rowsArray.length; i++){
                 successfullStatements.append("<li>Retrieved" +rowsArray[i].name+"</li>");
             }
             successfullStatements.append("<li>Done selecting data</li>");
         },
         function(error, statement){
             errors.append("<li>"+error.message+" Occured while processing: "+statement+"</li>");
         }
    );

}

To delete the table from dataase, “drop” sql query is used as shown in below code.

function deleteTable(){
	 html5sql.process( ["drop table if exists StarWarsCharacters"], function(){
		alert("Deleted the table");
	 } , function(error, statement){
		 console.log("error message"+ error.message);
	 });

}

Screenshots

Below is the screenshot of the HTML5SQL Application is shown below