Hello, world!

HTML 5 Web SQL Database HTML

Some of the important Web SQL Database topic discussed in the HTML tutorial are the core methods, performing queries, opening database, read operation and insert operation.



HTML5 - Web SQL Database

Web SQL Database can be execute in the recent version of Safari, Chrome and Opera.

Core Methods

There are the three core methods included in this tutorial:
openDatabase - This method produces the database object either using the given database or constructing a new one.
transaction - This method enables us to manage a transaction and operating either commit or rollback according to the situation.
executeSql - This method is applied to perform actual SQL query.

Performing Queries

You can apply the database.transaction() function to perform a query.
This function requires a single argument that is a function that is in charged of essentially generating the query as follows:

HTML code example performing queries:

var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
db.transaction(function (tx) {
   tx.executeSql('CREATE TABLE IF LOGS NOT EXISTS (id unique, log)');
});
The above query will build a table called LOGS in 'mydb' database.

Opening Database

The openDatabase method is in charge of opening a database if it previously present, this method will build it if it previously does not exist.
Apply the following code to build and open a database:
var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
The above method acquired the following five parameters:Database name, Version number, Text description, Size of database, Creation callback
If the database is being generated, the last and 5th argument, creation callback will be invoked. Nevertheless without this attribute, the databases are still being produced on the fly and properly versioned.

READ Operation

This example will apply a callback to capture the outputs in order to read previously present records.
HTML code example read operation:
var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
db.transaction(function (tx) {
   tx.executeSql('CREATE TABLE IF LOGS NOT EXISTS (id unique, log)');
   tx.executeSql('INSERT INTO LOGS (id, log) VALUES (1, "foobar")');
   tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "logmsg")');
});
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++){
         alert(results.rows.item(i).log );
      }
            
   }, null);
});

INSERT Operation

This example will add simple SQL query to create entries into the table of the the above query:

HTML code example insert operation:

var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
db.transaction(function (tx) {
   tx.executeSql('CREATE TABLE IF LOGS NOT EXISTS (id unique, log)');
   tx.executeSql('INSERT INTO LOGS (id, log) VALUES (1, "foobar")');
   tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "logmsg")');
});

This example will create entering, at the same time we can submit dynamic values.

HTML code example insert operation:

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

The e_id and e_log here are external variables, and executeSql maps every entry in the array argument to the "?"s.

Final Example

And lastly, let us carry on this following example in full pledged HTML5 web content and try to generate it with Safari browser.

HTML code example final example:

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