Programming the Perl DBI

Combining Automatic Error Handling with Transactions

Transactions, as you’ve probably realized by now, are closely related to error handling. This is especially true when you have to clean up after an error by putting everything in the database back to the way it was before the transaction started.

Chapter 4, we discussed error handling in some detail and sang the praises of using the RaiseError attribute for automatic error detection.

Imagine combining the automatic error detection of the DBI’s RaiseError attribute and the error trapping of Perl’s eval { ... } and the error handling properties of transactions. The result is a simple yet powerful way to write robust applications in Perl.

There is a fairly common structure to these kind of applications, so to help us discuss the issues, we’ve included the following example.

This outline example processes CSV files containing sales data from one country, it fetches currency exchange rate information from a web site and adds that to the data, it then performs a series of inserts, selects, updates and more inserts of the data to update the database. That processing is repeated for a series of countries.

Here’s the code:

### Connect to the database with transactions and error handing enabled my $dbh = DBI->connect( "dbi:Oracle:archaeo", "username", "password" , { AutoCommit => 0, RaiseError => 1, } ); ### Keep a count of failures. Used for program exit status my @failed; foreach my $country_code ( qw(US CA GB IE FR) ) { print "Processing ...