Latest web development tutorials

HTML5 Web SQL Database

Web SQL Database API is not part of HTML5 specification, but it is a separate specification, introduced a set of APIs to use SQL operation client database.

If you are a Web programmer rear end, it should be easy to understand SQL operations.

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

Web SQL Database can work in the latest version of Safari, Chrome and Opera browser.


Core methods

The following are the three core methods defined in the specification:

  1. openDatabase: This method uses an existing database or create a new database object database.
  2. transaction: This method allows us to control a transaction, and based on this case commit or rollback.
  3. executeSql: This method is used to perform the actual SQL query.

Open Database

We can use openDatabase () method to open an existing database, if the database does not exist, create a new database, use the following code:

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

openDatabase () method of the five parameters corresponding to the description:

  1. Database Name
  2. version number
  3. Description Text
  4. Database Size
  5. Create a callback

The fifth parameter, create a callback will be called after creating the database.


To perform query operations

Execution uses 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)');
});

The above statement creates a table named LOGS in the 'mydb' database after execution.


Insert data

After executing the above statement to create the table, 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, "本教程")');
   tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "www.w3big.com")');
});

We can also use a dynamic value to insert the 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];
});

Instance e_id e_log and external variables, executeSql array parameter is mapped to each entry in "?."


Read data

The following example demonstrates how to read the data already 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, "本教程")');
   tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "www.w3big.com")');
});

db.transaction(function (tx) {
   tx.executeSql('SELECT * FROM LOGS', [], function (tx, results) {
      var len = results.rows.length, i;
      msg = "<p>查询记录条数: " + len + "</p>";
      document.querySelector('#status').innerHTML +=  msg;
	
      for (i = 0; i < len; i++){
         alert(results.rows.item(i).log );
      }
	
   }, null);
});

Complete example

<!DOCTYPE HTML>
<html>

   <head>
	
      <script type="text/javascript">
		
         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, "本教程")');
            tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "www.w3big.com")');
            msg = '<p>数据表已创建,且插入了两条数据。</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>查询记录条数: " + 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>
      <div id="status" name="status">状态信息</div>
   </body>
	
</html>

try it"

Examples of the above operating results as shown below:


Delete Record

Format delete records are as follows:

db.transaction(function (tx) {
    tx.executeSql('DELETE FROM LOGS  WHERE id=1');
});

Remove the specified data id can also be dynamic:

db.transaction(function(tx) {
    tx.executeSql('DELETE FROM LOGS WHERE id=?', [id]);
});

update record

Update records using the following format:

db.transaction(function (tx) {
    tx.executeSql('UPDATE LOGS SET log=\'www.w3cschool.cc\' WHERE id=2');
});

Updates 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

<!DOCTYPE HTML>
<html>
   <head>
      <meta charset="UTF-8">  
      <script type="text/javascript">
      
         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, "本教程")');
            tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "www.w3big.com")');
            msg = '<p>数据表已创建,且插入了两条数据。</p>';
            document.querySelector('#status').innerHTML =  msg;
         });

         db.transaction(function (tx) {
              tx.executeSql('DELETE FROM LOGS  WHERE id=1');
              msg = '<p>删除 id 为 1 的记录。</p>';
              document.querySelector('#status').innerHTML =  msg;
         });

         db.transaction(function (tx) {
             tx.executeSql('UPDATE LOGS SET log=\'www.w3cschool.cc\' WHERE id=2');
              msg = '<p>更新 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>查询记录条数: " + 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>
      <div id="status" name="status">状态信息</div>
   </body>
   
</html>

try it"

Examples of the above operating results as shown below: