CRUD operation in PhoneGap application using SQLite Database

Introduction:
Few days ago I received a comment from one of the reader of my blog to show an example on CRUD (Create, Read, Update and Delete) with the database. So in this post we will explore the use of CRUD operation using SQLite database in PhoneGap application.

Description:
CRUD can be described as a process, which helps the user to add, view, search and modify the information inside the database.

Here are the basic steps which demonstrates CRUD operation.

  • Create or add new entries
  • Read, retrieve, search or view existing entries
  • Update, modify or edit existing entries
  • Delete, deactivate or destroy existing entries

Before doing any operation on database let’s initialize the database first, either using HTML5 local database concept or using SQLite plugin.

Let’s add the following javascript to initilze the database.

var databaseName =  "DummyDB";
var databaseVersion = "1.0";
var databaseDisplayName = "DummyDatabase";
var databaseSize =  2 * 1024 * 1024;
var myDB = "";

//Accessing with HTML5 local database
myDB = window.openDatabase(databaseName, databaseVersion , databaseDisplayName, databaseSize);

window.openDatabase helps to return the new database object. This method will create a the new SQLite database and also can be use for other data manipulation.

//Using SQLite plugin
myDB = window.sqlitePlugin.openDatabase({name : databaseName});

Above method will create the SQLite datebase with unlimited storage capacity. To integrate the SQLite plugin we can follow this post.

Now lets focus on the basic 4 steps of CRUD operation by using the database object already created by above step.

CREATE: Initializing or creating the table. We normally use two keywords CREATE and INSERT for this operation.

Example:

function CreateBusinessTable() {
            myDB.transaction(function(transaction) {
                transaction.executeSql('CREATE ' +
                           'TABLE IF NOT EXISTS ' +
                           'Business_Table (id integer primary key, business_id integer, business_name text)', [],
                    function(tx, result) {
                        console.log("Business table created successfully.");
                    }, 
                    function(error) {
                          console.log("Error occurred while creating the table.");
                    });
                });
}
function InsertBusinessData(){
        myDB.transaction(function(transaction) {
            // Define insert query
            var executeQuery = "INSERT INTO " +
                                "Business_Table" + 
                                "(business_id, business_name) "+
                                "VALUES(?,?)";
           	Helper.log(executeQuery);              
           	transaction.executeSql(executeQuery, ['1234', 'Mindfire']
                , function(tx, result) {   // On success
                     console.log('Business data inserted successfully.');
                },
                function(error){     // On error                               
                     console.log('Error occurred while inserting business data.'); 
                });
        });
}

READ: Use to get, search or retrieve entries from a table inside the database.

Example:

function GetBusinessData() {
           myDB.transaction(function(transaction) {
           	    transaction.executeSql("SELECT business_id, business_name FROM Business_Table 		                                            WHERE business_id =?", ['1234'],
                function(tx, result) {
                      var dataLength = result.rows.length;
                       console.log(dataLength);
                      if(dataLength  > 0){
                           var businessName = result.rows.item(0).business_name;
		    console.log(businessName);
                       }else{
                             console.log("No business found having this business id.); 
                       }
                  }, 
                  function(error) {
                        console.log("Error occurred while getting the data.);
                  });
          }); 
}

UPDATE: This operation is used to update or modify the entries in the table.

Example:

function UpdateBusinssTable(){
	myDB.transaction(
                    function(transaction) {
                    // Define update query
                    var executeQuery = "UPDATE " +
                                       "Business_Table " +
                                       "SET business_name = ?  WHERE  business_id =?"; 
                    transaction.executeSql(executeQuery, ['Mindfire Solutions', '1234']
                        , function(tx, result) {   // On success
                             console.log('Business updated successfully.');
                        },
                        function(error){     // On error                               
                            console.log('Error occurred while updating the business.'); 
                        });
           });
}

DELETE: This is the last and final method of CRUD operation which is used to delete/drop the entries in table.

Example:

function DeleteBusinessTable(){
            myDB.transaction(
                function(transaction) {
                // Define delete query
                var executeQuery = "DELETE FROM Business_Table";
                transaction.executeSql(executeQuery, []
                    , function(tx, result) {   // On success
                         console.log('All business data deleted successfully.');
                    },
                    function(error){     // On error                               
                         console.log('Error occurred while deleting the business data.'); 
                    });
            });
}

function DropBusinessTable(){
            myDB.transaction(
                function(transaction) {
                // Define delete query
                var executeQuery = "DROP TABLE  IF EXISTS Business_Table";
                transaction.executeSql(executeQuery, []
                    , function(tx, result) {   // On success
                         console.log('Table deleted successfully.');
                    },
                    function(error){     // On error                               
                         console.log('Error occurred while droping the table.'); 
                    });
            });
}

Summary:
I hope the above content will give some idea to start the basic CRUD operation with database and will make the life easy.

Written By: Sourabha Kumar Sahoo, Software Developer, Mindfire Solutions

Advertisements

One thought on “CRUD operation in PhoneGap application using SQLite Database

  1. Pingback: CRUD operation in PhoneGap application using SQLite Database | souravsahoo

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s