WEB SQL

What is Web SQL?

In SQL we can create databases, read the data in the databases, insert the records into the databases, and delete the records from the databases. For storing the data and managing it, a web page API is used. This API (Application Programming Interface) is known as Web SQL.

Web SQL is an Application Programming Interface which helps developers in tackling some database operations from the client side.

Web SQL Database API does not come under HTML5 specification. Web SQL has a different specification.

Latest versions of Google Chrome, Opera, Safari, Android browsers accept the web SQL database.

This database is a deprecated web browser API specification for storing data in databases that can be queried using SQL variant.

Methods in Web SQL:

We have three main methods in Web SQL to access the data from the API.

openDatabase:

This function creates a new database. It  can also create the database object using the already existing database.

When we create a database, it can have four parameters:

• Database name
• Version number
• Description
• Size
• Creation Callback

Transaction objects are obtained as result objects for callback. The result object has rows object, length. For obtaining individual rows, results.rows.item (i) should be used.

When we invoke openDatabase Method, it opens the specified database. If in case the database mentioned is absent, then this method creates a new database with the given database name.

NOTE: When the database is created, the creation callback is called internally.

Syntax:

var jtpdb = openDatabase (name of the database, version number, description,      size);

Example:

Now let’s create a database by running the below query.

var jtpdb = openDatabase (‘mydatabase’, ‘2.0’, ‘This is a company info database.’, 2*512*512);
if (! jtpdb)
{
	alert (‘Database is not created!”);
}
else
{
	var version = jtpdb. version;
}

So, this creates a database. If database is not created, then it gives an alert message.

executeSql:

This method is used to execute the SQL query. We use database.transaction () to execute a query. This function takes in, a single argument.

Example:

var db = openDatabase (‘mydatabase’, ‘2.0’, ‘Test_DB’, 2*512*512);
db.transaction (function (txc) {
	txc. executeSql (‘CRERATE TABLE IF NOT EXISTS LOBBY (ID unique, log)’);
});

This query creates a table LOBBY in ‘mydatabase’ database.

Transaction:

Performing some kind of operations on a database, is known as a transaction. Controlling the transactions is done, using rollback and commit functions.

Creating transactions:

From our database instance we can use transaction function to create transactions.

Syntax:

Mydb transaction (function (tx) {});

Mydb is an existing database and tx is a transaction that is used for upcoming operations.

Transaction will be rollbacked if any operation throws an error. These can be managed using transactions.

Steps of transaction:

We have a few steps for executing a transaction. These steps are assured to run asynchronously.

 These steps are initiated with a transaction callback, optionally a success callback, optionally an error callback, optionally a post flight operation, optionally a preflight operation along with a write/read mode or read only mode.

1. We should open a new SQL transaction to the database. Then create a transaction object, specify the object with a mode. If the mode is read/write, the transaction should have an exclusive write lock for the entire database. If the mode selected is read-only mode, then the transaction should have a shared lock for the entire database.
2. The user agent must wait for the appropriate lock to be available.
3. If any error occurs, the jump over to the last step.
4. For this instance of the transaction steps, if a prefight operation is defined then run that. If that fails, then we need to jump to the last step.
5. If the callback raised an exception, jump to the last step.
6. Perform the following steps for each queued up statement in the transaction with the oldest first, while there are any statements queued up in the transaction.
   • Execute the statement, in the context of the transaction.
   • Create a SQL Result Set, which represents the result of the statement.
   • Jump to the last step, if the callback has invoked an raised the exception.
   • Move to the next step, if no other issues raised.
7. If success callback is not null, queue a task to invoke success callback.

The database task source is the task source for the above tasks.

Insert Operation:

For creating entries, we query the following statements.

var db = openDatabase (‘mydatabase’, ‘2.0’, ‘Test_DB’, 2*512*512);

var db = openDatabase (‘mydatabase’, ‘2.0’, ‘Test_DB’, 2*512*512);
jtpdb.transaction (function (tx) {
	tx.executeSq l(‘ CREATE TABLE IF NOT EXISTS CLASS (id unique, class)’);
	tx.executeSql (‘ INSERT INTO CLASS (id, class) VALUES(1, “hey”)’);
	tx.executeSql (‘ INSERT INTO CLASS (id, class) VALUES(2, “HELLO”)’);
var id = “1”
var String text = “heyy”
tx.executeSql (‘INSERT INTO CLASS (id, text) VALUES (?, ?)’, [id, text]);

So, in the last step, with the help of ‘?’ symbol we are retrieving the id and text values.

Read Operation:

By using insertion operation, we insert records into the database. So, after storing, if we want to retrive the records, we will use the read operation using callback function.

var db = openDatabase (‘mydatabase’, ‘2.0’, ‘Test_DB’, 2*512*512);
db.transaction (function (tx) {
	tx.executeSql (‘CREATE TABLE IF NOT EXISTS CLASS (id unique, class)’);
	tx.executeSq l(‘INSERT INTO CLASS (id, class) VALUES (1, “hey”)’);
  	   tx.executeSql (‘ INSERT INTO CLASS (id, class) VALUES(2, “HELLO”)’);
db.transaction ( function (tx) {
	tx.executeSql (‘SELECT * FROM CLASS’, [], function (tx, results) {
		var length = results.rows.length, i;
		message = “<p>Found rows: “ + length + “</p>”;
		document.querySelector (‘#status’). innerHTML += message;
		
		for (i = 0; I < length; i++)
		{
			alert (results.rows.item(i).class );
		}
	}, null ) ;
} ) ;

In the above query, we inserted the records with id’s 1 and 2 having text hey and hello respectively.

Next using the SQL keyword select, we are getting all the records from the CLASS database. Along with the records we are printing the length/ the number of records present in the CLASS, using rows.length function.