sybperl-l Archive

From: Torsten Bauer <perl at torsten-bauer dot de>
Subject: Re: stored procedure and return values
Date: Oct 13 2005 2:19PM

thanks michael.

i played around a bit today as from my point of view the behavoir isn't 
right - but as you say it can be argued... but what you think about the 
following... i've added a nearly empty err_handler

and then i get my -6 back.. but then i have another funny behavior. when 
i try to print any of the commented prints in the error handler i get a 
core dump - also when i try to execute it with a trace of > 1

here's the script again...

any idea what caused the core dump and why it works with an empty error 
handler?


thanks
torsten

#!/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;
#DBI->trace(2);

my $dbuser = "login";
my $dbpass = "password";

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

$dbh_dst->{syb_err_handler} = \&err_handler;

$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 - $sth_dst->{syb_result_type}\n";
    }
  }
}
while($sth_dst->{syb_more_results});



  sub err_handler
  {
      my($err, $sev, $state, $line, $server,
        $proc, $msg, $sql, $err_type) = @_;
 
      my @msg = ();
     
      print "$err\n";
      print "$sev\n";
      print "$state\n";
      #print "$line\n";
      print "$server\n";
      #print "$proc\n";
      #print "$msg\n";
      #print "$sql\n";
      #print "$err_type\n";
      return 0; ## CS_SUCCEED
  }

michael.peppler@bnpparibas.com schrieb:

>One shouldn't reply from work when you don't take the time to read the
>request completely... I obviously got this wrong the first time around.
>
>Anyway - here's what happens:
>
>When $sth->execute() sees the error it cancels the batch because the result
>set that causes the error isn't a result set that returns any rows.
>You can verify this with a DBI->trace():
>
>    DBI 1.46-ithread default trace level set to 0x0/4 (pid 18035)
>    Note: perl is running without the recommended perl -w option
>    -> execute for DBD::Sybase::st (DBI::st=HASH(0x8200c20)~0x8200c08) thr#804bd00
>    servermsg_cb -> number=208 severity=16 state=1 line=1 server=DBA_SQL procedure=test1 text=#temp not found. Specify owner.objectname or use sp_help
> to check whether the object exists (sp_help may produce lots of output).
>
>    st_next_result() -> ct_results(4043) == 1
>    ct_res_info() returns 1 columns
>    ct_describe(0): type = 8, maxlen = 4
>    describe() -> col 0, type 8, realtype 8
>describe() retcode = 1
>    st_next_result() -> lasterr = 208, lastsev = 16
>    st_next_result() -> restype is not data result or syb_cancel_request_on_error is TRUE, force failFlag
>    st_next_result() -> failFlag set - clear request
>    clear_sth_flags() -> resetting ACTIVE, moreResults, dyn_execed, exec_done
>    clear_sth_flags() -> reset inUse flag
>    st_next_result() -> force CS_CMD_FAIL return
>    st_next_result() -> got CS_CMD_FAIL: resetting ACTIVE, moreResults, dyn_execed, exec_done
>    clear_sth_flags() -> resetting ACTIVE, moreResults, dyn_execed, exec_done
>    clear_sth_flags() -> reset inUse flag
>    !! ERROR: 208 'Server message number=208 severity=16 state=1 line=1 server=DBA_SQL procedure=test1 text=#temp not found. Specify owner.objectname
>or use sp_help to check whether the object exists (sp_help may produce lots of output).
>' (err#0)
>
>All this means that the status code is never returned in this situation.
>
>I suppose it might be argued that this isn't the right behavior...
>
>Michael
>
>
>
>  
>