Maintaining data with the SQLite database

Adobe AIR gives you a number of options to store application data locally. One possibility is the file system. An XML object could be used to contain structured data and the application could read and write that data to the file system as the information is required.

A better solution is to use the embedded SQLite database engine; it was included with Adobe AIR beta 1 and offers relational database creation and management via SQL. There's less new application logic needed in interacting with this data because you can adhere to database standards.

Check the documentation for examples of some of the SQL commands that can be used with SQLite. You'll find samples of code to add, fetch, modify, and delete records as well as create new tables to store the data in the database. If you're already familiar with SQL, you'll find that there's little variation with what you know for the basic operations. I'd suggest that you also consider searching the web for specific examples of SQLite syntax as required.

When you're using SQLite, it's important to get the timing of your events in the right order. First you need to create a connection to the database before you can start sending it queries, otherwise you'll get errors in your application caused by timing issues. The best approach is to create the connection within a function called when the application initializes.

Classes need to be imported for the SQL connection, the SQL statement, the result and possible errors (if you want to handle both), and the file system so that the application can access the flat file that the data is stored in. You can open the SQLite file with a text editor if you're curious. The raw text, however, doesn't make a lot of sense. It would be more productive to write a function to dump the results of a query and use that instead.

Look at the example below of running a SQL SELECT call to fetch a specific record and you'll see that there's a bit of work involved. After the imports I've created variables for the database, for the connection, and for storing the SQL statement that I'll be sending to fetch the data.

Next, is a function to initialize the connection, then a second function to run the query. Note how the sqlStatement object is pointed at the connection, and the SQL is then added to the statement, with the last part of the function executing the query. I need to add code to this process to indicate how the query's result is to be handled. These are the two addEventListeners contained within the function.

import flash.data.SQLConnection;
import flash.data.SQLStatement;
import flash.events.SQLErrorEvent;
import flash.events.SQLEvent;
import flash.filesystem.File;
private var dbFile:File =
    File.applicationStorageDirectory.resolvePath("FfData.db");
private var conn:SQLConnection = new SQLConnection();
private var sqlStatement:SQLStatement = new SQLStatement();
private function init():void {
 conn.open(dbFile,true);
}
private function fetchAllPhotos():void {
 sqlStatement.sqlConnection = conn;
 var sql:String =    "SELECT * FROM photos WHERE fileName
  = 'myPhoto.jpg'"
 sqlStatement.text = sql;
 sqlStatement.addEventListener(SQLEvent.RESULT, selectResult);
 sqlStatement.addEventListener(SQLErrorEvent.ERROR,
  selectError);
 sqlStatement.execute();
}
private function selectResult(event:SQLEvent):void {
 // result handler code
}
private function selectError (event:SQLErrorEvent):void {
 // error handler code
}

Although event listeners are used for handling the results and faults returned by the queries, you can also consider using a responder to do the work. Ensure that you've correctly typed the data received by your listeners. Note the two different types being used in the code above.

I personally find this whole procedure rather top heavy, especially the amount of code required within a function to make a query happen. This application is going to need a few different queries as part of the various processes required for its functionality. If I were to split up the visual elements into various views, I would also need a methodology to help data flow between the different entities.

Some kind of helper file that did the job of looking after the various queries and their results could make life a lot easier for us right at this point, and you'd be right in thinking that the application of a framework or design pattern would probably make a big difference.

After a bit of thinking and some investigation I found a very handy DataAccess utility class written by Brandon Ellis specifically for using SQLite in Adobe AIR applications that has proved to be a time saver. It acts as a data access layer, doing the majority of the work for you—your code utilizes it by passing it the SQL.

The class responds by returning an instance of the ArrayCollection class, which is fine if you're using components but not so good if you're doing most of your work in code. I've modified the class to return an event so that it can be more easily consumed by ActionScript.

The code below is the same process as in the previous sample with the exception that I used the DataAccess class. Note the way it's initialized with a reference to the database file with an internal relative URL path. The connection has to be opened with a generic query, calling one of the tables on the database. You may need to remove the event listener once the database response is received. You'll find an example of this in the application.

import com.flickrfloater.events.SqlDbEvent;
import org.brandonellis.DataAccess;
 [Bindable] private var da:DataAccess;
private function init():void {
 da = new DataAccess("app-storage:/FfData.db");
 da.addEventListener(SqlDbEvent.SQL_RESPONSE, selectResult);
 da.openConnection("SELECT * FROM photos WHERE fileName =
  'myPhoto.jpg'");
}
private function dbSelect():void {
 var sql:String = "Select * from photos WHERE photoId =
  2";
 da.DataAccessSelect(sql);
}
private function selectResult(e:Event):void {
 // result handler code
}

Page 5 of 9

Previous Page

Next Page