PERL Miscellaneous - Using DBI to retrieve exit code of stored procedure

Author

Using DBI to retrieve exit code of stored procedure

Domenico Discepola

2004-05-18, 4:32 pm

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

2004-05-20, 11:34 am

Domenico Discepola wrote:
> 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::Pg 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.)

--
(Posted from an account used as a SPAM dump. If you really want to get
in touch with me, dump the 'jboes' and substitute 'mur'.)
________
Jeffery Boes <>< jboes@qtm.net

Sponsored Links