sybperl-l Archive

From: Torsten Bauer <perl at torsten-bauer dot de>
Subject: stored procedure and return values
Date: Oct 12 2005 1:09PM

hi all

i have some trouble to get the return value from a stored procedure.. 
hopefully someone of you is able to help me. the procedure i used for 
testing:


create table #test
(
 test int
)
go
create proc proctest
as
begin
  select * from #test
end
go
drop table #test
go
exec proctest


when i run the procedure proctest with isql the return status is -6.

i try to catch this in perl but i can't get it.. i used different ways 
and this is the latest one - based on the documentation:

#!/usr/bin/perl

$ENV{SYBASE}="/apps/sybase/current";
$ENV{SYBASE_SYSAM}="SYSAM-1_0";
$ENV{SYBASE_FTS}="EFTS-12_5";
$ENV{SYBASE_JRE}="/apps/sybase/current/shared-1_0/jre1.2.2";
$ENV{SYBASE_ASE}="ASE-12_5";
$ENV{SYBASE_OCS}="OCS-12_5";

use DBI;
use DBD::Sybase;

my $dbuser = "user";
my $dbpass = "password";
                       
$dbh_dst = DBI->connect("dbi:Sybase:server=SERVER",  
                          $dbuser, $dbpass, {
                                   RaiseError => 0,
                                   PrintError => 1,
                                   AutoCommit => 1
                                 })        or die "Can't connect to 
database: ", $DBI::errstr, "\n";


$sth_dst = $dbh_dst->prepare("exec dwh_trash..proctest") or die "ERR";
   
$sth_dst->execute();# or die "ERR";

do
{
  while($data = $sth_dst->fetch)
  {
    if($sth_dst->{syb_result_type} == CS_STATUS_RESULT)
    {
      $status = $data->[0];
      print "if @$data\n";
    }
    else
    {
      print "else @$data\n";
    }
  }
}
while($sth_dst->{syb_more_results});
      
                  
print "end\n";


it seems that i don't get any data for the result type CS_STATUS_RESULT 
as the output is only:

./test.pl
DBD::Sybase::st execute failed: Server message number=208 severity=16 
state=1 line=4 server=SERVER procedure=proctest text=#test not found. 
Specify owner.objectname or use sp_help to check whether the object 
exists (sp_help may produce lots of output).
end of proc


has someone an idea what i do wrong? it seems i'm going mad..


thanks a lot in advance

torsten