A simple example of using SQLite as an embedded database for Adobe AIR applications.

There are two ways to make calls to your SQLite Database:

Synchronous – means that your application will make an “inline” call to SQLite where it performs the operation and then moves on as if it were any other line of actionscript code.

Asychnronous – means that your code will have an event listener on the SQLConnection and an event handler for the response.

Synchronous Example:

package
{
	import flash.display.Sprite;
	import flash.data.*;
	import flash.filesystem.File;
	import flash.events.SQLEvent;
	import flash.events.SQLErrorEvent;

	public class SQLiteExample extends Sprite
	{
		private var _sqlConnection:SQLConnection = new SQLConnection();
		private var _sqlStatement:SQLStatement = new SQLStatement();
		private var _dbDirectory:File;

		public function SQLiteExample()
		{
			/////////OPEN CONNECTION
			openDatabase("test");

			/////////CREATE TABLE
			query("CREATE TABLE IF NOT EXISTS users(username TEXT unique, password TEXT)");

			/////////INSERT VALUES INTO TABLE
			query("INSERT INTO users(username,password) values('mark','swordfish')");
			query("INSERT INTO users(username,password) values('bob','bobiscool')");
			query("INSERT INTO users(username,password) values('sam','samiscool')");

			/////////UPDATE ROW
			query("UPDATE users SET password='notswordfish' WHERE username='mark'");

			/////////SELECT VALUES FROM TABLE
			var queryResult:SQLResult = query("SELECT * FROM users WHERE username='mark'");
			trace(queryResult.data[0].password);
		}

		private function openDatabase(datebaseName:String):void
		{
			_dbDirectory = File.desktopDirectory.resolvePath(datebaseName + ".db");
			_sqlConnection.open(_dbDirectory);
		}

		private function query(sql:String):SQLResult
		{	_sqlStatement.sqlConnection = _sqlConnection;
			_sqlStatement.text = sql;
			_sqlStatement.execute();
			var sqlresult:SQLResult = _sqlStatement.getResult();
			if(sqlresult.data != null)
			{
				return sqlresult;
			}else{
				return null;
			}
		}
	}
}

Asychnronous Example

package
{
import flash.display.Sprite;
import flash.data.*;
import flash.filesystem.File;
import flash.events.SQLEvent;
import flash.events.SQLErrorEvent;

public class SQLiteExample extends Sprite
{
private var _sqlConnection:SQLConnection = new SQLConnection();
private var _sqlStatement:SQLStatement = new SQLStatement();
private var _dbDirectory:File;

public function SQLiteExample()
{
/////////OPEN CONNECTION
openDatabase("test");

/////////CREATE TABLE
query("CREATE TABLE IF NOT EXISTS users(username TEXT unique, password TEXT)");

/////////INSERT VALUES INTO TABLE
query("INSERT INTO users(username,password) values(’mark’,’swordfish’)");
query("INSERT INTO users(username,password) values(’bob’,'bobiscool’)");
query("INSERT INTO users(username,password) values(’sam’,’samiscool’)");

/////////UPDATE ROW
query("UPDATE users SET password=’notswordfish’ WHERE username=’mark’");

/////////SELECT VALUES FROM TABLE
query("SELECT * FROM users WHERE username=’mark’");
}

private function openDatabase(datebaseName:String):void
{
_dbDirectory = File.desktopDirectory.resolvePath(datebaseName + ".db");
_sqlConnection.open(_dbDirectory);
}

private function query(sqlQuery:String):void
{
_sqlStatement.sqlConnection = _sqlConnection;
_sqlStatement.text = sqlQuery;
_sqlStatement.addEventListener(SQLEvent.RESULT, sqlResultHandler);
_sqlStatement.addEventListener(SQLErrorEvent.ERROR, errorHandler);
_sqlStatement.execute();
}

private function sqlResultHandler(e:SQLEvent):void
{
var result:SQLResult = _sqlStatement.getResult();
if(result.data != null)
{
for(var i:int = 0; i < result.data.length; i++)
{
trace(result.data[i].username + " : " + result.data[i].password);
}
}
}

private function errorHandler(event:SQLErrorEvent):void
{
trace("Error occured while executing the statement.");
}
}
}