The handling of errors within programs, or the lack thereof, is one of the more common causes of questions concerning programming with DBI. Someone will ask “Why doesn’t my program work?” and the answer generally runs along the lines of “Why aren’t you performing error checking?” Sure enough, nine out of ten times when error checking is added, the exact error message appears and the cause for error is obvious.
Early versions of the DBI required programmers to perform their own error checking, in a traditional way similar to the examples listed earlier for connecting to a database. Each method that returned some sort of status indicator as to its success or failure should have been followed by an error condition checking statement. This is an excellent, slightly C-esque way of programming, but it quickly gets to be tiresome, and the temptation to skip the error checking grows.
The DBI now has a far more straightforward error-handling capability
in the style of
exception
s.
That is, when DBI internally detects that an error has occurred after
a DBI method call, it can automatically
either
warn()
or
die()
with
an appropriate message. This shifts the onus of error checking away
from the programmer and onto DBI itself, which does the job in the
reliable and tireless way that you’d expect.
Manual error checking still has a place in some applications where failures are expected and common. For example, should a database connection attempt fail, your program can detect the error, sleep for five minutes, and automatically re-attempt a connection. With automatic error checking, your program will exit, telling you only that the connection attempt failed.
DBI allows mixing and matching of error-checking styles by allowing you to selectively enable and disable automatic error checking on a per-handle basis.
Of course,
the
DBI still allows you to manually
error check your programs and the execution of DBI methods. This form
of error checking is more akin to classic C and Perl programming,
where each important statement is checked to ensure that it has
executed successfully, allowing the program to take evasive action
upon failure.
DBI, by default, performs basic automatic error reporting for you by
enabling the PrintError
attribute. To disable this
feature, simply set the value to 0
either via the
handle itself after instantiation, or, in the case of database
handles, via the attribute hash of the connect( )
method.
For example:
### Attributes to pass to DBI->connect( ) %attr = ( PrintError => 0, RaiseError => 0 ); ### Connect... my $dbh = DBI->connect( "dbi:Oracle:archaeo", "username", "password" , \%attr ); ### Re-enable warning-level automatic error reporting... $dbh->{PrintError} = 1;
Most DBI methods will return a false status value, usually
undef
, when execution fails. This is easily tested
by Perl in the following way:
### Try connecting to a database my $dbh = DBI->connect( ... ) or die "Can't connect to database: $DBI::errstr!\";
The following program disables automatic
error handling, with our own tests to check for errors. This example
also moves the attributes into the connect( )
method call itself, a clean style that’s commonly used:
#!/usr/bin/perl -w # # ch04/error/ex1: Small example using manual error checking. use DBI; # Load the DBI module ### Perform the connection using the Oracle driver my $dbh = DBI->connect( undef, "stones", "stones", { PrintError => 0, RaiseError => 0 } ) or die "Can't connect to the database: $DBI::errstr\n"; ### Prepare a SQL statement for execution my $sth = $dbh->prepare( "SELECT * FROM megaliths" ) or die "Can't prepare SQL statement: $DBI::errstr\n"; ### Execute the statement in the database $sth->execute or die "Can't execute SQL statement: $DBI::errstr\n"; ### Retrieve the returned rows of data my @row; while ( @row = $sth->fetchrow_array( ) ) { print "Row: @row\n"; } warn "Data fetching terminated early by error: $DBI::errstr\n" if $DBI::err; ### Disconnect from the database $dbh->disconnect or warn "Error disconnecting: $DBI::errstr\n"; exit;
As can be seen from the example, the code to check the errors that may have arisen in a DBI method is actually longer than the code to perform the operations themselves. Similarly, it is entirely possible that you may just genuinely forget to add a check after a statement, which may result in extremely bizarre program execution and error reporting, not to mention hours of wasted debugging time!
The automatic
error checking capabilities of the DBI operates on two levels. The
PrintError
handle attribute tells DBI to call the
Perl warn( )
function (which typically
results in errors being printed to the screen when encountered) and
the RaiseError
handle attribute (which tells DBI
to call the Perl die( )
function upon error, typically
causing the script to immediately abort).
Because the standard Perl functions of warn( )
and
die( )
are used, you can change the effects of
PrintError
and RaiseError
with
the $SIG{_ _WARN_ _}
and $SIG{_ _DIE_ _}
signal handlers. Similarly, a
die( )
from RaiseError
can be
caught via eval { ... }
.
These different levels of automatic error checking can be turned on for any handle, although database handles are usually the most common and useful. To enable the style of automatic error checking you want, you may set the value of either of the following two attributes:
$h->{PrintError} = 1; $h->{RaiseError} = 1;
Similarly, to disable
automatic error checking, simply set the value of these attributes to
0
.
If both RaiseError
and
PrintError
are enabled, an error will cause
warn( )
and die( )
to be
executed sequentially. If no $SIG{_ _DIE_ _}
handle has been defined, warn( )
is skipped to
avoid the error message being printed twice.[39]
A more common way in which these attributes are used is to specify
them in the optional attribute hash supplied to
DBI-
>connect( )
when
connecting to a database. Automatic error checking is the recommended
style in which to write DBI code, so PrintError
is
enabled by default in DBI-
>connect( )
. You can think of this as training wheels for novices and
grease for quick-and-dirty script writers. Authors of more
significant works usually either enable RaiseError
or disable PrintError
and do their own error
checking.
The following short example illustrates the use of
RaiseError
instead of manual error checking is:
#!/usr/bin/perl -w # # ch04/error/ex2: Small example using automatic error handling with # RaiseError, i.e., the program will abort upon detection # of any errors. use DBI; # Load the DBI module my ($dbh, $sth, @row); ### Perform the connection using the Oracle driver $dbh = DBI->connect( "dbi:Oracle:archaeo", "username", "password" , { PrintError => 0, ### Don't report errors via warn( ) RaiseError => 1 ### Do report errors via die( ) } ); ### Prepare a SQL statement for execution $sth = $dbh->prepare( "SELECT * FROM megaliths" ); ### Execute the statement in the database $sth->execute( ); ### Retrieve the returned rows of data while ( @row = $sth->fetchrow_array( ) ) { print "Row: @row\n"; } ### Disconnect from the database $dbh->disconnect( ); exit;
This example is both shorter and more readable than the manual error checking shown in a following example. The actual program logic is clearer. The most obvious additional benefit is that we can forget to handle error checking manually after a DBI operation, since the DBI will check for errors for us.
You can mix error checking styles within a single program, since automatic error checking can be easily enabled and disabled on a per-handle basis. There are plenty of occasions where mixed error checking is useful. For example, you might have a program that runs continuously, such as one that polls a database for recently added stock market quotes every couple of minutes.
Disaster occurs! The database crashes! The ideal situation here is that the next time the program tries connecting to the database and fails, it’ll wait a few minutes before retrying rather than aborting the program altogether. Once we’ve connected to the database, the error checking should now simply warn when a statement fails and not die.
This mixed style of error checking can be broken down into two areas:
manual error checking for the
DBI-
>connect( )
call, and
automatic error checking via PrintError
for all
other statements. This is illustrated in the following example
program:
#!/usr/bin/perl -w # # ch04/error/mixed1: Example showing mixed error checking modes. use DBI; # Load the DBI module ### Attributes to pass to DBI->connect( ) to disable automatic ### error checking my %attr = ( PrintError => 0, RaiseError => 0, ); ### The program runs forever and ever and ever and ever ... while ( 1 ) { my $dbh; ### Attempt to connect to the database. If the connection ### fails, sleep and retry until it succeeds ... until ( $dbh = DBI->connect( "dbi:Oracle:archaeo", "username", "password" , \%attr ) ) { warn "Can't connect: $DBI::errstr. Pausing before retrying.\n"; sleep( 5 * 60 ); } eval { ### Catch _any_ kind of failures from the code within ### Enable auto-error checking on the database handle $dbh->{RaiseError} = 1; ### Prepare a SQL statement for execution my $sth = $dbh->prepare( "SELECT stock, value FROM current_values" ); while (1) { ### Execute the statement in the database $sth->execute( ); ### Retrieve the returned rows of data while ( my @row = $sth->fetchrow_array( ) ) { print "Row: @row\n"; } ### Pause for the stock market values to move sleep 60; } }; warn "Monitoring aborted by error: $@\n" if $@; ### Short sleep here to avoid thrashing the database sleep 5; } exit;
This
program
demonstrates that with DBI, you can
easily write explicit error checking and recovery code alongside
automatic error checking.
[39] The
exact behavior when both attributes are set may change in future
versions. This is something to consider if the code is inside an
eval
.