Nearly all the methods of Adobe’s SQLConnection class are asynchronous. While this is nice when your running expensive operations (your interface won’t hang up), it can be quiet tough to deal with considering we come from a land where database operations have always been synchronous.

Before we begin, you might want to download this SQLite database .

A short example

If you’ve been using ActiveRecord like me, you’ve probably forgotten how to write SQL. Well, dust off your SQL skills because you’ll need to know how to write queries if you plan on using Adobe AIR’s SQLite support.


var connection = new air.SQLConnection();
connection.open(air.File.applicationResourceDirectory.resolve('development.sqlite'));

var statement = new air.SQLStatement();
statement.sqlConnection = connection;
statement.text = "SELECT * FROM contacts";
statement.execute();

The code above, albeit a little long winded, is the bare necessities for executing a query. While it looks fairly straight forward on the surface, we’ve already run into our first problem. If the execute operation is asynchronous, how do we know when we can begin to retrieve and manipulate the data returned? We can do it one of two ways, register an event listener or pass a Responder object to the execute method.

Using a Responder to handle results

The first option we have is passing a Responder instance to execute. A Responder is an object that encapsulates two functions, one for success that passes an SQLResult object, the other for a failure that passes an SQLError object. Lets modify our execute method:


statement.execute(-1, new air.Responder(
 function(result) {
   result.data.forEach(function(row) {
     air.trace(row.name);
   })
 },

 function(error) {
   air.trace(error.message);
 }));

If you execute this, you should get the names of our contacts printed to the terminal. Awesome! Now, change the query to add a table name that doesn’t exist, you should be the error message printed to the terminal.

The benefit of using a Responder object is there is no need to register and remove event listeners. In addition to that, there is no need to call the getResults method (see below) on the SQLStatement instance.

Registering Event Listeners

The second option we have is to register event listeners. Starting back at our original code, we can modify it to reflect the changes below.


//Directly below statement.sqlConnection = connection

statement.addEventListener(air.SQLEvent.RESULT, function callback(event) {
  var contacts = statement.getResult().data;
  contacts.forEach(function(contact) {
    air.trace(contact.name);
  });
  statement.removeEventListener(air.SQLEvent.RESULT, callback);
});

statement.addEventListener(air.SQLErrorEvent.ERROR, function error(event) {
  air.trace(event.error.message);
  statement.removeEventListener(air.SQLErrorEvent.ERROR, error);
});

statement.text = "SELECT * FROM contacts";
statement.execute();

This is a lot more code and it looks fairly nasty in comparison to good ol’ synchronous connections. Unfortunately, this is the world we live in with AIR. The shit hasn’t really hit the fan yet, let’s see what happens when we try to run a new query directly after our first one.


...
     statement.text = "SELECT * FROM contacts";
     statement.execute();
     statement.text = "SELECT * FROM cases";
     statement.execute();

We get an error: ”Error: Text property cannot be changed while executing.” Oops! It seems we can’t execute two queries simultaneously. We have to wait for one query to finish before the other one begins¹.

Dealing with multiple dependent queries

You can run single queries all day, preferably using a Responder object, but what happens when you need to run multiple queries and the later query is dependent on the results of the first one? It’s not pretty, but the cleanest way I’ve found to do it is to nest callbacks. I’m going to dump a load of code on you, and you can take a moment to soak it in.


 var SQLiteConnector = {
   connect: function() {
     return (function() {
       var connection = new air.SQLConnection();
       connection.open(air.File.applicationResourceDirectory.resolve('development.sqlite'));
       return new SQLiteAdapter(connection);
     })();
   }
 }

 var SQLiteAdapter = Class.create();
 SQLiteAdapter.prototype = {
   initialize: function(connection) {
     this.connection = connection;
     this.statement  = new air.SQLStatement();
     this.statement.sqlConnection = this.connection;
   },

   execute: function(sql, params, callback) {
     this.statement.text = sql;
     this.statement.clearParameters();
     for(param in params) {
       this.statement.parameters[':' + param] = params[param];
     }

    this.statement.execute(-1, new air.Responder(function(results) {
      if(typeof callback == 'function') 
        callback(results.data);
    }.bind(this), this.onFail.bind(this)));
   },

   onFail: function(error) {
     air.trace(error.message);
   }
 }

