February 24, 2021
Hot Topics:

Programming the Perl DBI

  • By Developer.com Staff
  • Send Email »
  • More Articles »

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:


    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
    or die "Can't execute SQL statement: ", $sth->errstr(),

### 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
    or warn "Failed to disconnect: ", $dbh->errstr(), "\n";


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:


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.

Page 5 of 8

This article was originally published on April 10, 2003

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date