Introduction
HTML5 Web SQL Databases introduces a set of APIs to manipulate the client side database and is not a part of HTML5. Following are the concepts covered.
- What is Web SQL Database
- Creating Database
- Execution of Web SQL
Description
Web SQL database is a web page API to store the data in databases.Actually SQL is not a part of HTML5. In order to store the data, HTML5 introduced some methods as follows.
- openDatabase Used to create object of the Database. user can use the existing Database or create new one.
- transaction Used to get the control on a transaction to perform commit or roll back depends on situation.
- executeSql Used to execute the SQL Query.
Description
In order to use Web SQL, initially open the existing data base, if not create the new database. The code below is used to create or open the database.
[sql]
var db = openDatabase( ' mydb ' , ' 1.0 ' , ' Test DB ' , 2 * 1024 *1024);
[/sql]
The above code parameters are described below.
Now use the transaction() function which is used to execute the queries and transaction function need only one argument code as shown below.
[sql]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, user)');
});[/sql]
Then enter the table data by adding simple queries to the code as shown below.
[sql]
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, user)');
tx.executeSql('INSERT INTO USER (id, name) VALUES (1, "foobar")');
tx.executeSql('INSERT INTO USER (id, name) VALUES (2, "logmsg")');
tx.executeSql('INSERT INTO USER (id, name) VALUES (3, "lomsg")');
[/sql]
- Name of the database
- Version
- Description of text
- Database size
- Callback Creation
Description
Now execute the Web SQL. After creating the Database and inserting the table data, create and execute the Web SQL on a web pages as shown below.
[html]<!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, user)');
tx.executeSql('INSERT INTO USER (id, name) VALUES (1, "foobar")');
tx.executeSql('INSERT INTO USER (id, name) VALUES (2, "logmsg")');
tx.executeSql('INSERT INTO USER (id, name) VALUES (3, "lomsg")');
msg = '<p>User message created and row inserted.</p>';
document.querySelector('#status').innerHTML = msg;
});
db.transaction(function (tx) {
tx.executeSql('SELECT * FROM USER', [], 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>
[/html]
Result
By running the above code in a preferred browser following output is obtained.
Points
- Web SQL use set of APIs for the client side database.
- Call back is used in created databases only.
- Web SQL works in latest browsers only.