RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Query a Local Database in AIR's Desktop RIA Environment : Page 3

One of the more intriguing features of Adobe Integrated Runtime (AIR) is its database connectivity with a local database. Learn how to leverage this feature in desktop by walking through the development of a database application in AIR.

AIR's Database Functions
Several classes implement database-interfacing functionality in AIR, and they offer a surprisingly rich set of functions—the 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 Alert—Or 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.

This application displays the results of the SQL statement in a new window (renderResults function); this is a "native" window. According to the AIR documentation, "AIR supports two distinct APIs for working with windows: the Flash-oriented NativeWindow class and the HTML-oriented JavaScript Window class. Windows created directly with the NativeWindow class use the Flash stage and display list."

One of the reasons I used JavaScript instead of HTML to build the initial HTML container is that populating native windows in AIR is similar. The window is created as follows:

	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.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date