Dealing with Database Errors (Perl Cookbook, 2nd Edition)

## 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 `die` d 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

Copyright © 2003 O'Reilly & Associates. All rights reserved.