Using DBI to retrieve exit code of stored procedure

[Domenico Discepola]

Hello all. I'm using DBI to connect via ODBC to Microsoft SQL Server 2000.
I wish to execute a stored procedure and return the exit code of the stored
procedure. The stored procedure only returns 1 numerical value. In this
stored proc, I'm calling gzip with a non-existent file in order to produce
an error. I do not get any results back. Any thoughts?

Stored procedure:
create procedure sp_test1 as
DECLARE @result int
EXEC @result = master..xp_cmdshell 'gzip h', no_output
return @result

Perl code:
#!perl
use strict;
use warnings;
use DBI;
our ( $g_dbhandle, $g_dsn );
$g_dsn = 'bcp';

sub main {
my ( $stmt, $sth, $res );
$g_dbhandle = DBI->connect("DBI:ODBC:${g_dsn}") || die
"$g_dbhandle->errstr\n\n$!\n";
$stmt = "exec northwind..sp_test1";
$sth->prepare('{? = call northwind..sp_test1}');
$sth->bind_param_inout( 1, \$res, 50 ); #code I got from somewhere
$sth->execute or die $g_dbhandle->errstr;
print "$res\n";
$sth->finish;
$g_dbhandle->disconnect or die $g_dbhandle->errstr;

}

exit 0;

 

[Jeff Boes]

Hello all. I'm using DBI to connect via ODBC to Microsoft SQL Server 2000.
I wish to execute a stored procedure and return the exit code of the stored
procedure. The stored procedure only returns 1 numerical value. In this
stored proc, I'm calling gzip with a non-existent file in order to produce
an error. I do not get any results back. Any thoughts?

Hmm ... well, I'm far more familiar with the DBD:g world than SQL
Server, but in *my* world you would retrieve it using something like --


SELECT sp_test();


I don't know if that's SQL-standard, so you may have to do --

SELECT sp_test() FROM dual;

(That's old Oracle background creeping in.)