Just as with Adobe AIR on the desktop, you can use an SQLite database to store data on a mobile device. The example below will create a database, use a simple form to save data to that database, and retrieve and display the stored data.
Let’s review the code below. At the top, you will see the database file defined as a file called users.db within the userDirectory. Next, the SQLConnection is defined. Finally, there are several SQLStatements declared and SQL strings defined, which will be used for working with the database.
Within the applicationComplete event handler, the SQLConnection is initiated; two event listeners are added to listen for SQLEvent.OPEN and SQLErrorEvent.ERROR; and finally, the openAsync method is called and the db file is passed in.
After the database is opened, the openHandler function is called. Within this function, the SQLEvent.OPEN event listener is removed. Next, createTableStmt is created, configured, and executed. This statement will create a new table called Users (if it doesn’t yet exist). If it is successful, then the createResult method is called. Within the create Result method, the SQLEvent.RESULT event is removed and the selectUsers method is called.
Within the selectUsers method, selectStmt is created, configured, and executed. This statement will return all rows within the Users table. This data is then stored within the selectStmt. If it is successful, the selectResult method is called. Within the selectResult method, the data is read from the selectStmt using the getResults method. It is then cast to an ArrayCollection and set to the dataProvider of a DataGroup, where it is shown on screen by formatting within an itemRenderer named UserRenderer.
All of the processes just described occur as chained events when the application loads up. So if there is any data in the database from previous uses, it will automatically display when the application is loaded. This can be seen in Figure 5-10.
The only remaining functionality is the ability to add a new user. There are two text fields with the ids of firstName and lastName, and a Button that when clicked will call the button1_clickHandler function. Within the button1_clickHandler function, insertStmt is created, configured, and executed. Notice that within the insertStmt configuration, the parameters firstName and lastName (which were defined in insertSQL) are set to the text properties of the firstName and lastName TextInput components. If it is successful, the insertResult method is called. Within the insert Result method, the selectUsers method is called and the DataGroup is updated, showing the newly-added data. This can be seen in Figure 5-11.
Here is the code for the main application:
<?xml version=”1.0″ encoding=”utf-8″?>
<s:Application xmlns:fx=”http://ns.adobe.com/mxml/2009″
xmlns:s=”library://ns.adobe.com/flex/spark”
applicationComplete=
“application1_applicationCompleteHandler(event)”>
<fx:Script>
<![CDATA[
import mx.collections.ArrayCollection;
import mx.events.FlexEvent;
privatevar db:File = File.userDirectory.resolvePath(“users.db”);
privatevar conn:SQLConnection;
privatevar createTableStmt:SQLStatement;
privatevar createTableSQL:String =
“CREATE TABLE IF NOT EXISTS User (” +
“userId INTEGER PRIMARY KEY AUTOINCREMENT,” +
“firstName TEXT,” + “lastName TEXT)”;
privatevar selectStmt:SQLStatement;
privatevar selectSQL:String = “SELECT * FROM User”;
privatevar insertStmt:SQLStatement;
privatevar insertSQL:String =
“INSERT INTO User (firstName, lastName)” +
“VALUES (:firstName, :lastName)”;
protectedfunction application1_applicationCompleteHandler
(event:FlexEvent):void
{
conn = new SQLConnection();
conn.addEventListener(SQLEvent.OPEN, openHandler);
conn.addEventListener(SQLErrorEvent.ERROR, errorHandler);
conn.openAsync(db);
}
privatefunction openHandler(event:SQLEvent):void {
log.text += “Database opened successfully”;
conn.removeEventListener(SQLEvent.OPEN, openHandler);
createTableStmt = new SQLStatement();
createTableStmt.sqlConnection = conn;
createTableStmt.text = createTableSQL;
createTableStmt.addEventListener(SQLEvent.RESULT, createResult);
createTableStmt.addEventListener(SQLErrorEvent.ERROR,
errorHandler);
createTableStmt.execute();
}
privatefunction createResult(event:SQLEvent):void {
log.text += “nTable created”;
conn.removeEventListener(SQLEvent.RESULT, createResult);
selectUsers();
}
privatefunction errorHandler(event:SQLErrorEvent):void {
log.text += “nError message: ” + event.error.message;
log.text += “nDetails: ” + event.error.details;
}
privatefunction selectUsers():void{
selectStmt = new SQLStatement();
selectStmt.sqlConnection = conn;
selectStmt.text = selectSQL;
selectStmt.addEventListener(SQLEvent.RESULT, selectResult);
selectStmt.addEventListener(SQLErrorEvent.ERROR, errorHandler);
selectStmt.execute();
}
privatefunction selectResult(event:SQLEvent):void {
log.text += “nSelect completed”;
var result:SQLResult = selectStmt.getResult();
users.dataProvider = new ArrayCollection(result.data);
}
protectedfunction button1_clickHandler(event:MouseEvent):void
{
insertStmt = new SQLStatement();
insertStmt.sqlConnection = conn;
insertStmt.text = insertSQL;
insertStmt.parameters[“:firstName”] = firstName.text;
insertStmt.parameters[“:lastName”] = lastName.text;
insertStmt.addEventListener(SQLEvent.RESULT, insertResult);
insertStmt.addEventListener(SQLErrorEvent.ERROR, errorHandler);
insertStmt.execute();
}
privatefunction insertResult(event:SQLEvent):void {
log.text += “nInsert completed”;
selectUsers();
}
]]>
</fx:Script>
<fx:Declarations>
<!– Place non-visual elements (e.g., services, value objects) here –>
</fx:Declarations>
<s:Label text=”First name” top=”35″ left=”10″/>
<s:TextInput id=”firstName” left=”150″ top=”10″ width=”300″/>
<s:Label text=”Last name” top=”95″ left=”10″/>
<s:TextInput id=”lastName” left=”150″ top=”70″ width=”300″/>
<s:Button label=”Save” click=”button1_clickHandler(event)” top=”130″ left=”150″/>
<s:Scroller height=”200″ width=”100%” left=”10″ right=”10″ top=”200″>
<s:DataGroup id=”users” height=”100%” width=”95%”
itemRenderer=”UserRenderer”>
<s:layout>
<s:VerticalLayout/>
</s:layout>
</s:DataGroup>
</s:Scroller>
<s:TextArea id=”log” width=”100%” bottom=”0″ height=”250″/>
</s:Application>
The code for the UserRenderer:
<?xml version=”1.0″ encoding=”utf-8″?>
<s:ItemRenderer xmlns:fx=”http://ns.adobe.com/mxml/2009″
xmlns:s=”library://ns.adobe.com/flex/spark”>
<s:Label text=”{data.lastName}, {data.firstName}”/>
</s:ItemRenderer>
