PEPPLER.ORG
Michael Peppler
Sybase Consulting
Menu
Home
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
Freeware
Sybperl
Sybase::Simple
DBD::Sybase
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
FAQs
Sybase on Linux FAQ
Sybperl FAQ
Personal
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: Charles_Mire at Concentra dot com
Subject: explicit stored procedure return codes from Sybase
Date: Jun 3 2005 8:13PM

Somehow the code works now.  Here is the working code and I get the
explicit return value from the stored procedure (not just the 1 or 0):

my $dbh = DBI->connect( "dbi:Sybase:server=$dbServer", $username,
$password,
                         {PrintError=>0, RaiseError=>1});


if( !( $dbh ) )
  {
     sub dbcheck { WARN("Cannot connect to $dbServer!"); }
     dbcheck();
  }
else
  {
        sub dbcheck { INFO("DB server connection is good!"); }

        dbcheck();


        $dbh->do("use $database");

        $sth=$dbh->prepare("exec $stored_proc");
        $sth->execute;
        do {
                while(my $rows=$sth->fetch) {
                        print "@$rows\n";
                }
        } while ($sth->{syb_more_results});


        $sth->finish;
  }
$dbh->disconnect;

Charles Mire
Programmer Analyst
Concentra
ph. 972.364.8132
----- Forwarded by Charles Mire/Dallas/HS/Concentra on 06/03/2005 03:11 PM
-----
|---------+---------------------------------->
|         |                   Charles Mire   |
|         |                   06/03/2005     |
|         |           12:48 PM               |
|         |                                  |
|---------+---------------------------------->
  >------------------------------------------------------------------------------------------------------------------------|
  |                                                                                                                        |
  |    To:            sybperl-l@peppler.org                                                                                |
  |    cc:            (bcc: Charles Mire/Dallas/HS/Concentra)                                                              |
  |    Subject:       explicit stored procedure return codes from Sybase                                                   |
  >------------------------------------------------------------------------------------------------------------------------|



I am trying to be able to capture miscellaneous explicitely-declared return
codes from Sybase stored procedures using DBI.  What I mean, is when a
stored procedure ends with a statement like "return 12345" -- I want to be
able to capture "12345".

Currently with DBI, using $sth->execute, I am only able to get 0 or 1 if
the stored procedure actually executed properly or not -- not the "12345" I
am looking for.

Here is my code:

my $dbh = DBI->connect( "dbi:Sybase:server=$dbServer", $username,
$password,
                         {PrintError=>0});


if( !( $dbh ) )
  {
     sub dbcheck { WARN("Cannot connect to $dbServer!"); }
     dbcheck();
  }
else
  {
        sub dbcheck { INFO("DB server connection is good!"); }

        dbcheck();
        $dbh->do("use $database");
        $sth=$dbh->prepare("exec $stored_proc");
        $sth->execute;
        do {
                while(my $data = $sth->fetch) {
                print "@$data\n";
                }
        } while($sth->{syb_more_results});
        $sth->finish;
  }
$dbh->disconnect;


The do loop returns nothing to me -- even though I am executing a dummy
stored procedure that does nothing but "return 12345".

I tried setting $sth->{syb_do_proc_status}=1 and capturing
syb_do_proc_status, but this puts me back to my original dilemma: I don't
care about the 0 that it executed properly -- I want the explicit return
code from within the procedure.

I have also tried modifying my prepare statement to this:

        $sth=$dbh->prepare("
                declare @rvalue int
                exec $stored_proc, @rv = @rvalue OUTPUT");

and then doing print "@rv\n"; -- but got nothing there, either.

I've googled on this for the last couple of days and found nothing to help
me with this.

Executing this stored procedure from an isql command line gives me "12345".

Thanks in advance for help!


Charles Mire
Programmer Analyst
Concentra
ph. 972.364.8132


                    ****** CONFIDENTIALITY NOTICE ******
NOTICE:  This e-mail message and all attachments transmitted with it may
contain legally privileged and confidential information intended solely for
the use of the addressee.  If the reader of this message is not the
intended recipient, you are hereby notified that any reading,
dissemination, distribution, copying, or other use of this message or its
attachments is strictly prohibited.  If you have received this message in
error, please notify the sender immediately and delete this message from
your system.  Thank you.



****** CONFIDENTIALITY NOTICE ******
NOTICE: This e-mail message and all attachments transmitted with it may contain legally privileged and confidential information intended solely for the use of the addressee. If the reader of this message is not the intended recipient, you are hereby notified that any reading, dissemination, distribution, copying, or other use of this message or its attachments is strictly prohibited. If you have received this message in error, please notify the sender immediately and delete this message from your system. Thank you.