Stored Procedure Error Checking

Shaun Lombard

Stored Procedure Error Checking
I know this is an old thread, but has someone figured out a way to
handle errors that occur in a stored procedure that is initiated by a
CALL from a trigger ?

Shaun
-----Original Message----- From: Lockwood Lyon
[mailto:[login to unmask email] Sent: Wednesday, July 24, 2002 2:02 PM
To: [login to unmask email] Subject: Re: Stored Procedure Error
Checking
Regrettably, our environment seems to be much 'dirtier' than this.
We have Stored Procedures being invoked from Web-based applications.
When I queried the developers about checking the SQLCODE (and other
fields in the SQLCA), the answer was, "What's the SQLCA? We invoke the
SP by <insert object-oriented jargon here> ". Something about a
COM-object. Guess I'm old-fashioned, but I got totally lost in the
explanation.
At any rate, the robust diagnostics we're used to getting
(SQLCODE/SQLSTATE, SQLERRM, etc) are either:
1) Not available in this envonment; or B) Available, but not all of
them; or iii) All available, but developers don't know how to find /
interpret / use them.
I suspect reason (iii) to be the case in our environment. :(
My opinion: EVERY stored procedure should have defined an OUT parameter
passing back Complete Status and Error Diagnostics, including the last
SQLCA.
- Lockwood Lyon Meijer Stores
>>> [login to unmask email] 07/24/02 02:08PM >>> William,
In general, one must remember to check two different error conditions
after calling a stored procedure: 1. Did the DB2 Execute the Stored
Procedure OK 2. Did the Stored Procedure encounter any problems.
For the first, just check SQLCODE as normal. For the second, have the
Stored Procedure pass back an error code as an output parameter, and
check that.
IF SQLCODE NOT EQUAL TO 0 THEN PERFORM SQL_ERROR_HANDLING_PARA ELSE IF
SP_OUT_CODE NOT EQUAL TO 0 THEN PERFORM EXCEPTION_PROCESSING_PARA ELSE
HANDLE SUCCESS ENDIF ENDIF
Hope this helps,
Tink --- William Poston <[login to unmask email]> wrote: > Can some one
send me an example of the error > checking there COBOL programs are
doing after > returning > from a Stored Procedure call? > > Thanks >




Notice:
The information contained in this e-mail message and any attached files may
be confidential information, and may also be the subject of legal
professional privilege. If you are not the intended recipient any use,
disclosure or copying of this e-mail is unauthorised. If you have received
this e-mail in error, please notify the sender immediately by reply e-mail
and delete all copies of this transmission together with any attachments.