One of the best aspects of writing applications deployed on Adobe AIR is having a reliable database engine. AIR apps can create tables, insert data, query data, etc. You can choose to interact Uses for this include local caching of data, offline usage and many others.
You can interact with the database in a synchronous or asynchronous execution mode (see this article for pros and cons).
The sample below creates a simple form that allows the user to enter new records that are saved in the database.
NOTE: AIR 1.5 now supports encrypted databases. More information about encrypted databases is available here.
This sample and many more are available with source code in Tour de Flex. An install badge is below the code sample.
<?xml version="1.0" encoding="utf-8"?>
<mx:WindowedApplication xmlns:mx="http://www.adobe.com/2006/mxml" layout="absolute" creationComplete="initDatabase()"
width="100%" height="100%" backgroundColor="#323232" verticalAlign="middle" horizontalAlign="center">
<mx:Script>
<![CDATA[
import flash.data.SQLResult;
import flash.filesystem.File;
import flash.data.SQLStatement;
import flash.data.SQLConnection;
import flash.events.SQLEvent;
import flash.events.SQLErrorEvent;
import mx.collections.ArrayCollection;
private var employeeDB:SQLConnection;
private var dbStatement:SQLStatement;
[Bindable]
private var employeeCollection:Array = new Array();
private function initDatabase():void
{
var dbFile:File = File.applicationStorageDirectory.resolvePath("employees.db");
dbStatement = new SQLStatement();
dbStatement.itemClass = Employee;
employeeDB = new SQLConnection();
dbStatement.sqlConnection = employeeDB;
employeeDB.addEventListener(SQLEvent.OPEN, onDatabaseOpen);
employeeDB.addEventListener(SQLErrorEvent.ERROR, errorHandler);
employeeDB.open(dbFile);
}
private function onDatabaseOpen(event:SQLEvent):void
{
dbStatement.text = "CREATE TABLE IF NOT EXISTS employees ( id INTEGER PRIMARY KEY AUTOINCREMENT, firstname TEXT, lastname TEXT, position TEXT )";
dbStatement.addEventListener(SQLEvent.RESULT, handleResult);
dbStatement.addEventListener(SQLErrorEvent.ERROR, createError);
dbStatement.execute();
// refresh employee list on open
getEmployees();
}
private function handleResult(event:SQLEvent):void
{
var result:SQLResult = dbStatement.getResult();
if (result != null) {
this.employeeCollection = result.data;
}
}
private function getEmployees():void
{
dbStatement.text = "SELECT * from employees";
dbStatement.execute();
}
private function onAddBtnClick(event:MouseEvent):void
{
var employee:Employee = new Employee();
employee.firstname = txtFirstName.text;
employee.lastname = txtLastName.text;
employee.position = txtPosition.text;
addEmployee(employee);
getEmployees();
}
private function addEmployee(employee:Employee):void
{
dbStatement.text = "INSERT into employees(firstname,lastname,position) values('" +
employee.firstname +
"','" +
employee.lastname +
"','" +
employee.position +
"')";
dbStatement.execute();
}
private function onDeleteBtnClick(event:MouseEvent):void
{
var employee:Employee = emplDataGrid.selectedItem as Employee;
var sqlDelete:String = "delete from employees where id='"+employee.id + "' and firstname='"+ employee.firstname+"' and lastname='"+
employee.lastname+"' and position='"+employee.position+"';";
dbStatement.text = sqlDelete;
dbStatement.execute();
getEmployees();
}
private function errorHandler(error:SQLError):void
{
trace("Error Occurred with id: " + error.errorID + " operation " + error.operation + " message " + error.message);
}
private function createError(event:SQLErrorEvent):void
{
trace("Error Occurred with id: " + event.error.errorID + " message " + event.error.message);
}
]]>
</mx:Script>
<mx:VBox left="10" right="10" top="5" width="100%">
<mx:DataGrid id="emplDataGrid" dataProvider="{employeeCollection}" width="100%" rowCount="4">
<mx:columns>
<mx:DataGridColumn dataField="id" headerText="Employee ID"/>
<mx:DataGridColumn dataField="firstname" headerText="First Name"/>
<mx:DataGridColumn dataField="lastname" headerText="Last Name"/>
<mx:DataGridColumn dataField="position" headerText="Position"/>
</mx:columns>
</mx:DataGrid>
<mx:Button id="deleteBtn" left="10" label="Delete Employee" click="onDeleteBtnClick(event)" enabled="{emplDataGrid.selectedIndex > -1}"/>
</mx:VBox>
<mx:Form top="110" left="180">
<mx:FormItem label="First Name:" color="white">
<mx:TextInput id="txtFirstName" color="black"/>
</mx:FormItem>
<mx:FormItem label="Last Name:" color="white">
<mx:TextInput id="txtLastName" color="black"/>
</mx:FormItem>
<mx:FormItem label="Position:" color="white">
<mx:TextInput id="txtPosition" color="black"/>
</mx:FormItem>
</mx:Form>
<mx:ControlBar top="200" left="270">
<mx:Button id="addBtn" label="Add Employee" click="onAddBtnClick(event)"/>
</mx:ControlBar>
</mx:WindowedApplication>