Using SQLite Databases with AIR - Part 6 - Transactions

Posted At : August 24, 2009 7:48 AM | Posted By : Jason Dean
Related Categories: JavaScript,AIR,Database

Transactions is SQL statements are something that I have come to love. I'm sure you have too. But for those that don't know what transactional control in a database management system is, we'll start with a definition.

Usually, when doing multiple SQL statements in a row in an application, each SQL statement is handled as a atomic unit and is committed permanently to the database before the next one is run. This can be very problematic when those queries depend on each other to work properly to maintain data integrity.

Let's look at a common transaction control example to illustrate this idea. In this example, we are working on a banking application where we are making fund transfers between accounts. This requires three queries.

The queries:

Deduct the transfer amount from the sending account
Add the transfer amount to the receiving account
Record the transfer in a log

So provided nothing goes wrong, this should always work fine. But what if something goes wrong? What if this process gets interrupted between steps 1 and 2? Or between steps 2 and 3? We might end up deducting the amount from the sending account, but then never adding it to the receiving account. We also might perform the transaction but have the log fail, resulting in a bookkeeping issue. Regardless of how the statements fail (server crash, network failure, record locking, etc) we do not want the failure to cause these data integrity issues. That is where transaction control comes in.

When we use transaction control for a set of queries like those described above, the system treats the entire set of queries as a single, atomic unit. We can programatically set up checks to make sure that the statements succeed, if they do, commit them, and if they fail we can perform an "undo", formally known as a rollback

So in the previous example, if we use transaction control, none the SQL statements would be permanently committed until they all succeeded. If any fail, then we would rollback. Let's look at an example.

            // Create a file object that points to the database file, create the connection and a SQLstatement to use
            var db = air.File.applicationStorageDirectory.resolvePath('myDB.db');
            var conn = new air.SQLConnection();
            var stmt = new air.SQLStatement(); // This statement can be reused for multiple sync queries

            // Open the connection and insert it into the statement
            conn.open(db, air.SQLMode.CREATE);
            stmt.sqlConnection = conn;

            // begin the transaction
            conn.begin();

            // Use a try/catch/finally to do error handling
            try {
                // Transfer $140.00 FROM accountNo = 1 to accountNo = 2
                stmt.text = "UPDATE balance SET balance = balance - 140.00 WHERE accountNo = 1";
                stmt.execute();

                stmt.text = "UPDATE balance SET balance = balance + 140.00 WHERE accountNo = 2";
                stmt.execute();

                // Log the transaction, DATE will be automatically inserted
                stmt.text = "INSERT INTO log (accountNo, currentBalance) VALUES (1, (SELECT balance FROM balance WHERE accountNo = 1))";
                stmt.execute();

                stmt.text = "INSERT INTO log (accountNo, currentBalance) VALUES (1, (SELECT balance FROM balance WHERE accountNo = 1))";
                stmt.execute();

                // commit the transaction
                conn.commit();

            } catch(err) {
                // If there is an error, catch it, rollback the transaction and trace the error to the console
                conn.rollback();
                air.trace(err);
            } finally {
                // regardless of what happens, close the connection
                conn.close();
            }

Quite a bit is happening here. I inserted comments so you can see and follow what is happening.

We begin by creating a few objects. The first is a File object that points to a preexisting DB. The DB has two tables, a balance table and a log table. The balance table simply contains the accountNo and the balance, which reflects the current balance. The log table contains the accountNo, the currentBalance, and a date column which defaults to the current date.

The second and third objects we create are a SQLConnection and a SQLStatement. To learn more about this, check out my previous posts.

Next, we begin the transaction with the begin() method from the connection object.

We are using a try/catch block to handle any errors that occur. Within the Try section of the block we simply create and execute each statement back-to-back. These are synchronous queries, so we don't need to worry about creating listeners or about using the same SQLStatement for each. Each will wait to execute until the previous one has committed.

At the end of the Try section we commit the transaction using the cleverly named commit() method of the connection.

In the Catch block we will "catch" any errors that occur in any of the code from the Try block. If any of the SQL statements throw an error, this will catch it, output a trace message and call the rollback() method of the connection, which will reverse any of the changes from the previous block that have not been committed, which in this case is all of them.

Lastly, our Finally block will close the connection. We want this to happen whether there is an error or not.

And that is it for the basics of transaction control in AIR. Fun, huh?