Interesting HTML5 Web SQL database

Posted by Kalland on Tue, 02 Jul 2019 20:41:27 +0200

The Web SQL database API is not part of the HTML5 specification, but it is an independent specification that introduces a set of APIs that operate client database using SQL.
Core approach
The following are the three core approaches defined in the specification:

openDatabase: This method creates a database object using an existing database or a newly created database.
Transaction: This approach allows us to control a transaction and perform commits or rollbacks based on this situation.
executeSql: This method is used to execute actual SQL queries.
Open the database
We can use the openDatabase() method to open an existing database. If the database does not exist, a new database will be created. The code is as follows:

var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
The five parameters of openDatabase() method are described as follows:

Database name
version number
Descriptive text
Database size
Create callbacks
The fifth parameter, creating callbacks, is invoked after creating the database.
Perform query operations
The database.transaction() function is used to perform the operation:

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

insert 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, "Newbie Course")');
   tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "www.runoob.com")');
});

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

<!DOCTYPE HTML>
<html>
   <head>
      <meta charset="UTF-8">
      <title></title> 
      <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, "Newbie Course")');
            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) {
               var len = 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);
         });
            
      </script>
        
   </head>
    
   <body>
      <div id="status" name="status">status information</div>
   </body>
    
</html>

Delete records

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 records
The format used for updating 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]);
});

<!DOCTYPE HTML>
<html>
   <head>
      <meta charset="UTF-8">  
       <title>Newbie Course(runoob.com)</title> 
      <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, "Newbie Course")');
            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 A record of 1.</p>';
              document.querySelector('#status').innerHTML =  msg;
         });

         db.transaction(function (tx) {
             tx.executeSql('UPDATE LOGS SET log=\'runoob.com\' WHERE id=2');
              msg = '<p>To update id 2 records.</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>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);
         });
         
      </script>
      
   </head>
   
   <body>
      <div id="status" name="status">status information</div>
   </body>
   
</html>

See from: https://www.runoob.com/html/html5-web-sql.html

Topics: PHP Database SQL html5 Javascript

Programmer Think

Interesting HTML5 Web SQL database

Hot Topics