Re: Embedded SQL Fetch problem

["Nelson C. Smith" <ncsmith@xxxxxxxx>]

I have a procedure that Fetches records from a file using the Declare
Cursor, Open Cursor, Fetch Into a host variable (externally defined ds),
Close Cursor method.  The Declare Cursor is based on a Prepare statement
containing the Select statement with the Where clause containing the key of
the record(s) to fetch.

The first call to this procedure works fine and returns the requested record
in the host variable.  The cursor is then closed and the procedure ends, but
the *srvpgm that the procedure is in stays active in it's own activation
group.  On the second call to this procedure, passing a different set of
keys, The new cursor is declared, opened, and the new fetch is done to the
host variable.  This time the host variable is empty.

Immediately after the fetch, the SQLCOD and SQLSTT are normal, SQLER3 = 1,
SQLER4 = 259 (the correct size of the record), and SQLER5= 100 (last record
returned).  The message "1 rows fetched from cursor xxxx" is written to the
joblog.  Yet, no data is in the host variable (the same host variable that
works fine on the first call).

Has anyone seen this before?  I'm suspecting it has something to do with the
position of the cursor, since it has been opened and closed once before, and
is now being opened again.  Is there something equivalent to a SETLL that I
need to do? Even so, why no errors? And why the normal SQLCA stating that
one record was fetched?

+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---END