Re: SQL Cursor issues

[Alan Cassidy <cfuture@xxxxxxxxxxx>]

Thank you,  Daniel.

After I posted the earlier conundrum, I  checked SQLCODE debug while 
testing. The first time the program runs, like I said runs okay. I close 
the cursor with "EXEC SQL CLOSE M1" after populating the subfile.

When the code comes thru again to open the cursor, the OPEN CURSOR 
returns SQLCODE = 100, the end-of-data value. That is a good clue that 
the end-of-data flag lingers from the previous open even after closing 
the cursor.

So my new question is, how to make sure that flag is reset after closing 
the cursor?

--Alan


On 6/11/2022 5:32 PM, Daniel Gross wrote:
> Hi Alan,
>
> have you SQLCODE checks after all EXEC SQL statements?
>
> Especially after the EXEC SQL OPEN statement, because if the open fails, the fetch will also "fail" or return no rows.
>
> Often it is an "cursor not closed" issue - so try to debug all SQL statements and find out, if you have any SQL errors.
>
> HTH and kind regards,
> Daniel
>
>
> > Am 11.06.2022 um 21:13 schrieb Alan Cassidy <cfuture@xxxxxxxxxxx>:
> >
> > Good day all,
> >
> > I have a program that declares and opens a cursor. The fetch is Load-All into a DS array that I use to populate a subfile. When the program first runs, works fine, I'm getting data displayed in the subfile, apparently data that should be there.
> >
> > Here is the sequence of events:
> >
> > 1. Declare and open cursor M1.
> >
> > 2. Fetch all selected records into a DS array.  Data is returned to the array.
> >
> > 3. Close the cursor.
> >
> > 4. Populate the subfile from the DS array. I see data, looks good.
> >
> > 5. Press F5 to refresh the data. (The cursor is based on an SQL View that should return data that reflects changes to the underlying tables/files the View is based on.
> >
> > 6. Returns ZERO rows. No data. Diagnostic data says zero too. So the subfile is what I call empty.
> >
> > 7. F5 again. Repeat steps 1 thru 4 again, this time there is data again.
> >
> > After that it's a cycle. Press F5, blank screen. F5 again, data. F5 again, blanks. F5 again, data!
> >
> > I thought of doing a DYNAMIC SENSITIVE SCROLL CURSOR so the cursor itself could get updated dynamically from data changes, /and/ at the same time I can FETCH FIRST again and start over.
> >
> > Except then I need to rerun the Declare and Open when they request to position to certain other places in the data, plus I want to implement something like this for another place in the program where the users can request a different ORDER BY.
> >
> > Any idea why this is happening, and more important how to "fix it"?
> >
> > To anybody who replies, THANK YOU!
> >
> > --Alan
> > --
> > This is the RPG programming on IBM i (RPG400-L) mailing list
> > To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> > or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at https://archive.midrange.com/rpg400-l.
> >
> > Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
> >
> > Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com