One of my favourite AIR features is the support of creating local database file using SQLite embedded engine. If you have SQL skills it'll be easy to storing persistent local data to your desktop application. You can use this feature to store application data, favourite users' configuration options, document-oriented application, to cache data and synch it with the network.
Creating a SQLite database in AIR is pretty simple:
1. Create a local file with the .db extension using the File class:
// JavaScript
var myDB = air.File.desktopDirectory.resolvePath("db/myDBFile.db");
// ActionScript
var folder:File= File.applicationStorageDirectory.resolvePath( "db" );
folder.createDirectory();
_myDB = folder.resolvePath( "myDBFile.db" );
2. Create an istance of the SQLConection class to open the database file to work with and open the database using the synchronous method open() or the asyncronous method openAsync():
//JavaScript
var dbConn = new air.SQLConnection();
dbConn.openAsync(myDB);
dbConn.addEventListener(air.SQLEvent.OPEN, onOpenHandler);
dbConn.addEventListener(air.SQLErrorEvent.ERROR, onErrorHandler);
// ActionScript
private var _dbConn:SQLConnection(); = new SQLConnection();
_dbConn.openAsync(_myDB);
_dbConn.addEventListener(SQLEvent.OPEN, openHandler);
_dbConn.addEventListener(SQLErrorEvent.ERROR, errorHandler);
If everything is ok the AIR SQLite database is created and opened. Now you're ready to create tables and populate it.
But a cool thing about the open() or the asyncronous method openAsync() is that you can create an in-memory database passing a null value to the first parameter of these methods. It means that the database is created not in a database file on disk but it's a temporary database reference.
The openAsync() method (as well as the opee()) has the following syntax:
public function openAsync(reference:Object = null, openMode:String = "create", responder:Responder = null, autoCompact:Boolean = false, pageSize:int = 1024):void
where the reference parameter is an Object that specifies the location of the database file that is opened. If you set it to null you'll create an in-memory database:
// actionScript
private var _dbConn:SQLConnection(); = new SQLConnection();
_dbConn.openAsync(null);
//JavaScript
var dbConn = new air.SQLConnection();
dbConn.openAsync(null);
So you can now create your SQL statements, define mechanism for executing multiple statements in a transaction, use the begin(), commit(), and rollback() methods without having a local file. At the end of your operations you'll decide if storing the database on the local client or sending it to your remote server !
