How to use sqlite database in phonegap

this article, we are about to explore the SQLIte statements used in Android/iOS using PhoneGap.

Let’s start to create database and database object which will be used entire application.


dbobj = window.sqlitePlugin.openDatabase({name: "databasename"});

iOS and Android both have different syntax for connecting with the database so you can use following code with a condition.


if(device.platform=="Android"){
dbobj = window.sqlitePlugin.openDatabase({name: "databasename"});
}
else{
dbobj = window.openDatabase("databasename", "", "",'');
//dbobj = window.openDatabase("databasename", "4", "Cordova Demo",'');
}

NOTE: If you are using above conditional code, you need to call code into device ready otherwise you will not get device.platform value.

var dbobj;
document.addEventListener("deviceready", onDeviceReady, false);


function onDeviceReady() {
if(device.platform=="Android"){
dbobj = window.sqlitePlugin.openDatabase({name: "databasename"});
}
else{
dbobj = window.openDatabase("databasename", "", "",'');
//dbobj = window.openDatabase("databasename", "4", "Cordova Demo",'');
}
}

Next is to create required tables into the database.Here we will use database object we have created above.


dbobj.transaction(createSchema, errorInSchema, successInSchema);

function createSchema(tx){
tx.executeSql('CREATE TABLE IF NOT EXISTS tablename(nID INTEGER PRIMARY KEY AUTOINCREMENT,sName TEXT)');
}


function errorInSchema(){
alert("Error to create schema");
}


function successInSchema(){
alert("Schema creation successful");
}

Here, In above code transaction used to runs a database transaction and executeSql method executes an SQL statement.


Next, We will see how to insert records in the sqlite database using PhoneGap.


dbobj.transaction(insertRecord, errorDB, successDB);
function insertRecord(tx){
tx.executeSql('INSERT INTO tablename (sName) VALUES("Bhumi Shah")',[],SuccessInsert,errorInsert);
}
function SuccessInsert(tx,result){
alert("Last inserted ID = " + result.insertId);
}
function errorInsert(error){
alert("Error processing SQL: "+error.code);
}

Now, It’s time to check update query to update existing record in the sqlite database using PhoneGap.


dbobj.transaction(updateRecord, errorDB, successDB);
function updateRecord(tx){
tx.executeSql('UPDATE tablename SET sName=’Bhumi Shah’ where nID = 1', [], successUpdate, errorUpdate);
}
function successUpdate(tx,result){
alert("Last updated ID = " + result.insertId);
}
function errorUpdate(error){
alert("Error processing SQL : "+error.code);
}

Here, you can use REPLACE query, if you want to update the record without using SELECT query. REPLACE query is required a primary key value to replace record.


function updateRecord(tx){
tx.executeSql('INSERT OR REPLACE tablename names (nID, sName) VALUES (1, "Bhumi")', [], successUpdate, errorUpdate);
}

Here, INSERT OR REPLACE would insert if the row does not exist or replace the values if it exists.

Well, It’s time to understand most important SQL select statement in the sqlite database using Phonegap syntax.


dbobj.transaction(selectRecords, errorDB, successDB);
function selectRecords(tx){
tx.executeSql('SELECT * FROM tablename', [], successResults,errorInQuery);
}
function successResults(tx,results){
// alert(JSON.stringify(results));
var sData = jquery.ParseJSON(results);
var nLength = results.rows.length;
for(var c=0;c<=len;c++){
// execute or place your desired statement
}
}


We’re done!I hope that you like this article and find many uses for your application.

Advertisements

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