× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



I just ran my program twice
Once the parm value was USR01 (record exists for this value)
Once with parm value USS01 (no record exists)
No recompiles, nothing changed just different value in parameter
No messages generated in QSYSOPR

Joblog for value USR01 (record exists for this value)
Unable to retrieve query options file.
PREPARE of statement W$MYSTMT completed.
Unable to retrieve query options file.
**** Starting optimizer debug message for query .
Access path of file SFDFT was used by query.
Arrival sequence access was used for file QSQPTABL.
**** Ending debug message for query .
ODP created.
ODP deleted.
VALUES statement complete.

Worked fine so my SQL statements should be correctly formatted
It retrieves the value I am looking for

Then I get
Joblog for value USS01 (record does not exist for this value)
Which should also be fine, I expect some values will not have a record in
the database file. I just want to continue processing without all these
messages in the joblog and without a dump in QEZJOBLOG

Unable to retrieve query options file.
PREPARE of statement W$MYSTMT completed.
Unable to retrieve query options file.
**** Starting optimizer debug message for query .
Access path of file SFDFT was used by query.
Arrival sequence access was used for file QSQPTABL.
**** Ending debug message for query .
ODP created.
ODP deleted.
VALUES statement complete.
Tried to refer to all or part of an object that no longer exists.
Ownership of object QP0Z236864 in QUSRSYS type *USRSPC changed.
File QAP0ZDMP in library QTEMP already exists.
User Trace data for job 236864/USR01/USR01A dumped to member QP0Z236864 in
file QAP0ZDMP in library QTEMP.
27 records copied from member QP0Z236864.
1 User Trace buffer(s) deleted.
SQL system error.

The message above " Tried to refer to all or part of an object that no
longer exists" I use F1 and get the following (not so helpful help):

Message ID . . . . . . : MCH3402 Severity . . . . . . . : 40

Message type . . . . . : Escape

Date sent . . . . . . : 02/17/10 Time sent . . . . . . : 17:37:54



Message . . . . : Tried to refer to all or part of an object that no
longer
exists.

Cause . . . . . : The most common cause is that a stored address to an

object is no longer correct because that object was deleted or part of the

object was deleted.



In addition to this joblog I get a dump in QEZJOBLOG

5722SS1 V5R1M0 010525 COPY FILE QTEMP/QAP0ZDMP
QP0Z236864 02/17/10 17:37:55 Page
From file . . . . . : QTEMP/QAP0ZDMP Member . . : QP0Z236864
Record format . . . . . : QAP0ZDMP
Record length . . . : 512

To file . . . . . . : *PRINT

RCDNBR *...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5
...+... 6 ...+... 7 ...+... 8 ...+... 9 ...+... 0
1 User Trace Dump for job 236864/USR01/USR01A. Size: 300K,
Wrapped 0 times.
2

3 --- 02/17/2010 17:37:54 ---

4 00000005:940648 SQ Run Time Dump of SQLCA section:

5 00000005:940712 C066512C8A:5C5800 L:0008 EyeCatch:

6 00000005:940760 C066512C8A:5C5800 E2D8D3C3
C1404048 *SQLCA .........*
7 00000005:940792 C066512C8A:5C5808 L:0004 Length of
SQLCA:
5722SS1 V5R1M0 010525 COPY FILE QTEMP/QAP0ZDMP
QP0Z236864 02/17/10 17:37:55 Page
RCDNBR *...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5
...+... 6 ...+... 7 ...+... 8 ...+... 9 ...+... 0
8 00000005:940824 C066512C8A:5C5800
00000088 *...........h....*
9 00000005:940856 C066512C8A:5C580C L:0004 SQL return
code:
10 00000005:940888 C066512C8A:5C5800
FFFFFC7B *...............#*
11 00000005:940920 C066512C8A:5C5810 L:0048 SQL error
message:
12 00000005:940976 C066512C8A:5C5810 000BD4C3
C8F3F4F0 F2000004 01000000 *..MCH3402.......*
13 00000005:941032 C066512C8A:5C5820 00000000
00000000 00000000 00000000 *................*
14 00000005:941080 C066512C8A:5C5830 00000000
00000000 00000000 00000000 *................*
15 00000005:941136 C066512C8A:5C5840 00000000
00000000 00000000 00000000 *................*
5722SS1 V5R1M0 010525 COPY FILE QTEMP/QAP0ZDMP
QP0Z236864 02/17/10 17:37:55 Page
RCDNBR *...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5
...+... 6 ...+... 7 ...+... 8 ...+... 9 ...+... 0
16 00000005:941184 C066512C8A:5C5850 00000000
00000000 *................*
17 00000005:941208 C066512C8A:5C5858 L:0008 Program
name:
18 00000005:941248 C066512C8A:5C5850
D8E2D8D9 D6E4E3C5 *........QSQROUTE*
19 00000005:941280 C066512C8A:5C5860 L:0018 Diag
information:
20 00000005:941336 C066512C8A:5C5860 00000000
00000000 00000000 00000000 *................*
21 00000005:941376 C066512C8A:5C5870 00000000
F7F9F4F9 *....7949........*
22 00000005:941400 C066512C8A:5C5878 L:000B Warning
flag:
23 00000005:941448 C066512C8A:5C5870
E6E64040 40404040 *........WW *
5722SS1 V5R1M0 010525 COPY FILE QTEMP/QAP0ZDMP
QP0Z236864 02/17/10 17:37:55 Page
RCDNBR *...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5
...+... 6 ...+... 7 ...+... 8 ...+... 9 ...+... 0
24 00000005:941488 C066512C8A:5C5880 404040
* .............*
25 00000005:941512 C066512C8A:5C5883 L:0005 SQL state:

26 00000005:941552 C066512C8A:5C5880 F5
F8F0F0F4 *...58004........*

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

58004 is a SQL System error. You need to look at the previous messages in
the job log to see what really happened. Should be a 00200 for a no record
found.

One thing I notice is that you are trying to receive the value into a
dynamic variable. I don't think you can do that.

On Wed, Feb 17, 2010 at 11:54 AM, John Allen <jallen@xxxxxxxxxxx> wrote:

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


--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.