RE: Embedded SQL no record retrieved error handling

If you do a WRKPRB, you'll see an entry that corresponds to these job logs (you will also see a message that a problem was reported if you look at QSYSOPR messages). This is something you'll need to call IBM on to get resolved.

Matt

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of John Allen
Sent: Wednesday, February 17, 2010 2:03 PM
To: 'RPG programming on the IBM i / System i'
Subject: RE: Embedded SQL no record retrieved error handling

I just noticed when no record is retrieved, I get a joblog dump for each
record not retrieved and I get messages in my joblog.
I know that is not going to be acceptable in the long run.
So there must be a way to inform SQL that no record retrieved is OK, don't
give me a program dump.

Correct?

Joblog shows the following every time a record can not be retrieved:

Tried to refer to all or part of an object that no longer exists.
Ownership of object QP0Z236860 in QUSRSYS type *USRSPC changed.
File QAP0ZDMP in library QTEMP already exists.
User Trace data for job 236860/USR01/USR01B dumped to member QP0Z236860 in
file QAP0ZDMP in library QTEMP.
27 records copied from member QP0Z236860.
1 User Trace buffer(s) deleted.
SQL system error.

Thanks
John


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of John Allen
Sent: Wednesday, February 17, 2010 1:54 PM
To: RPG400-L@xxxxxxxxxxxx
Subject: Embedded SQL no record retrieved error handling

I am new to using embedded SQL, and am trying to learn it in baby steps.
I did get my first program with embedded RPG to work fine. It retrieves a
single value from a database record in a physical file.
Compiled, works fine as long as the record is found.

If the record is not found I get error: SQLSTT = 58004 SQLCOD = -901

Is it normal to get these when no record is retrieved by the SQL
And I just need to monitor for them and program accordingly?
It will be normal that the record may not exist and I do have specific code
for no record found conditions.
I just want to make sure I am handling this type of error correctly.

My test Code snipet:

C eval W$SQLCmd = 'Values ( Select '+ P$FLDNM
C + ' from ' + P$FILNM
C + ' Where ' + W$KEYFLD + '=?'
C + ') into ?'
*
C/exec SQL
C+ Prepare W$MYSTMT from :W$SQLCmd
C/End-Exec
*
C/exec SQL
C+ execute W$MYSTMT Using :P$KEYVALUE, :P$RTNVALUE
C/End-Exec

*
C eval W$WrkDsp = 'SQLSTT = ' + SQLSTT
C + ' SQLCOD = '
C + %char(SQLCOD)
C W$Wrkdsp dsply


Thanks
John