Using SQLite Databases with AIR - Part 6 - Transactions

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:

  1. Deduct the transfer amount from the sending account
  2. Add the transfer amount to the receiving account
  3. 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?

Comments
Al's Gravatar Hi there...

Cool article about using transaaction in Air and very informative as well.
Thing is, the values that you have used here are hard coded, how would we
go about using dynamic values from other tables within the database to perform the transaction for updates, inserts, etc.

I was thinking along the lines of a loop function to match ID's in one table with another and then perform some math like ADD, SUB, etc, but something which I can't get my head around is how do we keep track of which info has been updated so that we don't get a duplicate, if I am making any sense..:))))

I am currently working on this solution as I write this to you, I think I might be able to get some parts working put I don't think that it will work without breaking in some places.

But If you have in insight on how to do this based on the bank transaction example, this would help greatly.

Thank you once again for the examples, they have been very helpful in many ways.

Stay well
# Posted By Al | 9/10/10 2:26 AM
Jason Dean's Gravatar Sorry Al, I've been meaning to answer your question in another post, but I have been very busy.

As far as I know, though I have not yet tested it, as long as you begin the transactions, do the queries (whether SELECT, UPDATE, DELETE, whatever) and then commit or rollback the transaction, all with the same SQLConnection, then it should all work as one atomic action.

So if you do an Insert into a table, and then want to get PK out of that table (by getting the highest value, for example), you should be able to do an INSERT, then a SELECT, then do any UPDATEs that you need to, and if you start a transaction with the connection, then do al of those things, then end the transaction, then you should not need to worry about crossing the streams with other actions.

Does that answer your question?
# Posted By Jason Dean | 9/16/10 10:24 PM
BlogCFC was created by Raymond Camden. This blog is running version 5.9.1. Contact Blog Owner