SQL errors not making it back to Perl

[Roode, Eric]

Hello all,

    I am trying to implement error handling in our stored procedures,
but when I raise exceptions in the SQL code, they seem to disappear on
their way back to the Perl app.  I've searched the archives, but
haven't found an answer.

    I'm using DBI 1.609 with DBD::ODBC 1.23 under strawberry Perl 5.12
on Windows 7, against a SQL Server 2000 database elsewhere on our LAN.

I've created a simple sproc to generate an error, as follows:

   create procedure error_test (@p1 varchar(50), @p2 int output)
   as
      set @p2=45;
      raiserror ('An error was raised. Input was "%s".', 16, 1, @p1)
      return 55
   GO

Here is a simple Perl program to invoke it:

   use Modern::Perl;
   use DBI;
   
   sub handle_error
   {
       my ($state, $msg, $native) = @_;
       say qq{handle_error: \$state  is "$state".};
       say qq{handle_error: \$msg    is "$msg".};
       say qq{handle_error: \$native is "$native".};
       return 1;
   }
   
   my $dbh = DBI->connect('DBI:ODBC:driver={SQL
Server};Server=xxxxx;Database=xxxxx,
                          'xxxxx', 'xxxxx',
                          {odbc_cursortype        => 2,
                           odbc_default_bind_type => DBI::SQL_VARCHAR,
                           odbc_err_handler       => \&handle_error,
                           RaiseError             => 1,
                           PrintError             => 0,
                          });
   
   my $sth = $dbh->prepare('{? = call error_test(?, ?)}');
   
   # Set up parameters
   my $retval;
   my $p1 = 'Some input parameter';
   my $p2 = 75;
   $sth->bind_param_inout(1, \$retval, 4000, DBI::SQL_VARCHAR);
   $sth->bind_param      (2,  $p1,           DBI::SQL_VARCHAR);
   $sth->bind_param_inout(3, \$p2,       32, DBI::SQL_INTEGER);

   # Execute   
   $sth->execute;

   # Show what we got
   $_ = defined($_)? qq{"$_"} : '{NULL}' for ($retval, $p1, $p2);
   say qq{After execute: \$retval is $retval.};
   say qq{After execute: \$p1     is $p1.};
   say qq{After execute: \$p2     is $p2.};
   say 'Done.';


Sadly, handle_error never gets called.  The output of the program is:

   After execute: $retval is {NULL}.
   After execute: $p1     is "Some input parameter".
   After execute: $p2     is "75".
   Done.

On the other hand, if I do something like try to call a nonexistent
stored procedure, handle_error does get invoked, and the output looks
like:
   handle_error: $state  is "42000".
   handle_error: $msg    is "[Microsoft][ODBC SQL Server Driver][SQL
Server]Could not find stored procedure 'blefh_error_test'.".
   handle_error: $native is "2812".


What do I have to do to detect the SQL exception?

Thanks,
-- Eric