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
