HTML5 Web SQL database
The Web SQL database API is not part of the HTML5 specification, but it is a separate specification that introduces a set of APIs that uses SQL to operate on client-side databases.
If you are a Web back-end programmer, it should be easy to understand the operation of SQL.
The Web SQL database works in the latest versions of Safari, Chrome and Opera browsers.
Core method
The following are the three core methods defined in the specification:
openDatabasethis method creates a database object using either an existing database or a newly created databasetransactionthis method allows us to control a transaction and perform a commit or rollback based on this situation.executeSqlthis method is used to execute the actual SQL query
Open the database
We can use it. openDatabase() method to open an existing database, and if the database does not exist, a new database is created, using the following code:
var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
openDatabase() the five parameters corresponding to the method describe:
Database name
Version number
Description text
Database size
Create callback
The fifth parameter, the creation callback will be called after the database is created.
Perform query operation
Perform operations to use database.transaction() function:
vardb=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 statement, when executed, creates a table named LOGS in the ‘mydb’ database.
Insert data
After executing the above create table statement, we can insert some data:
vardb=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, "Rookie Tutorial")');tx.executeSql('INSERT INTO LOGS
(id, log) VALUES (2, "www.runoob.com")');});
We can also use dynamic values to insert data:
vardb=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]);});
In the instance e_id and e_log is an external variable executeSql each entry in the array parameter is mapped to “?”.
Read data
The following example shows how to read data that already exists in the database:
vardb=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, "Rookie Tutorial")');tx.executeSql('INSERT INTO LOGS
(id, log) VALUES (2,
"www.runoob.com")');});db.transaction(function(tx){tx.executeSql('SELECT
\* FROM
LOGS',[],function(tx,results){varlen=results.rows.length,i;msg="<p>Number of query records:"+len+"</p>";document.querySelector('#status').innerHTML+=msg;for(i=0;i<len;i++){alert(results.rows.item(i).log);}},null);});
Complete instance
Example
vardb=openDatabase('mydb','1.0','Test
DB',2\*1024\*1024);varmsg;db.transaction(function(tx){tx.executeSql('CREATE
TABLE IF NOT EXISTS LOGS (id unique, log)');tx.executeSql('INSERT INTO
LOGS (id, log) VALUES (1, "Rookie Tutorial")');tx.executeSql('INSERT INTO LOGS
(id, log) VALUES (2,
"www.runoob.com")');msg='<p>The data table has been created and two pieces of data have been inserted.</p>';document.querySelector('#status').innerHTML=msg;});db.transaction(function(tx){tx.executeSql('SELECT
\* FROM
LOGS',[],function(tx,results){varlen=results.rows.length,i;msg="<p>Number of query 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);});
The running result of the above example is shown in the following figure:
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 to update records is as follows:
db.transaction(function (tx) {
tx.executeSql('UPDATE LOGS SET log=\'www.w3cschool.cc\' WHERE id=2');
});
Updating 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 instance
Example
vardb=openDatabase('mydb','1.0','Test
DB',2\*1024\*1024);varmsg;db.transaction(function(tx){tx.executeSql('CREATE
TABLE IF NOT EXISTS LOGS (id unique, log)');tx.executeSql('INSERT INTO
LOGS (id, log) VALUES (1, "Rookie Tutorial")');tx.executeSql('INSERT INTO LOGS
(id, log) VALUES (2,
"www.runoob.com")');msg='<p>The data table has been created and two pieces of 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 id is 1
record.</p>';document.querySelector('#status').innerHTML=msg;});db.transaction(function(tx){tx.executeSql('UPDATE
LOGS SET log=\\'www.w3cschool.cc\\'WHERE id=2');msg='<p>Update id to 2
record.</p>';document.querySelector('#status').innerHTML=msg;});db.transaction(function(tx){tx.executeSql('SELECT
\* FROM
LOGS',[],function(tx,results){varlen=results.rows.length,i;msg="<p>Number of query 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);});
The running result of the above example is shown in the following figure:
