Perl DBI - Capturing errors

Question

What's the best way of capturing any DBI errors in Perl? For example if an insert fails because there were illegal characters in the values being inserted, how can I not have the script fail, but capture the error and handle it appropriately.

I don't want to do the "or die" because I don't want to stop execution of the script.

Solution 1

Use the RaiseError=>1 configuration in DBI->connect, and wrap your calls to the $dbh and $sth in a try block (TryCatch and Try::Tiny are good implementations for try blocks).

See the docs for more information on other connect variables available.

for example:

use strict;
use warnings;
use DBI;
use Try::Tiny;
my $dbh = DBI->connect(
    $your_dsn_here,
    $user,
    $password,
    {
        PrintError => 0,
        PrintWarn  => 1,
        RaiseError => 1,
        AutoCommit => 1,
    }
);
try
{
    # deliberate typo in query here
    my $data = $dbh->selectall_arrayref('SOHW TABLES', {});
}
catch
{
    warn "got dbi error: $_";
};

Solution 2

you can also do the following, which will allow you to die, or gracefully handle the errors and continue.

$dbh = DBI->connect($data_src, $user, $pwd) or die $DBI::errstr;
my $sth = $dbh->prepare("DELETE FROM table WHERE foo = '?'");
$sth->execute('bar');
if ( $sth->err )
{
  die "DBI ERROR! : $sth->err : $sth->errstr \n";
}

Comments

[mscha]

Shouldn't you put the connect within the try block as well?

[Ether]

@mscha: that's not necessary - connect will return undef if it fails. (See the docs - you just need to check if a $dbh was returned.)

[Ether]

@mscha: what do you think will happen when trying to call a method on an undefined reference ($dbh)? It will die, which will be caught by the try/catch block. That's fine in this case, as the connection is made immediately before attempting to use it, but in production code (which may connect long before the first db query) you may wish to do something else. Likewise in production code you may want to have special handling for connections that have timed out. tl;dr version: read the manual carefully for usage notes on any method you call!