HTML 5 Web SQL Database Core Trident

Author：Eve Cole Update Time：2025-03-29 22:32:02

The Web SQL database API is not actually included in the HTML 5 specification. It is an independent specification that introduces a set of APIs that use SQL to operate client databases. Assuming you are an excellent web developer, there is no doubt that you are already familiar with the concepts related to SQL and databases. If you are not familiar with SQL, then continue reading. Before this article, it is best to learn SQL-related tutorials first.

The latest versions of Chrome, Safari, and Opera all support Web SQL databases.

Core Methods

This article will introduce three core methods defined in the specification:

1. openDatabase: This method uses an existing database or creates a new database to create a database object.

2. Transaction: This method allows us to control transaction commit or rollback according to the situation.

3. executeSql: This method is used to execute real SQL queries.

Open the database

The openDatabase method opens an existing database. If the database does not exist, it can also create a database. The syntax for creating and opening the database is as follows:

 vardb=openDatabase('mydb','1.0','TestDB',2*1024*1024);

The above openDatabase method uses the following five parameters:

1. Database name (mydb)

2. Version number (1.0)

3. Description (Test DB)

4. Database size (2*1024*1024)

5. Create a callback

The last one, that is, the fifth parameter, creates a callback, which will be called when creating the database, but even without this parameter, the database can be created at runtime.

Execute a query

Execute query using the database.transaction() function, which only requires one parameter, and the following is a real query statement:

 vardb=openDatabase('mydb','1.0','TestDB',2*1024*1024);
 db.transaction(function(tx){
 tx.executeSql('CREATETABLEIFNOTEXISTSLOGS(idunique,log)');
 });

The above query will create a LOGS table in the mydb database.

Insert operation

In order to insert a new record into the table, we added a simple SQL query to the above query statement, and the modified statement is as follows:

 vardb=openDatabase('mydb','1.0','TestDB',2*1024*1024);
 db.transaction(function(tx){
 tx.executeSql('CREATETABLEIFNOTEXISTSLOGS(idunique,log)');
 tx.executeSql('INSERTINTOLOGS(id,log)VALUES(1,foobar)');
 tx.executeSql('INSERTINTOLOGS(id,log)VALUES(2,logmsg)');
 });

When inserting a new record, we can also pass dynamic values, such as:

 vardb=openDatabase('mydb','1.0','TestDB',2*1024*1024);
 db.transaction(function(tx){
 tx.executeSql('CREATETABLEIFNOTEXISTSLOGS(idunique,log)');
 tx.executeSql('INSERTINTOLOGS
 (id,log)VALUES(?,?'),[e_id,e_log];
 });

Here e_id and e_log are external variables, executeSql maps each item to ? in the array parameters.

Read operation

If you want to read the record that already exists, we use a callback to capture the result, the code is as follows:

 vardb=openDatabase('mydb','1.0','TestDB',2*1024*1024);
 db.transaction(function(tx){
 tx.executeSql('CREATETABLEIFNOTEXISTSLOGS(idunique,log)');
 tx.executeSql('INSERTINTOLOGS(id,log)VALUES(1,foobar)');
 tx.executeSql('INSERTINTOLOGS(id,log)VALUES(2,logmsg)');
 });
 db.transaction(function(tx){
 tx.executeSql('SELECT*FROMLOGS',[],function(tx,results){
 varlen=results.rows.length,i;
 msg=<p>Foundrows:+len+</p>;
 document.querySelector('#status').innerHTML+=msg;
 for(i=0;i<len;i++){
 alert(results.rows.item(i).log);
 }
 },null);
 });

Complete example

Finally, we present what was previously described in a complete HTML 5 document, while using a browser to parse the HTML 5 document.

 <!DOCTYPEHTML>
 <html>
 <head>
 <scripttype=text/javascript>
 vardb=openDatabase('mydb','1.0','TestDB',2*1024*1024);
 varmsg;
 db.transaction(function(tx){
 tx.executeSql('CREATETABLEIFNOTEXISTSLOGS(idunique,log)');
 tx.executeSql('INSERTINTOLOGS(id,log)VALUES(1,foobar)');
 tx.executeSql('INSERTINTOLOGS(id,log)VALUES(2,logmsg)');
 msg='<p>Logmessagecreatedandrowinserted.</p>';
 document.querySelector('#status').innerHTML=msg;
 });
 db.transaction(function(tx){
 tx.executeSql('SELECT*FROMLOGS',[],function(tx,results){
 varlen=results.rows.length,i;
 msg=<p>Foundrows:+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>
 <divid=statusname=status>StatusMessage</div>
 </body>
 </html>

Below is the output result produced in the latest version of Safari or Opera browser.

 Logmessagecreatedandrowinserted.
 Foundrows:2
 foobar
 logmsg