Set custom DBI error handlers

The DBI module lets you handle errors yourself if you don’t like its built-in behavior. DBI lets you handle the errors at either the database or the statement handle level by specifying attributes:

my $dbh = DBI->connect( ..., ..., \%attr );
	
my $sth = $dbh->prepare( ..., \%attr );

There are several attributes that affect error handling, each of which you can use with either a connection or a statement handle:

These attributes are inherited by anything derived from the handle where you set them.

The PrintWarn and PrintError attributes do just what they say. They are on by default, and they don’t stop your program. In this example, you prepare a statement that expects one bind parameter, but when you execute it, you give two parameters instead:

use DBI;

my $dbh = DBI->connect( 'dbi:SQLite:dbname=test.db', '', '', {} );

my $sth = $dbh->prepare( 'SELECT * FROM Cats WHERE id = ?' );
$sth->execute( 1, 2 );

while( my @row = $sth->fetchrow_array ) {
	print "row: @row\n";
	}

print "Got to the end\n";

Since PrintError is true by default, DBI prints the error, but it allows the program to continue even though there was an error:

DBD::SQLite::st execute failed: called with 2 bind variables when 1 are needed at dbi-test.pl line 12.
Got to the end

If you set the ShowErrorStatement attribute, you get a better error message because DBI appends the SQL statement that you tried to execute. You can set this either database handle or the statement handle, but if you don’t know which statement is causing the problem, it’s easier to set it as part of the database handle:

# The rest of the program is the same
my $dbh = DBI->connect( 'dbi:SQLite:dbname=test.db', '', '', {
	ShowErrorStatement => 1,
	} );

The error message shows the SQL statement, but the program still continues:

DBD::SQLite::st execute failed: called with 2 bind variables when 1 are needed [for Statement "SELECT * FROM Cats WHERE id = ?"] at dbi-test.pl line 12.
Got to the end

The RaiseError attribute turns errors into fatal errors that you can trap with eval { ... } or Try::Tiny (Item 103: Handle Exceptions Properly) (or not trap if you want your program to die):

# The rest of the program is the same
my $dbh = DBI->connect( 'dbi:SQLite:dbname=test.db', '', '', {
	RaiseError         => 1,
	ShowErrorStatement => 1,
	} );
	
use Try::Tiny;

try {
	$sth->prepare( ... );
	$sth->execute( ... );
	}
catch {
	...
	};

The output shows that the program stops (there’s no “Got to the end”), but you see duplicated error messages; the one from PrintError that is just a warning, and the one from RaiseError that kills the program:

DBD::SQLite::st execute failed: called with 2 bind variables when 1 are needed [for Statement "SELECT * FROM Cats WHERE id = ?"] at dbi-test.pl line 14.
DBD::SQLite::st execute failed: called with 2 bind variables when 1 are needed [for Statement "SELECT * FROM Cats WHERE id = ?"] at dbi-test.pl line 14.

Turning off PrintError can fix the duplication:

# The rest of the program is the same
my $dbh = DBI->connect( 'dbi:SQLite:dbname=test.db', '', '', {
	PrintError         => 0,
	RaiseError         => 1,
	ShowErrorStatement => 1,
	} );

Simply raising the exception might be good enough for some applications, but sometimes you want more control of the errors. In those cases, you can handle the errors yourself by providing a code reference to HandleError. In this case, you can just catch the error and print it:

my $dbh = DBI->connect( 'dbi:SQLite:dbname=test.db', '', '', {
	ShowErrorStatement => 1,
	HandleError        => \&dbi_error_handler,
	} );

sub dbi_error_handler {
	my( $message, $handle, $first_value ) = @_;
	
	print "Caught: $message\n";
	
	return 1;
	}

DBI passes your HandleError three arguments: the error string it would have used with PrintError, the handle that generated the error, and first return value from the failing method (which is typically nothing useful since there’s an error of some sort).

The error message shows the you caught the error:

Caught: DBD::SQLite::st execute failed: called with 2 bind variables when 1 are needed [for Statement "SELECT * FROM Cats WHERE id = ?"]
Got to the end

If you want a stack trace, you can use Carp (and curiously, the argument alignment works out!).

use Carp;

my $dbh = DBI->connect( 'dbi:SQLite:dbname=test.db', '', '', {
	ShowErrorStatement => 1,
	HandleError        => \&Carp::confess,
	} );

HandleError is how Exception::Class::DBI inserts its error handler:

my $dbh = DBI->connect( $dsn, $user, $pass, {
	PrintError  => 0,
	RaiseError  => 0,
	HandleError => Exception::Class::DBI->handler,
	});

The DBIx-Log4perl uses HandleError, although it hides the details from you:

my $dbh = DBIx::Log4perl->connect('dbi:Oracle:XE', 'user', 'password');

There are some things that you might want to do when handling the error yourself, depending on what you want to accomplish:

• rollback if you are in the middle of a transaction
• disconnect from the database if you are going to quit
• reconnect to the database if you lost the connection
• print a stack trace

No matter what you want to do, however, it’s HandleError that lets you do it.

Things to remember

• The RaiseError attribute turns DBI handle warning into fatal errors
• You can handle errors yourself by giving HandleError a code reference
• Setting the ShowErrorStatement attribute adds the offending SQL statement to the error message