Use of Web SQL in Phonegap

Posted By : Avilash Choudhary | 01-Sep-2014



In Phonegap, you can use Web SQL to store data on local device. you can perform most of sql queries. To start with database, t
he first method is openDatabase that will take the four arguments. first is database name then version number,text description and estimated size of database. other method are transaction and executeSql.

Transaction method is used so that you can rollback your queries. With the help of transaction object you can call executeSql and perform any insert, update and delete queries.
 

This function will return the instance of database to perform sql queries.

 db.openDB = function() {
	var lyfelineDB = window.openDatabase("lyfeline", "1.0", "lyfeline", 200000);
	lyfelineDB.transaction(function(transaction) {
		transaction.executeSql("CREATE TABLE IF NOT EXISTS POSTQUESTIONDATA (ID INTEGER PRIMARY KEY,DATA TEXT)");
	});
	return lyfelineDB;
};
 

To perform insert query you need to write code like this:

 db.insertPostQuestionData = function(item) {
	var lyfelineDB = db.openDB();
	lyfelineDB.transaction(function(transaction) {
		transaction.executeSql('INSERT INTO POSTQUESTIONDATA(ID,DATA) VALUES(?,?)', [JSON.stringify(item.id), JSON.stringify(item)], function(transaction, results) {
			// success
		}, function(e) {
			console.log("some error inserting data");
		});
	});
};

 
 

In executeSql function, first you need to pass sql query then an array with value or blank array then success callback and failure call back.The success callback receives the transanction and result objects. The results object contains rows object which has length. To access the data you need to use results.rows.item(i) where i represents the index of row.

The Sql query to fetch data from database:

db.getQuestionInboxData = function() {
	var lyfelineDB = db.openDB();
	lyfelineDB.transaction(function(transaction) {
		transaction.executeSql('SELECT ID,DATA FROM POSTQUESTIONDATA', [], function(transaction, results) {
	var item = JSON.parse(results.rows.item(i).DATA);			
		}, function(e) {
			console.log("some error getting questions");
		});
	});
};

 
you can use the where clause to fetch particular data from database such as:
transaction.executeSql('SELECT DATA FROM POSTQUESTIONDATA WHERE ID=?', [postId], function(transaction, results) {

},function(e){

});
 

 

About Author

Author Image
Avilash Choudhary

Avilash has excellent experience in developing mobile and web applications using jQuery , Javascript and PhoneGap. His hobbies are watching and playing cricket.

Request for Proposal

Name is required

Comment is required

Sending message..