I had a bit of weirdness the other day with DBI and error handling. Basically I have a number of stored procedures that throw certain user defined error messages. Well it seems that these errors cannot be captured using DBI's inline error return. By this I mean that

my $rc = $sth->execute()
         or die "MYERROR: Can't execute statement: $DBI::errstr";
[download]

will never die, regardless of what happened in the error. I mentioned this in the CB the other day and mpeppler suggested I post it as a SOPW. So here goes. First the relevent SQL

/* define the message, drop it first 
   just in case we are actually changing it. */
sp_dropmessage 9999000
go
sp_addmessage 9999000, "Throwing error as @throw='%1!'"
go

/* define the stored proc, drop it first 
   just in case we are actually changing it. */
drop proc check_error_return
go
create proc check_error_return
  @throw numeric(1)=null
as
  if (@throw is null) begin
    select name from sysobjects where type='P'
  end else begin
    raiserror 9999000,@throw
    return -1
  end
go
[download]

As you can see this stored proc accepts an optional parameter. If the parameter is not null then it throws an error telling us the value of the parameter. If it is null it returns a list of the names of all the stored procedure in the current DB.

Now heres the perl code:

use strict;
use warnings;
use DESD::DBI_Utils qw(open_db_connection);
$|++;

use constant DATABASE => 'SOMEDB';

print "Establishing connection to DB ".DATABASE()."\n";
my $dbh = open_db_connection( 'me', 'password', server => 'MYSERVER', 
+database => DATABASE );

for my $raiserror (0,1) {
    for my $printerror (0,1) {
        $dbh->{RaiseError}=$raiserror;
        $dbh->{PrintError}=$printerror;

        for my $val qw(1 null) {
            eval {
                print "RaiseError=$raiserror PrintError=$printerror Va
+lue=$val\n";
                my $sth=$dbh->prepare("exec check_error_return \@throw
+=$val")
                           or die "MYERROR: Can't prepare statement: $
+DBI::errstr";

                my $rc = $sth->execute()
                      or die "MYERROR: Can't execute statement: $DBI::
+errstr";

                print "Got:";
                my $ret;
                while (($ret) = $sth->fetchrow_array) {
                   print "$ret\t";
                }
                print "\n";
                # check for problems which may have terminated the fet
+ch early
                die "MYERROR:".$sth->errstr if $sth->err;
            };
            if ($@) {
                if ($@=~/MYERROR/) {
                    print "Got an inline error:\n$@";
                } else {
                    print "Got an exception:\n$@";
                }
            } else {
                print "No error\n";
            }
        }
    }
}
[download]

Open DB connection is just a little procedure we use around here for ensuring the right driver is used for the right server etc. Effectively all it does is open the connection and return the handle with some exta error handling etc.

Here is the output (marginally edited for privacy reasons)

Establishing connection to DB SOMEDB
RaiseError=0 PrintError=0 Value=1
Got:-1
No error
RaiseError=0 PrintError=0 Value=null
Got:check_error_return
No error
RaiseError=0 PrintError=1 Value=1
DBD::Sybase::st execute failed: Server message number=9999000 severity
+=16 state=1 line=7 procedure=check_error_return text=Throwing error a
+s @throw='1' at D:\Development\rating\throw_error.pl line 43.
Got:-1
No error
RaiseError=0 PrintError=1 Value=null
Got:check_error_return
No error
RaiseError=1 PrintError=0 Value=1
Got an exception:
DBD::Sybase::st execute failed: Server message number=9999000 severity
+=16 state=1 line=7 procedure=check_error_return text=Throwing error a
+s @throw='1' at D:\Development\rating\throw_error.pl line 43.
RaiseError=1 PrintError=0 Value=null
Got:check_error_return
No error
RaiseError=1 PrintError=1 Value=1
DBD::Sybase::st execute failed: Server message number=9999000 severity
+=16 state=1 line=7 procedure=check_error_return text=Throwing error a
+s @throw='1' at D:\Development\rating\throw_error.pl line 43.
Got an exception:
DBD::Sybase::st execute failed: Server message number=9999000 severity
+=16 state=1 line=7 procedure=check_error_return text=Throwing error a
+s @throw='1' at D:\Development\rating\throw_error.pl line 43.
RaiseError=1 PrintError=1 Value=null
Got:check_error_return
No error
[download]

At no point does the inline error handling mecahnism fire. I'd love to know to write a stored proc that would cause the inline handling to fire. I know I can work around this by creating my own error handlers, but I would much prefer to use inline handling. Ideas are welcome.

Cheers all,