More details:
Procedure results from DB prompt:
[23] DEV03.dfirm002.1> declare @ret_val int, @output int
[23] DEV03.dfirm002.2> exec @ret_val = prcLoadTransacMove 2012, sm3, 17,
1775162844, 2, @output out
[23] DEV03.dfirm002.3> select @ret_val, @output;
(return status = 0)
-----------
0
(1 row affected)
----------- -----------
0 0
(1 row affected)
Regards,
Kavita :-)
---------- Forwarded message ----------
From: kavita kulkarni <[email protected]>
Date: Wed, Jan 16, 2013 at 7:18 PM
Subject: Receiving Output parameters of a sybase stored procedure
To: [email protected]
Hello All,
I want to call a sybase stored procedure from my perl script & store the
output parameter of script in a variable.
Script takes 5 input parameters & has one int type output prameter.
Below is part of my script.
Everytime I run this, I only get output as retVal = 0 (which is return
value of stored procedure) but I cannot get the output parameter of a
stored procedure. (I get output values when I ececute procedure from DB
prompt)
Can somebody let me know what am I missing?
my $sql = <<EOF;
exec prcLoadTransacMove \@TaxYear=?,\@ClientId=?, \@beginRecId=?,
\@endRecId=?, \@UI_Call=?, \@RecCount out
EOF
my $sth = $dbh->prepare("$sql") or print "Cannot prepare $sql:
$DBI::errstr\n";
print "Procedure sql prepared: $sql \n";
print "prcLoadTransacMove $TaxYear, $ClientId, $MinRecId, $MaxRecId,
$UiCall\n";
$sth->bind_param(1, $TaxYear, SQL_INTEGER);
$sth->bind_param(2, $ClientId, SQL_CHAR);
$sth->bind_param(3, $MinRecId, SQL_INTEGER);
$sth->bind_param(4, $MaxRecId, SQL_INTEGER);
$sth->bind_param(5, $UiCall, SQL_INTEGER);
$sth->execute() or print "Can not execute $sql in with params ==
taxYear:$TaxYear clientId:$ClientId MinRecId:$MinRecId MaxRecId:$MaxRecId
UiCall:$UiCall, $DBI::errstr\n";
{
my ($data, $retVal);
while ($data = $sth->fetch) {
if ($sth->{syb_result_type} == CS_PARAM_RESULT) { # it's a
PARAM result
my $ProcessNum = $data->[0];
print "ProcessNum = $ProcessNum\n";
} elsif ($sth->{syb_result_type} == CS_STATUS_RESULT) { #
it's a procedure STATUS result
$retVal = $data->[0];
print "retVal = $retVal\n";
print ("prcProcessRunCore retVal=$retVal") if
($retVal != 0);
} else {
print ("prcProcessRunCore output: " . join("|",
@$data));
}
}
redo if ($sth->{syb_more_results});
}
$sth->finish;
Thanks in advance.
Regards,
Kavita :-)
