sybperl-l Archive

From: Michael Peppler <mpeppler at MBAY dot NET>
Subject: getting status from multiple-statement transaction
Date: Feb 9 1999 2:40PM

>>>>> "Steve" == Wechsler, Steven M  writes:

Steve> If I understand it correctly, with DB-Library,
Steve> $dbh->dbretstatus will return the exit status of a stored
Steve> procedure.

Correct.

Steve> However, what happens if a multiple-statement batch is sent as
Steve> one transaction, e.g.:

Steve> exec sp_firstproc exec sp_secondproc go

Steve> How can I determine the exit status of the sp_firstproc
Steve> statement?  According to the docs, you can only call
Steve> dbhasretstat after dbresults returns NO_MORE_RESULTS, but, to
Steve> my understanding, this will occur *only* after all results have
Steve> been processed.

I re-read the Sybase docs for DB-Library/C (always the point of
reference if in doubt) and the documentation is a little clearer: You
must call dbhasretstat() and dbretstatus() after all *rows* from the
stored proc have been processed. You can call it *before* you call
dbnumrets() and friends to retrieve any OUTPUT parameters. I would
guess that you could write something like this:

while(dbresults != NO_MORE_RESULTS) {
    while(@data = dbnextrow) {
    ...
    }
    if(dbhasretstat()) {
       $status = dbretstatus();
    }
}

This *may* not do the right thing if any of your stored procs include
multiple SELECT statements, but I think that it should work fine.

This is a case where Client Library is a lot better architected...

Michael
-- 
Michael Peppler         -||-  Data Migrations Inc.
mpeppler@mbay.net       -||-  http://www.mbay.net/~mpeppler
Int. Sybase User Group  -||-  http://www.isug.com
Sybase on Linux mailing list: ase-linux-list@isug.com