Working asynchronously with a local SQL database
Modified
10 June 2010
Requirements
Prerequisite knowledge
General experience of building applications with Flash is suggested. For more details on getting started with this Quick Start, refer to Building the Quick Start sample applications with Flash.
User level
Intermediate
Required products
- Adobe AIR
- Flash Professional CS5 (Download trial)
Sample files
The sample application discussed in this article is intentionally simple. It creates a database in the computer's memory, creates a table in that database, and adds some data to the database. Clicking the Load data button then retrieves the data and displays it on the screen (see Figure 1). Other than loading data when the button is clicked and displaying it on the screen, there is no additional user interaction available. This is intentional, to focus the application entirely on the database operations. This sample application demonstrates the following Adobe AIR features:
- Connecting to a local SQL database using asynchronous execution mode
- Creating and executing SQL statements asynchronously:
- Creating a table in the database
- Inserting data into the database table
- Retrieving data from the database table and displaying that data in a Flash DataGrid component
Note: This is a sample application provided, as is, for instructional purposes.
Understanding the code
Note: This article does not describe all of the components used in the FLA file. For more information, see the ActionScript 3 Reference for the Flash Platform.
Connecting to a local SQL database
The init() method is called when the application finishes loading. Within this method, a SQLConnection instance name conn is created. (The variable conn is declared outside the method so that it is available to all the code in the application.) This SQLConnection object establishes the connection to a database, and is used by other objects to perform operations on that specific database. Once the SQLConnection instance is created, event listeners are registered with it to be called when the database connection is opened (or the openAsync() operation fails), and the openAsync() method is called to open the connection to the database in asynchronous execution mode.
conn = new SQLConnection();
conn.addEventListener(SQLEvent.OPEN, openSuccess);
conn.addEventListener(SQLErrorEvent.ERROR, openFailure);
conn.openAsync(null);
In this case null is passed as an argument to the openAsync() method, indicating that the runtime creates a database in the computer's memory rather than in a disk location. Alternatively, you could specify a file location (using a File instance). The runtime would then open the database file at that location (creating it first if it doesn't exist). The code to do that would look like this:
var dbFile:File = File.applicationStorageDirectory.resolvePath("DBSample.db");
conn.openAsync(dbFile);
File.applicationStorageDirectory points to the AIR application store directory, which is uniquely defined for each AIR application.
Assuming the openAsync() operation succeeds and the database connection opens, the openSuccess() method is called. That method simply performs the clean-up operation of removing the event listener registrations, and calls the createTable() method that does the work of creating a table in the database.
Creating a table in the database
The createTable() method uses a SQLStatement instance to execute a SQL command against the database that was opened in the init() method. The specific SQL command creates a table in the database named employees, with four columns. Here is a breakdown of the code and what it does:
- Creates a SQLStatement instance named
createStmt:
createStmt = new SQLStatement();
- Specifies that the statement will execute on the database that's connected through the SQLConnection instance
conn:
createStmt.sqlConnection = conn;
- Defines the SQL statement text to create a database table. The table is named employees. It has four columns: empId, firstName, lastName, and salary.
var sql:String = "";
sql += "CREATE TABLE IF NOT EXISTS employees (";
sql += " empId INTEGER PRIMARY KEY AUTOINCREMENT,";
sql += " firstName TEXT,";
sql += " lastName TEXT,";
sql += " salary NUMERIC CHECK (salary >= 0) DEFAULT 0";
sql += ")";
createStmt.text = sql;
- Registers event listeners to specify the methods that are called when the statement finishes executing (
createResult) or fails (createError):
createStmt.addEventListener(SQLEvent.RESULT, createResult);
createStmt.addEventListener(SQLErrorEvent.ERROR, createError);
- Executes the statement:
createStmt.execute();
Assuming that the statement runs successfully, the employees table is created and the createResult() method is called. That method removes the registered listeners and calls the addData() method to perform the next step in the process, adding data into the newly created table.
Inserting data into the database table
Like the createTable() method, the addData() method creates a SQLStatement, in this case to insert a row of data into the employees table in the database. The application inserts two rows of data, using two different SQLStatement instances (insertStmt and insertStmt2):
insertStmt = new SQLStatement();
insertStmt.sqlConnection = conn;
var sql:String = "";
sql += "INSERT INTO employees (firstName, lastName, salary) ";
sql += "VALUES ('Bob', 'Smith', 8000)";
insertStmt.text = sql;
insertStmt.addEventListener(SQLEvent.RESULT, insertResult);
insertStmt.addEventListener(SQLErrorEvent.ERROR, insertError);
insertStmt.execute();
insertStmt2 = new SQLStatement();
insertStmt2.sqlConnection = conn;
var sql2:String = "";
sql2 += "INSERT INTO employees (firstName, lastName, salary) ";
sql2 += "VALUES ('John', 'Jones', 8200)";
insertStmt2.text = sql2;
insertStmt2.addEventListener(SQLEvent.RESULT, insertResult);
insertStmt2.addEventListener(SQLErrorEvent.ERROR, insertError);
insertStmt2.execute();
Note that because the second statement execution doesn't depend on the result of the first one, the second SQLStatement instance is created, and its execute() method is called immediately after the first instance's execute() method is called. (As opposed to waiting for the result event of the first INSERT statement before executing the second one.) The runtime queues up these two statements, executing the second one immediately after the first one completes.
The only complicating factor is that the code needs to determine that both statements have completed before it moves on to retrieve data from the database. To do this, in the insertResult() method (which is called when either SQLStatement's result event is triggered) the application determines which statement finished executing, then checks whether both statements have finished executing. If they have, the status bar text (status.text) is updated to read "Ready to load data" and the application is ready to retrieve the data from the database and display it on the screen:
function insertResult(event:SQLEvent):void
{
var stmt:SQLStatement = event.target as SQLStatement;
stmt.removeEventListener(SQLEvent.RESULT, insertResult);
stmt.removeEventListener(SQLErrorEvent.ERROR, insertError);
if (stmt == insertStmt)
{
insert1Complete = true;
}
else
{
insert2Complete = true;
}
if (insert1Complete && insert2Complete)
{
status.text = "Ready to load data";
}
}
Retrieving data from the database table
Like creating a table and inserting data into the table, retrieving data from a table is carried out by creating a SQLStatement instance with a SQL SELECT statement as the SQLStatement instance's text property. The following code, from the getData() method, creates and executes the SELECT statement that retrieves all the rows from the employees table:
selectStmt = new SQLStatement();
selectStmt.sqlConnection = conn;
var sql:String = "SELECT empId, firstName, lastName, salary FROM employees";
selectStmt.text = sql;
selectStmt.addEventListener(SQLEvent.RESULT, selectResult);
selectStmt.addEventListener(SQLErrorEvent.ERROR, selectError);
selectStmt.execute();
As specified in the code, when the SELECT statement finishes executing the selectResult() method is called. In selectResult(), the result data that is retrieved by the SELECT statement is accessed by calling the SQLStatement instance's getResult() method. Calling getResult() returns a SQLResult instance that is stored in the variable result; the actual result rows are contained in an array in its data property. The results are displayed in the Flash DataGrid control named resultsGrid in two steps. First, the code creates a fl.data.DataProvider instance pre-populated with the data from the result.data property. Next, that DataProvider object is set as the resultsGrid data grid's dataProvider property:
function selectResult(event:SQLEvent):void
{
// ... clean up ...
var result:SQLResult = selectStmt.getResult();
resultsGrid.dataProvider = new DataProvider(result.data);
}
