http://www.perlmonks.org?node_id=437578

svenXY has asked for the wisdom of the Perl Monks concerning the following question:

Hi All,

In contrary to all documentation I was capable of, I wasn't able to catch a DBI error to replace it with another message.
I can get the error message into a varialbe, but cannot prevent DBI from displaying the error again.

Here's some sample code:
use DBI; my $dbh = DBI->connect("DBI:mysql:database=www;host=myhost.domain.com: +3306;user=auser;password=apwd", {RaiseError => 1, PrintError => 0}); my $sql = 'select wrong_col, correct_col from table limit 10'; my $hash_ref; print "Trying HandleError (with RaiseError on):\n"; $hashref = $dbh->selectall_hashref($sql, 'filename', {RaiseError => 1, PrintError => 1, HandleError => \&handle_error() +}); print "Trying RaiseError and eval:\n"; eval{ $hashref = $dbh->selectall_hashref($sql, 'filename', {RaiseError => 1, PrintError => 0}); }; if ($@) { print "\tEval found: $@\n"; } my $sth = $dbh->prepare($sql); print "Trying Errstr:\n"; $sth->execute(); if ($dbh->errstr) { print "\tErrstr: " .$dbh->errstr . "\n"; } sub handle_error { # my $error = shift; print "\tSub 'handle_error': $error\n"; print "\tEnd sub\n"; }

As a result, I get:

$ perl tester.pl Trying HandleError (with RaiseError on): Sub 'handle_error': End sub DBD::mysql::db selectall_hashref failed: Unknown column 'wrong_col' in + 'field list' at tester.pl line 11. Trying RaiseError and eval: DBD::mysql::db selectall_hashref failed: Unknown column 'wrong_col' in + 'field list' at tester.pl line 16. Trying Errstr: DBD::mysql::st execute failed: Unknown column 'wrong_col' in 'field li +st' at tester.pl line 26. Errstr: Unknown column 'wrong_col' in 'field list' - or - when redirecting STDERR: $ perl tester.pl 2>/dev/null Trying HandleError (with RaiseError on): Sub 'handle_error': End sub Trying RaiseError and eval: Trying Errstr: Errstr: Unknown column 'wrong_col' in 'field list'

Basically, the error message is printed to STDERR on all three tests, I was never able to prevent it.
Second, the error was caught by handle_error, but the error message was not handed over as it should be.
The third way worked OK, except that it did print the error message to STDERR...

What do I do/think wrong here?
Thanks for any hints,
Sven

Replies are listed 'Best First'.
Re: DBI::mysql error handling
by RazorbladeBidet (Friar) on Mar 08, 2005 at 15:25 UTC
    Can you try adding a return 1; at the end of your handle_error sub?

    I believe HandleError must return true for the RaiseError/PrintError subs to be skipped.
    --------------
    It's sad that a family can be torn apart by such a such a simple thing as a pack of wild dogs
Re: DBI::mysql error handling
by Mr. Muskrat (Canon) on Mar 09, 2005 at 04:15 UTC

    Here's an example script that I loosely based on your script.

    #!/usr/bin/perl use strict; use warnings; use DBI; my $dbh = DBI->connect("DBI:mysql:database=test;user=auser;password=ap +wd", {RaiseError => 1, PrintError => 1}); # set up error handling $dbh->{HandleError} = sub { handle_error(@_) }; my $sql = 'select wrong_col, correct_col from test limit 10'; our $handle_it; # This is only needed for this contrived example for $handle_it ( 0 .. 1 ) { print "\n", "-" x 40, "\n\n" if $handle_it; print "Trying HandleError (with RaiseError on):\n"; print "handle it? ", ($handle_it) ? 'yes' : 'no', "\n"; my $hashref = $dbh->selectall_hashref($sql, 'filename'); print "$_\t-->\t$hashref->{$_}\n" for (keys %{$hashref}); print "errstr says: ", $dbh->errstr, "\n" if $dbh->errstr; } sub handle_error { my ($error, $dbh) = @_; my $handled = 0; # did we handle the error? print "\n\tSub 'handle_error': $error\n"; if ( $handle_it ) { # if you want to say this error is not an err +or # clear the error $dbh->set_err(undef,undef); # set the new return value $_[2] = { wrong_col => 'bar', correct_col => 'foo' }; # update our handled flag $handled = 1; } else { # we acknowledge it was an error # muck around with $error here and store it in $_[0] # $_[0] is the error message seen by RaiseError/PrintError $_[0] = 'The gremlins are loose again!'; # potentially change the error too $dbh->set_err(1234, $_[0]); } print "\tEnd sub\n\n"; # return our handled flag return $handled; } __END__ Trying HandleError (with RaiseError on): handle it? no Sub 'handle_error': DBD::mysql::db selectall_hashref failed: T +able 'test .test' doesn't exist End sub The gremlins are loose again! at ./database.pl line 24. errstr says: Table 'test.test' doesn't exist [err was 1146 now 1234] The gremlins are loose again! ---------------------------------------- Trying HandleError (with RaiseError on): handle it? yes Sub 'handle_error': DBD::mysql::db selectall_hashref failed: T +able 'test .test' doesn't exist End sub correct_col --> foo wrong_col --> bar
[Tangent] Re: DBI::mysql error handling
by trammell (Priest) on Mar 08, 2005 at 17:48 UTC
    For what it's worth, you should use strict and use warnings:
    my $hash_ref; ... $hashref = $dbh->selectall_hashref($sql, 'filename', ...
      I do, just skipped it to keep the code short.