HTML5 Web SQL database

HTML5 Web SQL Database

The Web SQL Database API is not part of the HTML5 specification, but it 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 SQL operations.

You can also refer to our SQL tutorial to learn more about database operations.

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 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, the create 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 queries. This function requires one parameter, which is a function responsible for actually executing the query, 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 above query statement will create a table called LOGS in the 'mydb' database.

Insert operation

In order to create entries 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")'); });

You can also pass when creating entries 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)'); tx.executeSql('INSERT INTO LOGS (id,log) VALUES (?, ?'), [e_id, e_log]; });

Here e_id and e_log are external variables, executeSql will map each entry in the array parameter to "?".

Read operation

To read an already existing record, we can use a callback to capture the result, 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)'); 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 = "
Found rows: " + len + "
"; document.querySelector('#status').innerHTML += msg; for (i = 0; i < len; i++){ alert(results.rows.item(i).log ); } }, null); });

Final example

      
  
状态信息
  

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 the record 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]);});