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:

flash.data.sqlResult
flash.data.sqlConnection
flash.data.sqlStatement

flash.filesystem.File

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)

@L_502_4@

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