Error Handling (Programming the Perl DBI)

4.5.1. 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.

4.5.1.1. 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!

4.5.1.2. 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;

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.[41]

[41]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.

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.

4.5.1.3. 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.

4.5.2. Error Diagnostics

The ability to trap errors within the DBI is very useful, with either manual or automatic error checking, but this information is only marginally useful on its own. To be truly useful, it is necessary to discern exactly what the error was in order to track it down and debug it.

To this end, DBI defines several error diagnostic methods that can be invoked against any valid handle, driver, database, or statement. These methods will inform the programmer of the error code and report the verbose information from the last DBI method called. These are:

$rv  = $h->err();
$str = $h->errstr();
$str = $h->state();

These various methods return the following items of information that can be used for more accurate debugging of errors:

$h- >err() returns the error number that is associated with the current error flagged against the handle $h. The values returned will be completely dependent on the values produced by the underlying database system. Some systems may not support particularly meaningful information; for example, mSQL errors always have the error number of -1. Oracle is slightly more helpful: a connection failure may flag an ORA-12154 error message upon connection failure, which would return the value of 12154 by invoking $h->err(). Although this value is usually a number, you should not rely on that.

$h- >errstr() is a slightly more useful method, in that it returns a string containing a description of the error, as provided by the underlying database. This string should correspond to the error number returned in $h->err().
For example, mSQL returns -1 as the error number for all errors, which is not particularly useful. However, invoking $h->errstr() provides far more useful information. In the case of connection failure, the error:
```
ERROR : Can't connect to local MSQL server
```
might be generated and returned by $h->errstr(). Under Oracle, a connection failure returning the error number of 12154 will return the following string as its descriptive error message:
```
ORA-12154: TNS:could not resolve service name (DBD ERROR: OCIServerAttach)
```

$h- >state() returns a string in the format of the standard SQLSTATE five-character error string. Many drivers do not fully support this method, and upon invoking it to discern the SQLSTATE code, the value:
```
S1000
```
will be returned. The specific general success code 00000 is translated to 0, so that if no error has been flagged, this method will return a false value.

The error information for a handle is reset by the DBI before most DBI method calls. Therefore, it's important to check for errors from one method call before calling the next method on the same handle. If you need to refer to error information later you'll need to save it somewhere else yourself.

A rewriting of the previous example to illustrate using the specific handle methods to report on errors can be seen in the following code:

#!/usr/bin/perl -w
#
# ch04/error/ex3: Small example using manual error checking which also uses 
#                 handle-specific methods for reporting on the errors.

use DBI;            # Load the DBI module

### Attributes to pass to DBI->connect() to disable automatic
### error checking
my %attr = (
    PrintError => 0,
    RaiseError => 0,
);

### Perform the connection using the Oracle driver
my $dbh = DBI->connect( "dbi:Oracle:archaeo", "username", "password" , \%attr )
    or die "Can't connect to database: ", $DBI::errstr, "\n";

### Prepare a SQL statement for execution
my $sth = $dbh->prepare( "SELECT * FROM megaliths" )
    or die "Can't prepare SQL statement: ", $dbh->errstr(), "\n";

### Execute the statement in the database
$sth->execute
    or die "Can't execute SQL statement: ", $sth->errstr(), "\n";

### Retrieve the returned rows of data
while ( my @row = $sth->fetchrow_array() ) {
    print "Row: @row\n";
}
warn "Problem in fetchrow_array(): ", $sth->errstr(), "\n"
    if $sth->err();

### Disconnect from the database
$dbh->disconnect
    or warn "Failed to disconnect: ", $dbh->errstr(), "\n";

exit;

As you can see, it's even more long-winded than using the $DBI::errstr variable, which can at least be interpolated directly into the error messages.

In addition to these three methods, which allow finely grained error checking at a handle level, there are three corresponding variables that will contain the same information, but at a DBI class level:

$DBI::err
$DBI::errstr
$DBI::state

Use of these variables is essentially the same as that of $h->err() and friends, but the values referred to are for the last handle used within DBI. They are particularly handy for interpolating into strings for error messages.

Since these variables are associated with the last handle used within the DBI, they have an even shorter lifespan than the handle error methods, and should be used only immediately after the method call that failed. Otherwise, it is highly likely they will contain misleading error information.

The one case where the variables are very useful is for connection errors. When these errors occur, there's no new handle returned in which to hold error information. Since scripts don't use the internal driver handles, the $DBI::errstr variable provides a very simple and effective way to get the error message from a connect() failure.

In summary, for most applications, automatic error checking using RaiseError and/or PrintError is recommended. Otherwise, manual checking can be used and $DBI::errstr can easily be interpolated into messages. The handle methods are available for more complex applications.

4.5. Error Handling

4.5.1. Automatic Versus Manual Error Checking

4.5.1.1. Manual error checking

4.5.1.2. Automatic error checking

4.5.1.3. Mixed error checking

4.5.2. Error Diagnostics