What is Web SQL?

Web SQL Database is a web page used for storing or managing the data in the database. The API is supported by Google Chrome, Opera and Android browsers.

The Web SQL API is not a part of the HTML5 specification, but is a separate specification. It addresses a set of APIs to manipulate the client-side database.

Open databases, transactions, are the basic methods to execute.

The W3C web application is working on the specification in November 2010. It was specifying a lack of independent implementations because the specification cannot proceed to become a W3C recommendation.

Mozilla Corporation was one of the key behind the standard at the same time being the prime mover behind IndexDB, is an alternative storage standard.

The Web API is not the part of HTML5 specification. It is a separate specification which specifies a set of APIs to manipulate the client-side database.

The web SQL database works in the newest version of Safari, Chrome and Opera.

Methods of Web SQL:

There are three methods -

Open Database - It creates a database object using the existing database or also to create a new one.
Transaction - Transaction can control a transaction and commit or rollback depending upon the situation.
Execute SQL - It is used to execute a real SQL query.

Database opening

The Open Database method opening the database if it already exists. Use the code below to create and open the database-

The above method took the below five parameters -

Database Name
version number
Text Description
Database size
build call-back

In the last and 5th arguments, the creation call-back will be called if the database is being created.

We use the database.transaction () to execute a query. It has a single argument, that executing the query as 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, log)'); 
});

The above code generates a table named LOGS in 'mydb' database.

INSERT Operation

To create entries in the table, we execute a SQL query, as follows:

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, log)'); 
   tx.executeSql('INSERT INTO LOGS (id, log) VALUES (1, "foobar")'); 
   tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "logmsg")'); 
}); 

We can pass dynamic values while creating entering as follows -

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, log)'); 
   tx.executeSql('INSERT INTO LOGS (id,log) VALUES (?, ?'), [e_id, e_log]; 
});

e_id and e_log are the external variables here, and executes every item in the array.

READ Operation

To read already existing records we use a callback to capture the results as follows -

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, 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); 
});

Example

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 (tx) { 
            tx.executeSql('CREATE TABLE IF NOT EXISTS LOGS (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>

The above code produces the following result:

Log message created and row inserted.
Found rows: 2
foobar
logmsg

Next TopicHow to create functions in SQL

← prev next →

Latest Courses

We provides tutorials and interview questions of all technology like java tutorial, android, java frameworks

Contact info

G-13, 2nd Floor, Sec-3, Noida, UP, 201301, India

[email protected].

Python

AI, ML and Data Science

Java

B.Tech and MCA

Web Technology

Software Testing

Technical Interview

Java Interview

Web Interview

Database Interview

Company Interviews

SQL Tutorial

SQL Database

SQL Table

SQL Select

SQL Clause

SQL Order By

SQL Insert

SQL Update

SQL Delete

SQL Join

SQL Keys

Difference

SQL Injection

SQL String Functions

Misc

SQL MCQ

PL/SQL Tutorial

Interview

Quiz

What is Web SQL?

Methods of Web SQL:

Database opening

INSERT Operation

READ Operation

Example

Latest Courses

Contact info

Follow us

Tutorials

Interview Questions

Online Compiler