Online Certificate Courses HTML5 Course. HTML5 Certification, HTML5 Training, HTML5 Tutorials. 

What is the Web SQL Database

Actually, The Web SQL Database API is not part of the released HTML5 specification, it is a specification that is on its own, it comes with a set of APIs that is meant for manipulating client-side databases with the use of SQL

Web SQL Database is available on the latest version of Chrome, Opera and Safari.

The Core Methods

The following are the three core methods that were defined in the specification.

  • open database: This core method generates the database object either by using an existing database or bootstrapping a new one.

  • transaction: This core method only gives us the ability to control a transaction and performing either commit or rollback based on the situation.

  • execute SQL: This core method is meant for executing the main SQL query.

Opening Database

The open database builtin function takes care of opening a database if it already exists, this method will create it if it already does not exist.

To generate and launch a database, you can use the following code:

var databBase = openDatabase('mydataBase', '2.1', 'Example DB', 2 * 1024 * 1024);

The method above takes the following parameters:

  • Database name
  • Version number
  • Text description
  • Size of database
  • Creation callback

In the 5th and last arguments, the creation callback function will be initiated when the database is being created. Without this particular feature, the databases will still be created and correctly versioned.

Executing Queries

In other to execute a database query you need to use the database.transaction() method. This method needs just one argument, which is a callback function that helps to actually execute the query, an example below demonstrates how executing queries is done:

var db = openDatabase('logger', '2.1', 'Example DB', 2 * 1024 * 1024); 

db.transaction(function (tx) {   
   tx.executeSql('CREATE TABLE IF NOT EXISTS DARA (id unique, log)'); 
});

The query above will create a table called LOGS in the 'logger' database.

INSERT Operation 

in other to generate entries into the database table we simply an SQL query in the above example:

var db = openDatabase('logger', '2.1', 'Example DB', 2 * 1024 * 1024); 

db.transaction(function (txn) { 
   txn.executeSql('CREATE TABLE IF NOT EXISTS LOGS (id unique, log)'); 
   txn.executeSql('INSERT INTO LOGS (id, log) VALUES (1, "barfoo")'); 
   txn.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "logmsg")'); 
});  

We can also pass in dynamic values as we are creating an entry like so:

var db = openDatabase('logger', '2.1', 'Example DB', 2 * 1024 * 1024);
db.transaction(function (txn) { txn.executeSql('CREATE TABLE IF NOT EXISTS LOGS (id unique, log)'); txn.executeSql('INSERT INTO LOGS (id,log) VALUES (?, ?'), [e_id, e_log]; });

in the code above e_id and e_log are all external variables and they execute SQL maps on each item in the array argument to the "?"s.

READ Operation

To read records that are already existing we can use a callback to get the results like below;

var db = openDatabase('mydataBase', '2.0', 'Example DB', 2 * 1024 * 1024);  

db.transaction(function (txn) { 
   txn.executeSql('CREATE TABLE IF NOT EXISTS LOGS (id unique, log)');
   txn.executeSql('INSERT INTO LOGS (id, log) VALUES (1, "foobar")'); 
   txn.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "logmsg")'); 
});  

db.transaction(function (txn) { 
   txn.executeSql('SELECT * FROM LOGS', [], function (txn, results) { 
      var len = results.rows.length, i; 
      messag = "<p>Found the following rows: " + len + "</p>"; 
      document.querySelector('#statuses').innerHTML +=  messag; 
  
      for (i = 0; i < len; i++) { 
         alert(results.rows.item(i).log ); 
      } 
  
   }, null); 
});

Final Example 

So finally, let us keep this example in a full-fledged HTML5 document as follows and try to run it with the Safari browser. 

<!DOCTYPE HTML>
<html>  
   <head> 
      <script type = "text/javascript"> 
         var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024); 
         var msg; 
    
         db.transaction(function (txn) { 
            txn.executeSql('CREATE TABLE IF NOT EXISTS LOGS (id unique, log)'); 
            txn.executeSql('INSERT INTO LOGS (id, log) VALUES (1, "foobar")'); 
            txn.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "logmsg")'); 
            message = '<p>Log message has been created and row has inserted.</p>'; 
            document.querySelector('#statuses').innerHTML =  message; 
         })

         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>

90% Scholarship Offer!!

The Scholarship offer is a discount program to take our Course Programs and Certification valued at $70 USD for a reduced fee of $7 USD. - Offer Closes Soon!!

Copyrights © 2021. SIIT - Scholars International Institute of Technology is a subsidiary of Scholars Global Tech. All Rights Reserved.