HTML5 offline article

Home >> HTML5 offline >>

  HTML5 offline database CRUD operation

This Article will explain you about HTML5 offline Database CRUD Operation using Web SQL Database. Web SQL Database will work with latest version of Safari, Chrome and Opera..

The Core Methods:
There are following three core methods defined in this article

openDatabase :- This method creates the database object either using existing database or creating a new one.

transaction: - This method gives us the ability to control a transaction and performing either commit or rollback based on the situation.

executeSql :- This method is used to execute actual SQL query.

Opening Database Method
The openDatabase method takes care of opening a database, If the database already exists, this method will avoid creation of database and If database does not exists this method will create it.

Like below example :

var mywebdb = openDatabase('websql_db', '1.0', 'phpcodehub WebSql Test DB', 2 * 1024 * 1024);

Above method took following five parameters:

1. Database name
2. Version number
3. Text description
4. Size of database
5. Creation callback

The last and 5th argument, creation callback will be called if the database is being created. Without this feature, however, the databases are still being created on the fly and correctly.

NOTE : Make sure that every transaction method will invoke by your openDatabase method reference variable, in my case I am using "mywebdb" variable as reference variable.

Transaction Method

The transaction method takes care of executing a query in a table and all operations will take place inside the transaction method

Like below example :

mywebdb.transaction(function(tx) 
{
      // Your query will execute here
});

ExecuteSql Method

executeSql method takes care of executing a query in a table and all operations will take place inside the transaction method

Like below example :

mywebdb.transaction(function(tx) 
{
    void executeSql(sqlStatement, argumentsWithinSquareBrackets, sqlStatementCallback, sqlStatementErrorCallback)
});

Above method took following four parameters:

1. sqlStatement : This is your query.
2. argumentsWithinSquareBrackets : This is your query opeartion values.
3. sqlStatementCallback : This will call when your query will execute successfully.
4. sqlStatementErrorCallback : This will call when something is wrong with your  query.

Note : But sqlStatementCallback, sqlStatementErrorCallback are optional.

Now let's start example, with real time data

Creating a Database

var mywebdb = openDatabase('websql_db', '1.0', 'phpcodehub WebSql Test DB', 2 * 1024 * 1024);

Create Table 

// Create table
function createTable() 
{ 
    mywebdb.transaction(function(tx) 
    {
        tx.executeSql("CREATE TABLE IF NOT EXISTS user_details (user_id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, email TEXT, active_flag INTEGER default '1')", []);
    });
}

Insert record Into table 

// Insert user details.
function saveUserDetails()
{
        mywebdb.transaction(function (tx) 
        {
            var name_val = 'phpcodehub';
            var email_val = 'phpcodehub@gmail.com';
            
            tx.executeSql("INSERT INTO user_details (name, email) VALUES (?, ?);", 
            [name_val, email_val],showSavedUserDetails(), onError);
                                    
        });
} 

showSavedUserDetails(); function will call when your query will execute successfully and then it will alert the saved record.

onError(); function will call when something is wrong with query and then it will alert the sql error.

// It will show query error if something is wrong with query.
function onError(tx, error) 
{
   alert(error.message);
} 

Select record from table

// Select user details.
function showSavedUserDetails()
{
    mywebdb.transaction(function (tx) 
    {
          tx.executeSql('SELECT user_id, name, email FROM user_details', [], function (tx, results) 
          {
               var total_rec = results.rows.length;
               //alert("Total record  =  " +total_rec);
               
                   for (i = 0; i < total_rec; i++)
                   {
                      var record_data =  results.rows.item(i);
                      var user_name  = (record_data.name);
                      var user_email = (record_data.email);

                      alert("User Name = "+user_name+" And Email = "+user_email);
                   }       

          }, null);
    });
}

Update  record Into table

// Update user details.
function updateUserDetails() 
{
    var name_val = 'Change Name';
    var email_val = 'change@gmail.com;
    var update_user_id = '1';

     mywebdb.transaction(function(tx) 
     {
       tx.executeSql("UPDATE user_details SET name = ?, email = ? WHERE user_id = ?", 
       [name_val,email_val, update_user_id],showSavedUserDetails(), onError);
     }); 
}

Delete  record from table

// Delete user details.
function deleteUserRecord() 
{ 
   var delete_user_id = 1;
    
   mywebdb.transaction(function(tx) 
   {
      tx.executeSql('DELETE FROM user_details WHERE user_id = "'+delete_user_id+'" ');
   });

   showSavedUserDetails();
}

Final Demo will look like below Image

  Demo   Download code

Comments

It's amazing

I was just looking here and there finally I got it.

  1 year ago      Kick Johnson      
It's really helpfull
Re : It's amazing.

It's really helpfull

  1 year ago      Praveen      
well done

nice explanation and specially demo was superb. I was looking for the same

  10 months ago      Amit Kumar      

Write your comment now