In the previous tutorial, I demonstrated how to return all of the rows of a database table and access the results within AIR. Many times you will not want to retrieve all of the rows in a table, so today I will examine some techniques for querying your tables. I will focus on two main areas: Strongly Typing Results and Parameterized Queries.

Strongly Typing Your Results

As I mentioned in the previous article, by default SQLResult returns all of your data as weakly-typed objects. AIR has the capability to return strongly-typed values to you as well. To do this, you need to use the itemClass property of the SQLStatement class. There are a few important things to consider though:

Any class assigned to this property must have a constructor that does not require any parameters. In addition, the class must have a single property for each column returned by the SELECT statement. It is considered an error if a column in the SELECT list does not have a matching property name in the itemClass class.

Reference: http://livedocs.adobe.com/flex/3/langref/flash/data/SQLStatement.html#itemClass

If you already have VO's for your data, this would work very well. If your class does not meet these requirements, your application will throw an error and let you know what property it could not map to your class. If things work properly, your results should look different from the last tutorial. Instead of a generic weakly-types Object, the results come back in the strongly typed format of choice (the sample below is from the example application included with this article). You can use the itemClass property as below:

// Import the Needed Class
import vo.Conctact;
...
// Set the itemClass Property
query.itemClass = Contact;

Figure 1. Weakly Typed SQLResult Data

Figure 2. Striongly Typed SQLResult Data

Parameterized Queries

Another great feature within AIR is the ability to have parameterized queries. This gives many benefits including: cleaner code, protection again SQL injection, and even closer type-matching to the database table. Basically, it allows you to substitute values in the query upon execution. Let's look at an example:

// Create SQLStatement
var query:SQLStatement = new SQLStatement;
var queryString:String = "SELECT * from contacts WHERE firstName = :firstName AND lastName = :lastName";
...
// ADD-In Parameter Values
query.parameters[":firstName"] = "David";
query.parameters[":lastName"] = "Tucker";

// Populatr and Execute the Query
query.text = queryString;
query.execute;

In this example, the query string contains two parameters. Parameters either begin with a : or a @ symbol. After the symbol, is the name of the given parameter. The actual instance of the query class also contains a property, parameters. You can then assign your parameter names and values to the parameters property as shown above.

There is another way that you can use parameters - indexed parameters. When you use indexed parameters, you don't refers to parameters by name, but rather you refer to them in the order that they appear in the query string. Instead of using the previously mentioned symbols, indexed parameters are denoted by a ? in the query string.

// Create SQLStatement
var query:SQLStatement = new SQLStatement;
var queryString:String = "SELECT * from contacts WHERE firstName = ? AND lastName = ?";
...
// ADD-In Parameter Values
query.parameters[0] = "David";
query.parameters[1] = "Tucker";

// Populatr and Execute the Query
query.text = queryString;
query.execute;

Using either of these methods provides much cleaner and concise code than attempting to inject the value directly into the query string.

Example Application

This sample application uses both of the principles covered today: strongly typed results and parameterized queries. This application also demonstrates the ability of AIR to handle larger databases. Unlike the previous example (with 500 rows), this example has 10,000 rows. Despite this additional data, the application still runs very quickly (at least on my machine). This database is also included in the application files should you want to it one of your own projects. If you want to look at the data, you can use of the SQLite administration tools that I mentioned in the first article of this series.

To use the application, you will first need to click the connect button. This will open the connection to the database file. Next, you can type in anything into the search field and the application will look for matching values in either the firstName or lastName fields. The results will be displayed in the DataGrid. I also created a Contact value-object which allows the results to be strongly typed. Finally, this data is passed to the SQLStatement through a parameterized query. As always, View Source is enabled on the application and the source files can be downloaded below.

Application Source Code
Download (259 kb)

Read more from David Tucker.