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.
Comments
6 Responses to “SQLQueue: Chaining Asynchronous SQL statements using the AIR database access API”
Leave a Reply
Sounds like you want a transaction, which SQLite supports ( http://www.sqlite.org/lang_transaction.html ). Wouldn’t it be better to use the database’s own API for grouping execution of statements and rely on its rollback algorithm when there were problems? You’d have to write a lot of code to address transactional atomicity problems that the database has already solved.
Guess I need to read more about the data API.
Hi,
This approach is not meant to replace the database transaction management API, but to actually help you use it. The complexity that an asynchronous database access API introduces is that if the execution of the statements representing your logical unit of work is not linear, you have no obvious place to commit or rollback your transaction. (i.e. in the first code snippet above, where could I write the commit or rollback statements?). So this technique simply allows you to chain the execution of your asynchronous SQL statements (execute them one after the other) so that you could rollback the transaction as soon as one statement fails, or commit it when you reach the end of the queue (all the statements in the queue have completed successfully). i.e. In the second code snippet above you would write your commit statement in resultHandler and your rollback statement in faultHandler.
Christophe
Would be nice if it had a timeout event handler, as well as an argument to determine length of time before timeout fires, too.
Hello! Good Site! Thanks you! xlyzprrwhmoo
Thanks for the post. But I reckon that Mike has a point. It would be more useful with a timeout handler.
very good article,
it’s exacly what I needed,
It’s a shame that there is no more support for sql db triggers and errors.