HTML5 - SPLessons

HTML5 Web SQL

Chapter 20

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

HTML5 Web SQL

HTML5 Web SQL

shape Introduction

HTML5 Web SQL Databases introduces a set of APIs to manipulate the client side database and is not a part of HTML5. Following are the concepts covered.

  • What is Web SQL Database
  • Creating Database
  • Execution of Web SQL

What is Web SQL Database

shape Description

Web SQL database is a web page API to store the data in databases.Actually SQL is not a part of HTML5. In order to store the data, HTML5 introduced some methods as follows.

Creating Database

shape Description

In order to use Web SQL, initially open the existing data base, if not create the new database. The code below is used to create or open the database.

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

The above code parameters are described below.

Now use the transaction() function which is used to execute the queries and transaction function need only one argument code as shown below.

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

db.transaction(function (tx) {

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

});

Then enter the table data by adding simple queries to the code as shown below.

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

db.transaction(function (tx) {

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

tx.executeSql('INSERT INTO USER (id, name) VALUES (1, "foobar")');

tx.executeSql('INSERT INTO USER (id, name) VALUES (2, "logmsg")');

tx.executeSql('INSERT INTO USER (id, name) VALUES (3, "lomsg")');

Execution of Web SQL

shape Description

Now execute the Web SQL. After creating the Database and inserting the table data, create and execute the Web SQL on a web pages as shown below.

<!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 LOGS (id unique, user)');
            tx.executeSql('INSERT INTO USER (id, name) VALUES (1, "foobar")');
            tx.executeSql('INSERT INTO USER (id, name) VALUES (2, "logmsg")');
			tx.executeSql('INSERT INTO USER (id, name) VALUES (3, "lomsg")');
            msg = '<p>User message created and row inserted.</p>';
            document.querySelector('#status').innerHTML =  msg;
         });

         db.transaction(function (tx) {
            tx.executeSql('SELECT * FROM USER', [], 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>

Result

By running the above code in a preferred browser following output is obtained.

Summary

shape Points

  • Web SQL use set of APIs for the client side database.
  • Call back is used in created databases only.
  • Web SQL works in latest browsers only.