Accessing Local Database SQLite with AIR API

Probably I went in wrong order, it seems that I picked the most complex one to start this topic. "User Experience Considerations with SQLite Operations", the title implies that the author will emphasis on the two different execution mode: synchronous and asynchronous mode, and talk about how these two modes impact user experience. But maybe because my CPU is too powerful, and the data volume is too small, I couldn't figure out the difference from three modes by testing author's sample program.   

"User Experience Considerations with SQLite Operations"

Inside the 'SQLite_operations folder', there is sample program called 'SQLiteArticle'. 

Eh..... Flash Builder 4 is much more primary than 4.5, I opened the project in 4.5 and got an error in 'Problems' Tab, says "Namespace 1.0.0 in the application descriptor file should be equal or higher than the minimum version 1.5.3 required by Flex SDK." Whereas in 4.0, I failed to build the project just without prompting that. And in 4.0, the XML file is just shown as plain text, not formatted, which is really inconvenient.

The real problem is that the version specified in 'SQLiteArticle-app.xml' should be higher than '1.5.3', but currently is 1.0, because the user published this blog in 2008 Mar, so dated. Change '1.0' to '1.5.3', and I can build and run the program.

(this snapshot is taken in FB4.5, but the comings will be in FB4.0)

The database file, (please note that the folder 'Application Data' is hidden by default):

For convenience, let's find a admin tool for SQLite, download the SQLiteSpy from: http://www.xdowns.com/soft/softdown.asp?softid=50794

According to the Debug Output, open the db file in SQLiteSpy: "C:\Documents and Settings\OOi\Application Data\SQLiteArticle\Local Store\data.db".

"Working asynchronously with a local database"

But since I am building my project with Flash CS5.5 this time, I only need to get it work in Flash IDE instead of Flex. So let's exam the attached sample program in this article - "SimpleDBExampleFlash". 

There are some keys to emphasis before going on:

****************************************************************************************************************************** The classes for accessing local SQL database, such as: flash.data.SQLResult flash.data.SQLConnection flash.data.SQLStatement they are only avaliable for AIR program. And so is the file handling class: flash.filesystem.File So the .fla must be specified for AIR, rather than Flash Player. Otherwise you will get compiling error. ******************************************************************************************************************************

****************************************************************************************************************************** All the sample programs make use of Flash build-in component: datagrid. And operating on its property: dataProvider. But the class difinadtion for dataProvider: "fl.data.DataProvider" its library path will not be include into the project's class path, unless you drag a component into your stage. Even you can add the class path to your project(but in CS5, I can't find where is the class file located), it was said that it would not help. ******************************************************************************************************************************

****************************************************************************************************************************** The next key point is, if you use "File.applicationStorageDirectory", to handle file, every AIR program will create its own folder (the folder's name is just the same as program's name) under '.../Application Data/'. And   "File.applicationStorageDirectory" will reference to thay directory. I made a mistake at this point, I created a AIR program mySqlTest, and I just use it to access "data.db" created by the above Flex program "SQLiteArticle", (actually the  file is located at "..../Application Data/SQLiteArticle/"), by using  "File.applicationStorageDirectory", but the program will try to open the file from a different location(its own one), and find nothing, then create a brand new 'data.db' with nothing, no wonder I always got an Error: no such table 'product'... ******************************************************************************************************************************

So, I don't wanna to bother with how to import DataProvider class, I won't use DataGrid, I will just trace out the data in table in console. And after I firstly run the program, at this point its own folder should be in placve, I copied 'data.db' to its own folder, and ran again, then I shouldn't get error any more, I should work. 

My own code:

import flash.data.SQLResult;
import flash.filesystem.File;
import flash.data.SQLConnection;
import flash.events.SQLEvent;
import flash.events.SQLErrorEvent;
import flash.data.SQLStatement;
import flash.data.SQLMode;


var conn:SQLConnection = new SQLConnection();
	conn.addEventListener(SQLEvent.OPEN, onConnOpened);
	conn.addEventListener(SQLErrorEvent.ERROR, onConnOpenError);

var dbFile:File = File.applicationStorageDirectory.resolvePath("data.db");
	// open the connection in asynchronous execution mode
	conn.openAsync(dbFile);


var selectStmt:SQLStatement = new SQLStatement();
var	sqlText:String = "SELECT * FROM sku LIMIT 16";

var products:SQLResult;	

function onConnOpened($e:SQLEvent):void
{
	trace("open conn successfully!");
	selectStmt.sqlConnection = conn;
	selectStmt.text = sqlText;
	selectStmt.addEventListener(SQLEvent.RESULT, onSelectExecuted);
	selectStmt.addEventListener(SQLErrorEvent.ERROR, onSelectError);
	selectStmt.execute();
}

function onConnOpenError($e:SQLErrorEvent):void
{
	trace("failed to open!");
	trace($e.error.operation);
	trace($e.error.message);
	trace($e.error.details);
}

function onSelectExecuted($e:SQLEvent):void
{
	// get the result of query
	products = selectStmt.getResult();
	// data property is an numeric array
	var numRows:int = products.data.length;
	trace(numRows);
	// each row will be parsed into an object with the data cell as properties
	// or an associative array
	trace(products.data[12]["price"]);
}


function onSelectError($e:SQLErrorEvent):void
{
	trace("failed to execute query!");
	trace($e.error.operation);
	trace($e.error.message);
	trace($e.error.details);	
}

Of course, there is an easy way to iterate all the data cells within a row, just treat the object as an associative array:

var numRows:int = products.data.length;
for (var i:int = 0; i < numRows; i++)
{
	var output:String = "";
	for (var prop:String in products.data[i])
	{
		output += prop + ": " + products.data[i][prop] + "; ";
	}
	trace("row[" + i.toString() + "]\t", output);
}

REFS:

User experience considerations with SQLite operations

Using the SQLite database access API in Adobe AIR

New AIR SQLite Administration App (with Source Code)

Working asynchronously with a local SQL database

http://seantheflexguy.com/blog/2007/06/14/super-simple-sqlite-example-for-adobe-air-1-beta/

http://www.leonardofranca.com/index.php/2010/07/23/using-the-local-database-with-adobe-air/

http://tv.adobe.com/watch/adc-presents/using-the-local-database-functionality-in-adobe-air/

http://www.sqlite.org/features.html

http://qops.blogspot.com/2007/06/air-local-database_23.html

http://blog.ben.idv.tw/2008/03/air-sqlite.html