P.S.: Re: SQL Cursor issues -- RPG400-L

Hi Alan,

what I forgot to mention - you have written, that you do a fetch with all the data. That means you load the whole table into memory, even if the user only looks at the first subfile page.

I don’t know how large the table is - but it’s probably not the best performance wise to load it completely if you only need maybe 20 records.

A growing subfile a keeping the cursor open may be the better option.

Just my 2ct about that issue.

Kind regards,
Daniel

Am 11.06.2022 um 23:32 schrieb Daniel Gross <daniel@xxxxxxxx>:

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