Sqlite in Cordova Using Cordova Plugin

Basic Description:

Sqlite is a Light Weight Database. Sqlite Mostly used in mobile applications. The HTML5/WEBSQL API imposes an upper limit of 5MB for client-side databases which is sufficent for some apps and it is according to current standard but for now in these days this is not sufficient according to some apps those are having very large amount of database. for those apps we use Sqlite Cordova plugin. Cordova/PhoneGap Plugin offers some major advantages like built-in Web SQL library API which supports for large database sizes and provides us excellent reliability.

Installation

Using Terminal go in your project directory, from inside your Cordova project you have to enter the following command.

 cordova plugin add https://github.com/brodysoft/Cordova-SQLitePlugin
 

Below is the method to use this plugin:

To create Database in sqlite using Cordova plugin.

 var db = window.sqlitePlugin.openDatabase({name: "sqlitedemo"});

To Insert values into Database Table in sqlite using Cordova plugin.

 function insert() {
var fname = document.getElementById("firstname").value;
var lname = document.getElementById("lastname").value;
db.transaction(function(tx) {
tx.executeSql('CREATE TABLE IF NOT EXISTS NAME (id integer primary key, firstname text, lastname text)');
tx.executeSql('INSERT INTO NAME (firstname,lastname) VALUES (?,?)', [fname, lname]);
});
}

In this first of all we get the data from text boxes and then by the above given method we saved the data in database table named as “name” into column name “first name” and “last name”.

To Fetch values into Database Table in sqlite using Cordova plugin.

 function fetchdata() {
db.transaction(function(tx) {
tx.executeSql('SELECT * FROM NAME', [], function(tx, res) {
var len = res.rows.length;
for (var i = 0; i < len; i++) {
alert(res.rows.item(i).firstname);
alert(res.rows.item(i).lastname);
}
}, function(e) {
console.log("some error getting");
});
});
}
 

In this method we fetch data from the database by using select query and using res.rows.item() we are displaying the database values in alert boxes.

To Drop Table into Database in sqlite using Cordova plugin.

function deletetable()
{
	var db = window.sqlitePlugin.openDatabase({name: "sqlitedemo"});
	db.transaction(function(tx){
		tx.executeSql('DROP TABLE IF EXISTS NAME');
	});
}

To Update values into Database Table in sqlite using Cordova plugin.

 function updatetable()
{
	var db = window.sqlitePlugin.openDatabase({name: "sqlitedemo"});
	db.transaction(function(tx) {
		tx.executeSql("UPDATE NAME SET firstname='Karan' WHERE lastname='Bhardwaj'",[],function(tx,res){alert("query executed")},function(e){
			console.log("some error getting");
		});
	});
}