Re: SQL Cursor issues -- RPG400-L

Cleaning up old emails, I came across this thread and it looks like I didn't send a "final report" on this.

I did apply Birgitte Hauser''s suggestion of executing a "Close M1" statement /before /the first Open. I don't remember if that was what fixed the issue even though I was closing it after the first open.

That's all I can think of right now. In any case the issue cleared up.

--Alan Cassidy

         exec sql Declare M1 SENSITIVE DYNAMIC SCROLL CURSOR for
         SELECT * FROM TSTALANC.CS58V003
           WHERE :PASCCHCE = 2 AND
                 :PASCNAME IN (CSCAS, CSNTCAS, CS44CAS)
              OR :PASCCHCE = 3 AND
                 :PASCNAME IN (CSCAS, CSNTCAS, CS44CAS)
              OR :PASCCHCE IN (0,1) AND :@@USER = CSUSER AND
                 :SVKEY < MAINKEY
           ORDER BY CS15X3AD, CS15PUDT, CS15OT, CS15PRO ;
           //
         exec sql Open M1 ;*

         exec sql
             Fetch First from M1 for :RowsToFetch rows into :MX ;*

         exec sql Close M1; *

On 6/17/2022 11:03 AM, Michael Quigley wrote:

I've been following this thread and it's puzzling because SQLCode 100 should not be possible on an OPEN. So my question is, are you checking the SQLCode after the CLOSE? The code you've posted doesn't show that happening. If there's an error closing the cursor, all bets are off for any future operations. As others have said, check the SQLCode after every operation (which will not include the DECLARE as nothing is executed by a DECLARE).

Michael Quigley
Computer Services
The Way international
www.TheWay.org

-----Original Message-----
message: 4
date: Thu, 16 Jun 2022 11:27:01 -0400
from: Alan Cassidy<cfuture@xxxxxxxxxxx>
subject: Re: SQL Cursor issues

Here you are Daniel. The code itself.? (Good quote from Linus Torvalds).
As you can see, it is based on an SQL View object. That is becaue the data is
constantly changing throughout the day as they work on it. It's a Load-all
Fetch, which will be just today's work....

To declare and open the cursor:

*???????? exec sql Declare M1 SENSITIVE DYNAMIC SCROLL CURSOR for
???????? SELECT * FROM TSTALANC.CS58V003 ?????????? WHERE
:PASCCHCE = 2 AND ???????????????? :PASCNAME IN (CSCAS, CSNTCAS,
CS44CAS) ????????????? OR :PASCCHCE = 3 AND ????????????????
:PASCNAME IN (CSCAS, CSNTCAS, CS44CAS) ????????????? OR :PASCCHCE
IN (0,1) AND :@@USER = CSUSER AND ???????????????? :SVKEY < MAINKEY
?????????? ORDER BY CS15X3AD, CS15PUDT, CS15OT, CS15PRO ; ??????????
// ???????? exec sql Open M1 ;*

Then the fetch which is set to do a multi-row fetch, and the 5000 will more
than cover the numbers of rows for fetching well into the future (This is data
for only one day's work). And if they ever need more than
5000 in a subfile, that's enough to work on all day, and they can always hit F5
to refresh to get more (by reopening the cursor and fetching again)

The fetch:

*???????? exec sql**
**?????????? Fetch First from M1 for :RowsToFetch rows into :MX ;*

And after that fetch:

*exec sql Close M1; *

That's it. The program loads the subfile from the data-structure array MX and
then processes each of them in a FOR-loop up to the number of
RowsFetched.