In the previous article, I gave an introduction to SQLite and its place inside or AIR. In this tutorial, I will be explaining the code needed to connect to and query a previously existing SQLite database. This will take two articles to accomplish this, so this article will focus on learning the basic classes and methods that you will need to know. The next tutorial will contain a sample application that implements these features.

Making the Connection

The first step in connecting to local database is to create an instance of the SQLConnection class. Since we are programming asynchronously, we will need to add some event listeners for this class. The SQLConnection class dispatches many events, but for this example we will be primarily concerned with two: open and error.

// Instantiate the Class
var connection:SQLConnection = new SQLConnection();
// Event Listener that will tell us when the DB is opened
connection.addEventListener(SQLEvent.OPEN, openSuccess);
// Event Listener that will tell us if an error occurs
connection.addEventListener(SQLErrorEvent.ERROR, openFailure);

At this point, we haven't actually connected to a database. If you remember in the last article, I stated that an SQLite database is contained in a single file. With this being the case, you will first need to create a file object that points to its location. Since I placed the database in the application storage directory, I will create a reference to it there.

// Create a File object that points to the contacts.db file
var database:File = File.applicationStorageDirectory.resolvePath("contacts.db");

Finally, to actually open a connection to the database, you simply need to call the openAsync method of the SQLConnection instance. If we were working with the database synchronously, we would use the open method. We will pass the file object in as the first argument (in either synchronous or asynchronous modes). If you were to pass null for this first argument, a database would be created in memory (as opposed to a file).

// Create a File object that points to the contacts.db file
connection.openAsync( database );

Note: If you haven't used the File class before, check out Rich's excepts from his book that have been posted here on InsideRIA. I have included links at the bottom of this article.

Querying Data

To actually query the data, you will need to know another class: SQLStatement. This is the class that will actually contain your SQL Query. After the class is instantiated, you need to pass the reference to the SQLConnection instance that you created previously for the sqlConnection property. In most situations, you will want to listen for two different events of this class: result and error. You can instantiate the class, connect it to SQLConnection, and add its listeners as follows:

// Create New Instance
var query:SQLStatement = new SQLStatement();
// Add the Connection Reference
query.sqlConnection = connection;
// Add Event Listeners
query.addEventListener( SQLEvent.RESULT, onResult );
query.addEventListener( SQLErrorEvent.ERROR, onError );

Since you have created these event listeners, you will also need to create the functions that you just referenced. We will just create shell methods now, and will implement them in the next tutorial.

private function onResult( event:SQLEvent ):void {
 	// The Query Worked
}
private function onError( event:SQLErrorEvent ):void {
 	// There Was an Error
}

After these steps, you can create your actual query. The text property of the SQLStatement class contains the SQL query. To actually execute the query, you need to call the execute() method of the SQLStatement instance.

// SQL Query
query.text = "SELECT lastName, firstName from contacts";
// Execute the Query
query.execute(); 

In the next tutorial you will work with an actual AIR application that has a preexisting database. You will learn how to access the results of an SQLStatement as well as how to determine error information if an error occurs.

Language Reference

SQLConnection [ ActionScript , JavaScript ]
SQLStatement [ ActionScript, JavaScript ]

Additional Resources

Beginning AIR - Accessing the File System Part 1 (Rich Tretola) - InsideRIA
Beginning AIR - Accessing the File System Part 2 (Rich Tretola) - InsideRIA