AIR's Database Functions
Several classes implement database-interfacing functionality in AIR, and they offer a surprisingly rich set of functionsthe current limitations are due to SQLite, not AIR. The database-related classes (such as SQLConnection, SQLStatement, SQLResult, SQLError) provide an API that programmers with ADO.NET or JDBC knowledge will find both familiar and refreshingly simple.
A SQLite database consists of only one file, but an application can open multiple databases and they can even share the same SQLConnection object (in which case, you can have a cross-database transaction). SQL statements can be prepared and can use bound parameters. Database operations are asynchronous and response is provided by either events (AIR implements error/response/update events, which are defined for SQLConnection or for SQLStatement) or callback functions.
This application is very simple: once the database is opened, it allows the user to type a command in the window and submit it to the SQL engine by clicking the [Send] button. Of course, the first time you execute the application, the database won't contain any objects so you will need to issue a couple of CREATE commands to build your database structure. The core of the database functionality is implemented by the class dbClass.
The main loop of the application adds handlers to events raised by the SQLCommand and SQLStatement objects. The following lines set up this loop with callback functions that handle the opening of a connection, a connection error, the execution of a statement, and an error encountered during statement execution:
_conn.addEventListener( air.SQLEvent.OPEN, openHandler );
_conn.addEventListener( air.SQLErrorEvent.ERROR, errorHandler );
_sqlStmt.addEventListener( air.SQLEvent.RESULT, resultHandler );
_sqlStmt.addEventListener( air.SQLErrorEvent.ERROR, errorHandler );
With SQLStatement, an alternative is to use a Responder object in the execute call (which actually submits the SQL statement to the database engine). Upon execution, this object's listener functions will be invoked.
After the user types a SQL command in the text box and clicks the [Send] button, the btnSendQuery function (which is the handler for the click event of the button) defers the submission of the statement to the exec_sql function of dbClass, which is a wrapper for the execute method of SQLStatement. (See Sidebar 2. Beta Bug AlertOr Maybe It's Just Me to learn more about a possible hiccup when a SQL command cannot be executed.)
SQLite supports prepared SQL statements and parameterized queries. The AIR documentation states that "if the statement is relatively complex, preparing the statement in advance of executing it, such as during application start up or idle time, can improve performance. Once a statement is prepared it does not need to be prepared again unless the text property changes. Setting one or more parameter values does not require the statement to be prepared again." Parameters are a means of avoiding SQL injection attacks when a statement is built dynamically and are implemented using the parameters property (array) of the SQLStatement class. (See Sidebar 3. O/R Capability to learn about an even more interesting SQLStatement feature.)
If the statement succeeds, the RESULT event listener (in this application's code, the resultHandler function) is called. The target property of resultHandler's event parameter is the SQLStatement object that returned the result. The getResult method of SQLStatement provides access to the result set. It returns either a SQLResult object, which is essentially an array containing the entire result set, or a fire hose (forward-only) cursor. The latter occurs if the execute statement was called with a positive prefetch parameter, which allows the database engine to improve large result set processing performance by returning the results in pages instead of in one large block.
var ptrWin = new runtime.flash.display.NativeWindow( true, win_options );
At this point, adding widgets to the native window is conceptually similar to the DOM insertion that creates the button and the text field on the first HTML document (in the appLoad function). Placing visual objects on the Stage object of the Flash runtime is called display list programming. The application instantiates a TextField object where the results of the statement will be listed:
var ptrTextField = new runtime.flash.text.TextField();
ptrTextField.text = "...result...";
ptrWin.stage.addChild( ptrTextField );
TextField is a class contained in the flash.text package of the Flash runtime. You make it available to AIR by referencing it via the runtime package. The documentation for the Flash runtime classes raises one of the few difficulties I see with AIR at this time: figuring out exactly how each class works with the AIR environment, and how to map it to the AIR class structure. This is all there is to the code!
Now you can package the application for distribution. Type this at the command prompt:
adt -package AirQuery.air AirQuery.xml AirQuery.html AirQuery.js AIRAliases.js
The parameters have to be in this order: AirQuery.air is the name of the distributable package, followed by the name of the application descriptor, followed by all the other files the application uses.