Re: Trapping Stored Procedure Error

On 5/15/2015 9:04 AM, Charles Wilt wrote:
> I would expect the Java program to see the error.  Unless either the Java
> program or the RPGLE/CL is submitting a new job.

I'm hardly the expert on this stuff but my personal experience is like
Gad's.  Say I have an RPG program with no MONITOR, *PSSR, etc.  When an
error occurs - like substring out of range - the default error handler
gets control.  At this point, the RPG program is essentially suspended,
because the default error handler is probably displaying a message to
*EXT - QSYSOPR, no doubt.  Until that message is answered, RPG doesn't
get control, and until RPG gets control, the database manager doesn't
get control and until that happens, JDBC thinks the stored procedure is
running a long time.

> On Fri, May 15, 2015 at 8:57 AM, Gad Miron <gadmiron@xxxxxxxxx> wrote:
> 
>> Hello guys
>>
>> We have a Java-Windows application that uploads text files (one at a time)
>> to the iSeries/System i/AS400 machine and then calls a Stored Procedure to
>> process
>> the file uploaded.
>> The SP calls an RPG program that in turn conditionally call other RPG/CL
>> programs.
>> all of the above is executed in a QZDASOINIT job (spawned especially?)
>>
>> Sometimes one of the RPG/CL programs in the process "bombs" (with a CPFXXX)
>> but the guy using th Java application is not aware of it, it is the SYSOPR
>> actually that
>> sees the MSGW status of the QZDASOINIT job and calls a programmer to look
>> at it.
>>
>> Now,
>> Is there a way to trap the errors and inform the Java application user that
>> there was an error ?
>> This* without* inserting MONITOR lines in the RPG programs and manually
>> returning
>> some kind of a return code?

The Java program is almost certainly using JDBC to connect to DB2, and
executing an SQL statement like CALL MYPROC();  The way that SQL informs
a program about errors is through SQLSTATE.  My RPG based stored
procedures all use MONITOR to properly set SQLSTATE in the event of an
unexpected error.  This way I can also gather diagnostics [say DUMP(A)]
and log the event on my side of the transaction if I want to.  The point
is that I never, ever want an RPG stored procedure to throw a white message.

>> The Java programmer tell me she can trap an error but an error is not
>> received.
>> (perhaps it is another QZDASOINIT job? )
>>
>> Any advice will be appreciated

Again, I am not the expert, but this is the way I do it.  MONITOR and
set SQLSTATE.

>> Regards
>> Gad

  --buck