While developing another project I was working with SQLite database and I found it amusingly easy to use. I thought I will share some codes with you guys: 

First we create a database.

I do suggest LITA Air application for SQLite management. It has some very simple interface and it was enough for me.

http://www.dehats.com/drupal/?q=node/58

I would suggest you to save database source file into the application folder. So when you compile app it will include db also. When user install application we need to copy database source file to user storage folder. So next time they update app or re-install it, it will pick up database from storage without losing previously entered data.

Making  database connection

Copy the javascript method below somewhere into your page, call it anytime u need to run a query

var dbFilename = "mydatabase.sqlite"; //change it to the name of your database file
var getDbConnection = function()
{
	//lets cache connection for farther use
	if ( getDbConnection.conn )
		return getDbConnection.conn;
 
	//this is the path to database file in the user storage
	var dbFile = air.File.applicationStorageDirectory.resolvePath(dbFilename);
 
	//if this file do not exists, meaning we are installing app for the first time
	//let copy from source
	if (!dbFile.exists)
	{
		var dbTemplate = air.File.applicationDirectory.resolvePath(dbFilename);
		dbTemplate.copyTo(dbFile, true);
	}
 
	//now lets try to make a connection
	try
	{
		//mcn: cache connection in a "static" variable
		this.conn = new air.SQLConnection();
		this.conn.open(dbFile);
		this.conn.compact(); //lets compact database,
		//I do prefer to compact the database however it it not necessary
 
		return this.conn;
	}
	catch (error)
	{
		this.conn = null;
		//handle error here
		return null;
	}
}

Running a query

To run a query syntax is very simple.

var stmt = new air.SQLStatement();
stmt.sqlConnection = getDbConnection(); //call method created above
stmt.text = "select * from table id = ? ";
stmt.parameters[0] = 10;
stmt.execute();

Happy Airing…

Dima Svirid

Software architect, JAVA, Spring, Hibernate, AngularJs, Backbone, MongoDB, Oracle. CTO and Co-Founder of Homeadnet.com

More Posts

Follow Me:

Tagged with: