HTML 5 Web SQL Core Trident

The "51CTO translation" Web SQL database API, which is not actually contained in the HTML 5 specification, is a stand-alone specification that introduces a set of APIs that use SQL to manipulate the client database. Assuming you're a good web developer, there's no doubt that you're familiar with the concepts of SQL and databases, and if you're unfamiliar with SQL, it's a good idea to learn about SQL-related tutorials before you continue reading this article.

The latest version of Chrome,safari and opera browsers support the Web SQL database.

Core approach

This article describes the three core methods defined in the specification:

1. OpenDatabase: This method creates a database object using an existing database or creating a new database.

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

3, ExecuteSQL: This method is used to execute the real SQL query.

Open Database

The OpenDatabase method opens a database that already exists, and if the database does not exist, it can also create the database, and the syntax for creating and opening the database is as follows:

 
  
 

  
   
  
var db = OpenDatabase (' mydb ', ' 1.0 ', ' Test db ', 2 * 1024 * 1024);
 
  
 

The OpenDatabase method above 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 callback

The last, the fifth parameter "create callback," is invoked when the database is created, but even without this parameter, the database can be created at run time.

Execute Query

The Execute query uses the Database.transaction () function, which requires only one argument, and the following is a real query:

 
  
 

  
   
  
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 query above will create a logs table in the "MyDB" database.

Insert operation

To insert a new record into the table, we added a simple SQL query to the query above, with the following modified statement:

 
  
 

  
   
  
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 inserting new records, we can also pass dynamic values, such as:

 
  
 

  
   
  
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 the e_id and E_log are external variables, ExecuteSQL map each item to "?" in the array argument.

Read operations

If you want to read a record that already exists, we 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 = "
found rows:" + len + "
";
  
  

  
Document.queryselector (' #status '). InnerHTML + msg;
  
  

  
for (i = 0; i < len; i++) {
  
  

  
Alert (Results.rows.item (i). log);
  
  

  
}
  
  

  
}, NULL);
  
  

  
});
 
 

A complete example

Finally, we present the previous story in a complete HTML 5 document and use the browser to parse the HTML 5 document.

 
 

  
  

  

  
  

  

  
  

  

  
  

  

  
  

  

  
  

  

  
  

  

   

    status message
   
  
  

  

  
  

  

 
 

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

 
  
 

  
   
  
Log message created and row inserted.
  
   
  

  
   
  
Found Rows:2
  
   
  

  
   
  
Foobar
  
   
  

  
   
  
Logmsg
 
  
 

Original link: http://www.tutorialspoint.com/html5/html5_web_sql.htm

"Edit Recommendation"