FreeTDS Development Group

["Orlando Colamatteo" <Ocolamatteo AT affinitygroup.com>]

I am using DBD::ODBC to call MS SQL Server stored procedures and am
having issues capturing return codes whenever an error is raised inside
the procedure with error severity level >= 11. Generically speaking my
proc is coded like this:

Create Proc dbo.raise_and_return_code
As
Begin
    RAISERROR('An error occurred in proc
dbo.raise_and_return_code',11,1)
    RETURN 1
END

Error message is available in $DBI::errstr however I cannot capture the
return code. It seems like the DBI connection is terminated as soon as
the raiserror is encountered making it impossible to get any return code
back from the database.

However removing the raiserror from the proc as follows allows me to
capture the return code:

Create Proc dbo.return_code_only
As
Begin
    RETURN 1
END

Has anyone experienced this problem? I have a hunch that this might be a
DBD::ODBC or DBI issue however I thought I would start here. I need for
calls to my database procs to continue to completion even if an error
occurs so I can get back the return code. It would be ideal if a
callback was made whenever an error was encountered so that I could log
the error details as they occurred, however I would settle for the
connection turning a blind eye to intermediate errors and simply give me
back a return code when the proc completes.

Here is my Perl code:

use strict;
use DBI;

sub err_handler {
   my ($state, $msg, $native_error) = @_;
   # Strip out all of the driver ID stuff
   $msg =~ s/^(\[[\w\s]*\])+//;
   print "===> \n\tstate: $state\n\tmsg: $msg\n\tnative error:
$native_error\n";
}


my $dbh = DBI->connect("dbi:ODBC:agipps01-sybdev2", "UserNoMatter",
"PassNoMatter", { RaiseError => 0, PrintError => 0 } )
       || die "Can't connect: $DBI::errstr\n";

$dbh->{odbc_err_handler} = \&err_handler;
$dbh->{odbc_async_exec} = 1;

my $rc;

my $sth;
$sth = $dbh->prepare("{? = call return_code_only}") || die $dbh->errstr;

# Bind value for the first placeholder. Because the prepared statement
# updates the bind value, bind_param_inout rather than bind_param needs
to be used.
my $output;
$rc = $sth->bind_param_inout(1, \$output, 1);

$rc = $sth->execute;

print "===> \n\treturn code: \"$output\"";
print "\n\t\$DBI::errstr: $DBI::errstr";
print "\n\t\$DBI::err: $DBI::err";
print "\n\t\$DBI::state: $DBI::state";

$rc = $sth->finish;
$rc = $dbh->disconnect;




When I run this exact code I get:

===> 
        return code: "1"
        $DBI::errstr: 
        $DBI::err: 
        $DBI::state: 



I got a return code, good. Now if I change the prepare to call
raise_and_return_code instead of return_code_only I get:

===> 
        state: 42000
        msg: An error occurred in proc dbo.raise_and_return_code
        native error: 50000
===> 
        return code: ""
        $DBI::errstr: [Microsoft][SQL Native Client][SQL Server]An error
occurred in proc dbo.raise_and_return_code (SQL-42000)(DBD:
st_execute/SQLExecute err=-1)
        $DBI::err: -1
        $DBI::state: 42000

See, it captured the error details as seen from the first display block
output by the err_handler, however no return code. I have tried playing
with the DBI attribute RaiseError & PrintError, as well as with the
odbc_async_exec & odbc_err_handler properties to see if one of those was
interefering, but with no luck. Has anyone encourntered this issue? Is
there a solution?

--opc3