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:
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:
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 -
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 -
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.
<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:
Found rows: 2
foobar
logmsg
