SQLQueue: Chaining Asynchronous SQL statements using the AIR database access API

The AIR database access API is asynchronous. This works well for executing expensive queries without blocking the user interface. However, sometimes you may need to run a series of short and interdependent statements, and in that case, executing each statement asynchronously and independently can make your code difficult to manage.

Consider an example where you have an array of contact objects that you need to persist to your local database (this might be part of a data synchronization process). Your Flex code might look like this:

for (var i:int = 0; i < contacts.length; i++)
{
      var stmt:SQLStatement = new SQLStatement();
      stmt.sqlConnection = connection;
      stmt.text = "INSERT INTO CONTACT (FIRST_NAME, LAST_NAME, PHONE) VALUES(?, ?, ?)";
      stmt.parameters[1] = contacts[i].firstName;
      stmt.parameters[2] = contacts[i].lastName;
      stmt.parameters[3] = contacts[i].phone;
      stmt.addEventListener(SQLEvent.RESULT, resultHandler);
      stmt.addEventListener(SQLErrorEvent.ERROR, faultHandler);
      stmt.execute();
}

Depending on the requirements of your application, this code may lead to several questions…

• Where do you write the code you want to execute after “all” the statements complete successfully?
• What if you want to “not run” the remaining statements as soon as one statement fails?

There are times when you want a more linear execution of asynchronous statements. To support this pattern I wrote a simple class I called SQLQueue. SQLQueue allows you to put SQL statements in a queue and uses recursion to execute them one after the other.

Your code would now look like this:

var q:SQLQueue = new SQLQueue();
for (var i:int = 0; i < contacts.length; i++)
{
      q.addItem("INSERT INTO CONTACT (FIRST_NAME, LAST_NAME, PHONE) VALUES(?, ?, ?)",
            [contacts[i].firstName, contacts[i].lastName, contacts[i].phone], true);
}
q.execute(connection, resultHandler, faultHandler);

• If you want to stop execution after one statement fails, specify true (default) as the third argument (stopOnError) of the addItem function.
• “resultHandler” is executed after all your statements complete successfully… so that’s where you write the code that depends on the successful completion of your statements.

Click here to download SQLQueue.as along with a complete example.

SQLQueue is a bare bone implementation… there are many ways it could be improved. For example, you could dispatch “result” and “error” events instead of passing callback functions to the execute method, you could trigger a “progress” event after each statement completes, you could pass prepared statements to the addItem function instead of SQL strings, you could recycle a unique SQLStatement instance, etc.

We may add a synchronous option to the database access API, but in the meantime, this pattern has helped me keep the execution of my interdependent asynchronous SQL statements manageable.