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.

  • Chip Temm

    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.

  • christophe

    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

  • Mike K

    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.

  • very good article,

  • Hello Good Site Thanks you….

  • Hello! Good Site….

  • Excelent! this pattern helped me a LOT. have a nice day.

  • Thank you for sharing a complete example. It helped a lot.

  • Does anyone know if there is another language or set of commands beside SQL for talking with databases?

    I’m working on a project and am doing some research thanks

  • Does anyone know if there is another language or set of commands beside SQL for talking with databases?

    I’m working on a project and am doing some research thanks

  • Pingback: äåìîíòàæ çäàíèé()

  • I am not a fan of using API. Struggle with it every time.

  • I’m working on a project and am doing some research thanks

  • John Hall

    Christophe,

    Thanks so much for this post. Even though it’s relatively old by AIR standards, it works perfectly. However, I’m curious in the source file for the queue, the next to last line says

    stmt.parameters[ i + 1 ] = params[ i ];

    which would error out for me until I changed i+1 to i. Am I missing something and fooling myself that all seems to be working fine now?

    As always, thanks for your contributions.

  • I can’t get enough of your blog, you are an excellent writer!

  • Hi, I want to see your code for the SQL Queue but the link is broken

  • Alfonso

    Any change we can get the link working agian? Thanks!

  • mass production, cost-effectiveness and quality of production we.http://www.jonsunsport.com/antrenman-malzemeleri.html

  • obrg gracias amigos

css.php