Re: SQL errors not making it back to Perl

On 08/09/10 21:24, Roode, Eric wrote:
> 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
> 
> 

Works fine for me (although possibly under different conditions):

use DBI;
use strict;

sub handle_error
{
    my ($state, $msg, $native) = @_;
    print qq{handle_error: \$state  is "$state".}, "\n";
    print qq{handle_error: \$msg    is "$msg".}, "\n";
    print qq{handle_error: \$native is "$native".}, "\n";
    return 1;

}
my $dbh = DBI->connect('dbi:ODBC:baugi','sa','easysoft',
                      {odbc_err_handler => \&handle_error});

eval {
   local $dbh->{PrintError} = 0;
   $dbh->do("drop procedure t_raiserror");
};

$dbh->do(<<'EOT');
CREATE PROCEDURE  t_raiserror (@p1 varchar(50), @p2 int output)
AS
set @p2=45;
raiserror ('An error was raised. Input was "%s".', 16, 1, @p1)
return 55
EOT

sub test()
{
   my $sth = $dbh->prepare("{? = call t_raiserror(?,?)}");

   my ($p1, $p2) = ('fred', undef);
   $sth->bind_param_inout(1, \my $retval, 4000);
   $sth->bind_param(2, $p1);
   $sth->bind_param_inout(3, \$p2, 32);
   $sth->execute();

   print qq{After execute: \$retval is $retval.}, "\n";
   print qq{After execute: \$p1     is $p1.}, "\n";
   print qq{After execute: \$p2     is $p2.}, "\n";
}

#$dbh->{odbc_err_handler} = \&handle_error;

test();

$dbh->disconnect;

perl raiserror.pl
handle_error: $state  is "42000".
handle_error: $msg    is "[unixODBC][Easysoft][SQL Server Driver][SQL Server]An error was raised. Input was "fred".".
handle_error: $native is "50000".
DBD::ODBC::st execute failed: [unixODBC][Easysoft][SQL Server Driver][SQL Server]An error was raised. Input was "fred". (SQL-42000) at raiserror.pl line 37.
After execute: $retval is .
After execute: $p1     is fred.
After execute: $p2     is .

I had to take out odbc_cursortype and odbc_default_bind_type as they produced other errors with the driver I was using.

You could send me a level 15 trace and I might see something but it looks like raiserror in your case is not raising an error.

I was using Perl 5.10.1, DBD::ODBC 1.24_3, DBI 1.609 on Ubuntu Linux with the Easysoft SQL Server ODBC Driver.

Martin
-- 
Martin J. Evans
Easysoft Limited
http://www.easysoft.com