How to create an in-memory database in AIR using ActionScript and JavaScript

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 !

Comments

[Martin Valler]

As I was reading that "At the end of your operations you'll decide if storing the database on the local client or sending it to your remote server", I started to look for an example how to store the in-memory database on the filesystem but could not find one. Could you give a hint how to do that? Thank you.