The Web SQL Database API is not part of the HTML5 specification, but it is an independent specification that introduces a set of APIs for operating client databases using SQL.
Assuming that you are a good web developer, if so, there is no doubt that you will be well aware of the concepts of SQL and RDBMS. If you still need a SQL topic, check out our SQL tutorials.
We can use the Web SQL database in the latest versions of Safari, Chrome and Opera.
Core methods
The following are the three core methods defined in the specification. Also covered in this tutorial:
openDatabase: This method creates a database object using an existing database or a new database.
transaction: This method allows us to control a transaction and perform commit or rollback based on this situation.
executeSql: This method is used to execute the actual SQL query.
Open the database
If the database already exists, the openDatabase method is responsible for opening the database. If it does not exist, this method will create it.
Use the following code to create and open a database:
var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
The above method accepts the following five parameters:
Database name
Version number
Description text
Database size
Create callback
The last and fifth parameter, create The callback will be called after the database is created. However, even without this feature, the runtime will still create the database and the correct version.
Execute query
You need to use the database.transaction() function to execute query. This function takes one parameter, which is a function responsible for actually executing the 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)');
});
The above query statement will create a table called LOGS in the 'mydb' database.
Insert operation
In order to create an entry in the table, we add a simple SQL query to the above example 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")');
});
When creating an entry, you can also pass dynamic values as shown 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 e_id and e_log here are external variables, and executeSql will map each entry in the array parameter to "?". Read operation
To read an existing record, we can use a callback to capture the result, 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);
});
Final example
Finally, let’s put this example into a complete HTML5 document as shown below, and then try to run it in 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>In the latest version of Safari or Opera browser, this will generate the following results:
Log message created and row inserted.Found rows : 2foobar
logmsg
Delete records
The format used to delete records is as follows:
db.transaction(function (tx) {
tx. executeSql('DELETE FROM LOGS WHERE id=1');
});
Deleting the specified data id can also be dynamic:
db.transaction(function (tx) {
tx.executeSql('DELETE FROM LOGS WHERE id=?', [id]);
});
Update record
The format used to update records is as follows:
db.transaction(function (tx) {
tx.executeSql('UPDATE LOGS SET log=\'www. w3cschool.cc\' WHERE id=2');
});
Update the specified data id can also be dynamic:
db.transaction(function( tx) {
tx.executeSql('UPDATE LOGS SET log=\'www.w3cschool.cc\' WHERE id=?', [id]);
});
