Re: SQL Cursor issues

[Alan Cassidy <cfuture@xxxxxxxxxxx>]

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.