The above code is written using Prototype, but it’s fairly simple to see how to adapt this to PAJ. With that disclosure, lets see what’s going on here.

The first thing I’ve created is a singleton object that connects to the database and returns an instance of our ‘SQLiteAdapter’. The SQLiteAdapter just makes our life easier by wrapping some of the lower level database operations and also deals with setting up our callbacks so we don’t have to. Lets put it to use.


 var connection = SQLiteConnector.connect();
 connection.execute('SELECT * FROM contacts WHERE name = :name', {name: 'Bob'}, 
 function(contacts) {
   air.trace(contacts[0].name)
 });

So, I’m passing 3 arguments to execute, the sql, parameters and callback. The sql is an unprepared string (notice ’:name’). The parameters are what we use to replace the placeholders in our sql text. See here for more info on parameters. The final argument is our callback and it also accepts an argument of it’s own, our data returned by the query.

If you look in the execute method, you’ll see that we’re passing our own function to a Responder object, and within that function, we invoke our callback, passing it the results of the query via results.data. The bind isn’t necessarily needed, but if we wanted to invoke methods of our class, we use it to control the execution scope. If you run this you should see ”Bob” printed to the terminal. But now that we have a contact object, how can we use that in another query? Nested callbacks is the most efficient way I’ve found so far.

Why nested callbacks? Why not a separate function somewhere else in our file? The answer for me is simple; I want to group related code, I want to read my code as if it was being executed from top to bottom, I don’t want to have to fumble through potentially thousands of lines of code to find the callback that responds to a certain query. This is personal preference, if you want to use named functions or the like, go ahead. But, on with the show, what does this look like?


 var connection =  SQLiteConnector.connect();
 connection.execute('SELECT * FROM contacts WHERE name = :name', {name: 'Bob'}, 
 function(contacts) {
   connection.execute('SELECT * FROM cases WHERE contact_id = :id', {id: contacts[0].id},
    function(kase) {
      air.trace(contacts[0].name, kase[0].title);
    });
 });

Scary stuff, I know, but lets look at what’s going on here. The first thing we do is fetch a contact record from the database, and in the callback for this query, we execute another query that gets a case record for that contact, again passing in a callback so we can finally manipulate the data. In a traditional synchronous operation, this might look like the code below.


var bob = connection.execute("SELECT * FROM contacts WHERE name = :name", {name: 'Bob'})[0];
var kase = connection.execute("SELECT * FROM cases WHERE contact_id = :id", {id: bob.id})[0];
air.trace(kase.name);

So, it all boils down to either a) nested anonymous functions or b) named functions spread throughout your code.

AIR: The Good, Bad, and Ugly

The Bad

No command line support, won’t be in 1.0 either.
Can’t open native applications from within an AIR app.
Psychedelic 70’s Desktop. Just look at the samples, not one with a native look. It’s really easy to slap something together, but it’s also possible to create native looking applications (Minus the unified toolbar on OS X).
No direct Ruby support. You can use a local proxy server, but that server can’t be started from the application, so it has to be hand cranked if you want to use Ruby to write AIR applications.
No XPath support (document.evaluate). Webkit has this, not sure why AIR doesn’t considering it uses what I assumed to be a fairly recent Webkit.
Incomplete documentation (Note the Responder object says nothing about how we used it in this article, only refers to NetConnection.
All the developers seem to be on the bus. I’ve yet to get a response on a forum post or email.
Few details on when the next beta will be released or exactly what features/bugs will be addressed.

The Good

Cross platform
One CSS implementation, One JavaScript implementation. Sweet harmony.
HTML and JavaScript or Flex and Flash
Extensions to JavaScript, File System Access, SQLite support
Free Book and Videos
You can use Prototype, JQuery, Mootools, Dojo, YUI, it doesn’t matter.

The Ugly

Asynchronous Database operations
Did I mention the non native look?

Wrapping up

Despite my criticisms of AIR, I’m really really excited about it. It has real potential and I think we’ll see a majority of the issues outlined here addressed in future releases. I encourage you to download the host of free applications and play around with it.