Using SQLite Databases with AIR - Part 3 - Asynchronous Database Connection

So in my last AIR and SQLite post we talked about Synchronous Database Connections in AIR.

In many cases, synchronous connections may be all you need. If your queries are fast and a slight applicaiton pause is not a concern, or if you have a need for rigid program flow control, then synchronous connections are great. But there may come a time when you do not want the program to pause during a query, or series of queries. You may want the user to be able to continue working while the queries take place in the background. This is where asynchronous queries come in.

An asynchronous query works by creating an asynchronous connection to the DB and then firing off the query to run in the background while the program continues running. You, the developer, can then set up a listener that "listens" for an event to be dispatched from the SQLite database engine letting you know that the query has finished. The event object will then contain, among other things, any results from the query.

Let's look at an example.



    // Create a new connection
    dbConn = new air.SQLConnection();
                        
    // Open an aSync connection to the db file
    dbConn.openAsync(db, air.SQLMode.CREATE);
                        
    //add a listener for when the event is open
    dbConn.addEventListener(air.SQLEvent.OPEN, function(){
        $(document).trigger("connectionMade", dbConn);
    });

In this series of statements, just as in our example for a synchronous connection, we are making a connection to the database, and if the db file does not exist, it will create it.

First we make a new SQLConnection object, and then we open the object using the SQLMode.CREATE constant. But notice that this time we are calling the openAsync() method on the connection instead of just open(). This is all we need to do to create an asynchronous connection instead of a synchronous one.

Now, even the opening of the connection is done asynchronously, so the application is going to continue doing what it does while that connection is being made. So if we want the application to do anything once the connection is made, we need to "listen" for the event that is fired when the connection is open. That event is air.SQLEvent.OPEN.

The last statement in our script above sets up an event listener on our connection that says "when the connection is opened and ready for use, execute this callback function". In this case, the call back function dispatches an event, using jQuery, to notify the application that the database connection is ready, it is including the newly made connection object along with the event data so that it can be used in other places. Then the application can react to that by creating tables, if needed.


    // Hey look at that, a connection was made, sweet!
    $(document).bind("connectionMade", function(e, dbConnection) {
    
        dataMgr.createTables(dbConnection);
    })

So elsewhere in our application, like in some sort of controller, we have a jQuery listener set up using the bind() method that is listening for the connectionMade event to be dispatched. NOTE: I am using jQuery to make my examples extra awesome. You can use another framework if you choose, or you can use plain old JavaScript.

The callback function in the jQuery bind() method will be executed when the connectionMade event is dispatched. This method will receive both the event object and the dbConnection that was passed along. The dbConnection object can then be passed along to the createTables() method (which in my example is in a dataMgr object) for use in that query.


    function createTables(dbConn){
        var statment = new SQLStatement();
            
        statement.sqlConnection = dbConn;
        statement.text = "create table if not exists todoLists (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, priority INTEGER);"
        
        statement.execute();

        statement.addEventListener(air.SQLEvent.RESULT, function(e){
            $(document).trigger("tableCreated");
        };

        statement.addEventListener(air.SQLEvent.ERROR, function(e){
            $(document).trigger("showError", e.target.ERROR);
        };
    }

Here the createTables() function is receiving the connection object and then creating a new SQLConnection object. It then assigns the connection to the statement so that the statement knows which connection to use.

Next we alter the text property of the statement to add a CREATE statement. Now we are ready to execute the statement, so we do so with the execute() method.

The create statement will fire, asynchronously, so we need to set up a listener. When the statement finishes executing it will dispatch air.SQLEvent.RESULT. In the case, unlike with a SELECT statement (which we'll look at in later posts), there really are no results, so we don't need to pass anything along, we just need to notify the application that the tables were created, in case the application needs to do something with the table, like populate it with sample data.

The last thing we do is set up a listener to listen for an ERROR event. In case something goes wrong. In my case, the callback function for this event triggers another event, again using jQuery, and passes along the error text which is contained in e.target.ERROR. Somewhere else in my application I will have a listener set up to listen for showError and act appropriately.

I will stop here for today. I've said previously that we'll look at doing other CRUD with AIR and SQLite in later posts. Otherwise this will get way to long.

Comments
Ben Nadel's Gravatar Very cool stuff. I'm really trying to find time to play with this stuff. I started working on a small HTML prototype last night for a simple app.
# Posted By Ben Nadel | 7/28/09 11:50 AM
Jason Dean's Gravatar @Ben,

I'm looking forward to hearing more about your thoughts and experiences with AIR. Let me know if you have any questions. I'd be happy to help where I can.
# Posted By Jason Dean | 7/28/09 12:08 PM
Phillip Senn's Gravatar So in Adobe AIR samples, tables, they have a db.open but they also have a db.addEventListener.

If it's ok to pause the program while the database opens/processes, is it ok to not add an Event Listener?
# Posted By Phillip Senn | 10/29/10 4:25 PM
Jason Dean's Gravatar Actually, with the db.open() method you would not use an event listener. You would only use an event listener with the db.openAsync() method.

And it is up to you whether it is "ok" to pause the program while it works in the DB. Chances are that sometimes it will be ok, sometimes not.

One consideration is user experience. If the program will pause for a long time while something processes, then you need to think about whether or not doing it Async would be better and whether or not doing it async could cause other problems. For example, if you allow it to process something synchronously and you allow the user to continue using the program, will they try to access something that is not yet ready from the query? if so, you might get an error. So if certain queries need to run in a certain order, or if something needs to complete before anything else is done, then sync queries are best, even if it means pausing the program.

With most small queries. you will likely not be able to tell the difference between sync and async.
# Posted By Jason Dean | 10/29/10 4:35 PM
BlogCFC was created by Raymond Camden. This blog is running version 5.9.1. Contact Blog Owner