Error Handling

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.

Automatic Versus Manual Error Checking

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.

Manual error checking

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!

Automatic error checking

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.

Mixed error checking

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.