Recipe 14.11 Dealing with Database Errors

14.11.1 Problem

You want your program to catch and handle database errors, possibly displaying informative error messages.

14.11.2 Solution

The best solution is to enable RaiseError when you connect to the database, then wrap database calls in eval:

$dbh = DBI->connect($DSN, $user, $password,                     { RaiseError > 1 }); eval {   $dbh->do($SQL);   $sth = $dbh->prepare($SQL2);   $sth->execute( );   while (@row = $sth->fetchrow_array) {     # ...   } }; if ($@) {   # recover here using $DBI::lasth->errstr to get   # the error message }

14.11.3 Discussion

The logic here is simple: first tell DBI to die if there's a problem with your SQL (otherwise, the database can't do what you wanted it to). Then, wrap the code that might die in eval to catch fatal errors. Next, check $@ (either the error message you would have died with or empty if there was no error) to see whether something went wrong. If it did, somehow deal with the error.

DBI supplies the $DBI::lasth variable, containing the last handle acted on. If something went wrong, that's the handle that caused it. You could use the $@ error message, but that also has the "died at file ... line ..." text from die that you might not want. To discover the SQL statement that died, use $DBI::lasth->{Statement}. If you are using only the one handle, you can call the methods directly on your handle instead of on $DBI::lasth:

$msg = $dbh->errstr; $sql = $dbh->{Statement};

An alternative approach is to disable RaiseError and check the return value for each database call. Methods such as do and execute return a true value if successful, so you can say:

$dbh->do($SQL) or die $dbh->errstr; $sth->execute( ) or die $sth->errstr;

The do method returns the number of rows affected, but in such a way that it always returns a true value if successful. (If you're curious how to do this, see the Introduction to Chapter 1 for the gory details of how Perl decides what's true and what's false.)

If you're debugging error catching and error handling, you might want to add the PrintError attribute to your database connection:

$dbh = DBI->connect($DSN, $user, $password,                     { RaiseError => 1, PrintError => 1 });

When problems occur on the handle, PrintError issues a warning before RaiseError calls die. So even if you catch the error with eval and it doesn't necessarily kill your program, you still get to see the text of the error message.

14.11.4 See Also

The documentation with the DBI module from CPAN; http://dbi.perl.org; Programming the Perl DBI; Recipe 14.12