HTML5 Web SQL
HTML5 Web SQL database
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.
If you are a web back-end programmer, it should be easy to understand the operation of SQL.
You can also refer to our SQL tutorial to learn more about database operations.
The Web SQL database can work in the latest versions of Safari, Chrome and Opera browsers.
Core Method
The following are the three core methods defined in the specification:
openDatabase: This method uses an existing database or a newly created database to create a database object.
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
We can use the openDatabase() method to open an existing database. If the database does not exist, a new database will be created. The code is as follows:
var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
The five parameter descriptions corresponding to the openDatabase() method:
Name database
version number
Description text
Database size
Create callback
The fifth parameter, the creation callback will be called after the database is created.
Perform Query Operations
To perform operations, use the database.transaction() function:
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)');
});
After the above statement is executed, a table named LOGS will be created in the'mydb' database.
Insert Data
After executing the above create table statement, we can insert some data:
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, "TutorialFish")');
tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "www.tutorialfish.com")');
});
We can also use dynamic values to insert data:
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]);
});
The e_id and e_log in the example are external variables, and executeSql will map each entry in the array parameter to "?".
Read Data
The following example demonstrates how to read data that already exists in the database:
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, "Tutorial Fish")');
tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "www.tutorialfish.com")');
});
db.transaction(function (tx) {
tx.executeSql('SELECT * FROM LOGS', [], function (tx, results) {
var len = results.rows.length, i;
msg = "<p>Query the number of records: "+ len + "</p>";
document.querySelector('#status').innerHTML += msg;
for (i = 0; i <len; i++){
alert(results.rows.item(i).log );
}
}, null);
});
Complete Example
Example
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, "Tutorial Fish")');
tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "www.tutorialfish.com")');
msg ='<p>The data table has been created, and two more data have been 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>Query the number of records: "+ 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);
});
Delete Record
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 for 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]);
});
Complete Example
Example
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, "Tutorial Fish")');
tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "www.tutorialfish.com")');
msg ='<p>The data table has been created, and two more data have been inserted. </p>';
document.querySelector('#status').innerHTML = msg;
});
db.transaction(function (tx) {
tx.executeSql('DELETE FROM LOGS WHERE id=1');
msg ='<p>Delete the record with id 1. </p>';
document.querySelector('#status').innerHTML = msg;
});
db.transaction(function (tx) {
tx.executeSql('UPDATE LOGS SET log=\'www.tutorialfish.com\' WHERE id=2');
msg ='<p>Update the record with id 2. </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>Query the number of records: "+ 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);
});