Skip to Content.
Sympa Menu

freetds - [freetds] MS SQL stored proceedure OUTPUT bindings and return codes?

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Martin Bonner" <martin.bonner AT argentas.co.uk>
  • To: <freetds AT lists.ibiblio.org>
  • Subject: [freetds] MS SQL stored proceedure OUTPUT bindings and return codes?
  • Date: Fri, 17 Oct 2003 22:09:18 +0100

Hi,

I'm using freetds-0.62.dev.20031015 with TDS protocol version 7 to connect
to a MSSQL 7.0 SP4 server using Perl and DBD::Sybase v1.01.

I'm trying to gain access to both the return value of a stored proceedure
and bind to its OUTPUT variables without much success.

I don't appear to get a results set for the OUTPUT variables and the results
set of type 4043 (CS_STATUS_RESULTS) always contains a value of '0'
regardless of the value returned by the stored proceedure.

I've included my stored proceedure, my Perl script, and its output below...

Can anyone offer any suggestions as to what I am doing wrong, or whether I
am trying to do something which is not (yet?) supported.

Many Thanks,
Martin.

-----Stored Proceedure-----
create procedure sp_test
@invar int,
@outvar int OUTPUT
AS
BEGIN
SET @outvar = 5
RETURN @invar
END
----- end -----


-----Perl Script-----
#!/usr/bin/perl

use DBI;
use strict;
use Data::Dumper;

my %restype;
$restype{4040}='CS_ROW_RESULT';
$restype{4041}='CS_CURSOR_RESULT';
$restype{4042}='CS_PARAM_RESULT';
$restype{4043}='CS_STATUS_RESULT';
$restype{4044}='CS_MSG_RESULT';
$restype{4045}='CS_COMPUTE_RESULT';

my $dbh = DBI->connect("dbi:Sybase:server=MyServer;database=test", 'sa',
'mypassword', {PrintError => 1});
die "Unable to connect to server $DBI::errstr" unless $dbh;

my $sth=$dbh->prepare("declare \@foo int exec sp_test \@invar = 5, \@outvar
= \@foo OUTPUT");

if($sth->execute()) {
do {
while( my $results=$sth->fetchrow_hashref()) {
printf ("resulttype: %d
(%s)\n",$sth->{syb_result_type},$restype{$sth->{syb_result_type}});
print Dumper $results;
}
} while($sth->{syb_more_results});
}
----- end -----


-----Output from Perl script-----
resulttype: 4043 (CS_STATUS_RESULT)
$VAR1 = {
'COL(1)' => 0
};
----- end-----








Archive powered by MHonArc 2.6.24.

Top of Page