Include existing SQLite database in an AIR application

 A lots of desktop application use database to storage data locally; in some AIR application we need to embed an existing SQLite database and work with it.

You know that an air file is a package with some files inside and when you install an AIR application in your computer, you copy those files in Application folder or in its subfolder .
So if you want to include others files in an AIR application, when you create it, you must package also other files like images, text file or database file.

In fact you may include an existing SQLite database (created for another application or with another program) and work in your AIR application with it.
But when you install this application the Application folder (File.applicationDirectory), for security reason, is read-only and if you try to work with database you fall in a silent error (take a look at File.applicationDirectory post in AIR cookbook), so you must copy database file, with method copyTo of File Class, in another folder like documents folder or desktop folder.

To do this you can use File class, for example you'd like to copy software.db to desktop folder:

var dbFile:File = File.applicationDirectory.resolvePath("db/software.db");

var dbWorkedFile:File = File.documentsDirectory.resolvePath("software.db");

if(!dbWorkedFile.exists){

// you can also overwrite db file setting the default boolean params in copyTo method

dbFile.copyTo(dbWorkedFile);

}

When you copy this file into another directory you can finally work with your database, so you can create new records, update or delete them.

So you can open a connection to database with SQLConnection class and then work with SQLStatement to execute SQL instruction in AIR.

This is a sample how to work with SQL API with AIR and Actionscript 3:

//create SQL connection

dbConn = new SQLConnection();

dbConn.addEventListener(SQLErrorEvent.ERROR, errorAlert);

dbConn.addEventListener(SQLEvent.OPEN, popolateDG)

dbConn.openAsync(dbWorkedFile);

//create error SQL handler

private function errorAlert(e:SQLErrorEvent):void{

Alert.show(e.error.errorID + "\n\n" + e.error.message + "\n" + e.error.details);

}

//launch SQLStatement to popolate a datagrid component with data in database

private function popolateDG(e:SQLEvent):void{

var sqlStat:SQLStatement = new SQLStatement();

sqlStat.sqlConnection = dbConn;

sqlStat.addEventListener(SQLErrorEvent.ERROR, errorAlert)

sqlStat.addEventListener(SQLEvent.RESULT, showData);

sqlStat.text = "SELECT * FROM Adobe_Software";

sqlStat.execute();

}

private function showData(e:SQLEvent):void{

var sqlRes:SQLResult = e.currentTarget.getResult();

dataDG.dataProvider = sqlRes.data;

}

In attachment you can find full Flex and HTML/Javascript sample where you copy a database from application folder to another folder and you start to work with it.

I suggest also to download an open source software that you can find in sourceforge site that allow you to view database schema, add, delete or update records and execute Sql Statement; it called SQLite database browser and you can download it at own sourceforge page.