In previous article we have covered the To setup SQLite for Android-Phonegap application. In 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.
1 2 3 | 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.
1 2 3 4 5 6 7 8 9 | if(device.platform=="Android"){ dbobj = window.sqlitePlugin.openDatabase({name: "databasename"}); } else{ dbobj = window.openDatabase("databasename", "<version>", "<display_name>",'<size>'); //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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | var dbobj; document.addEventListener("deviceready", onDeviceReady, false); if(device.platform=="Android"){ dbobj = window.sqlitePlugin.openDatabase({name: "databasename"}); } else{ dbobj = window.openDatabase("databasename", "<version>", "<display_name>",'<size>'); //dbobj = window.openDatabase("databasename", "4", "Cordova Demo",''); } } |
Read about: Disable zoom-in/out effect in Android/iOS
Next is to create required tables into the database.Here we will use database object we have created above.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | function createSchema(tx) { tx.executeSql('CREATE TABLE IF NOT EXISTS tablename(nID INTEGER PRIMARY KEY AUTOINCREMENT,sName TEXT)'); } function errorInSchema() { } function successInSchema() { alert("Schema creation successful"); } |
Here, In above code transaction used to runs a database transaction and executeSql method executes an SQL statement.
Read:Ipad Orientation Detection
Next, We will see how to insert records in the sqlite database using PhoneGap.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | dbobj.transaction(insertRecord, errorDB, successDB); function insertRecord(tx) { tx.executeSql('INSERT INTO tablename (<em>sName</em>) 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | 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.
1 2 3 4 5 | 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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | dbobj.transaction(selectRecords, errorDB, successDB); function selectRecords(tx) { tx.executeSql('SELECT * FROM tablename', [], successResults,errorInQuery); } function successResults(tx,results){ // alert(JSON.stringify(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.
As always, thanks for reading. Don’t Forget to Follow us on Twitter or Subscribe us to Get the Latest Updates.
Comments (22)