× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



#1. Thanks Daniel, The input of the cursor is with a FETCH to a data structure array, and the loop is controlled by DOU ROWSFETCHED < ROWSTOFETCH, not a compare to SqlCode. I figure if there is an SQLCODE < Zero error, it won't fetch anything. At first I did have a problem with a few columns that were returning Null, I had thought I handled that but didn't.

#2. Thanks to Alan (Campin), good idea, I'll start checking the Prepare and Open henceforth.

#3. Thanks, Jon P. I agree about initially loaded a DS array and working with that. In this particular case, if it were not so important to get the refreshed data with every subfile reload, I would do that, or more likely use a user index as I did in a couple of recent assignments. This one involves shipments and appointments, and the data is constantly changing all day, and they need the screen to reflect the current state of each shipment. Data keeps dropping off all day, and we don't want them losing time on loads that have already moved out of status for view.

#4. THE FIX!  My bad, in this case I was getting "duplicate rows" according to the key fields in the columns, probably a result of something I missed in the JOINs. I've spent weeks on this thing moving /very/ complex logic from RPG into the base SQL View. It involves a dozen files, one file used as three different files, and a lot of Select when A then B when C then D, and so on, about five common table expressions.

Anyway, I used the RPG to skip the duplicates and that did it. My next go-round in this program, or when I do something with that View, I will revisit the SQL statement.

THANKS!

--Alan Cassidy



On 10/10/2022 10:10 AM, Daniel Gross wrote:
Hi Alan,

what Darren wrote is often true - especially when you only code

dow SQLCODE = *zero;
...

Because loops like that are do not detect SQL errors (SQLCODE < *zero).

The best way is, to step debug the program, and find out, what happens.

And of course - you have to make sure, that the SFL is cleared and RRN is reset when starting over.

Regards,
Daniel


Am 10.10.2022 um 16:04 schrieb Darren Strong<darren@xxxxxxxxx>:

Usually this happens because there is one or more values that you cannot bring into your program because the value is not compatible with the variable. For example, if one field is sometimes null, and your RPG variable can't except a NULL, then the SQL fetch fails, you get a code that you assume is the end of the list code, and your Dox loop exits. When you sort (order by) the list, these nulls come at different times in the cursor fetch.


-----Original Message-----
From: RPG400-L<rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Alan Cassidy
Sent: Monday, October 10, 2022 9:26 AM
To:rpg400-l@xxxxxxxxxxxxxxxxxx
Subject: Repeated CLOSE and OPEN of SQL cursors

CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.


I have one last thing stumping me for now to finish a major code change to boost performance in one program I'm working on, relating to SQL cursors.

The RPG program opens an SQL cursor and the entire subfile is input.
Then the user requests a different "ORDER BY" than the default. I use the same SQL for the sort, the whole thing is identical except for tacking on the different ORDER BY clause at the end of the dynamically constructed SQL statement.

At the top of the subroutine that does the cursor open, the code first does an Exec SQL Close M1, where M1 is the cursor. Then re-open. So if this is the second or third time, etc., opening the cursor, the code first runs the line "exec SQL Close M1".

The issue is that the sorted open is getting sometimes around two or three times (it varies) as many rows returned as the first open.

Can someone point me to why or how that could happen?

--Alan Cassidy



--
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 athttps://archive.midrange.com/rpg400-l.

Please contactsupport@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link:https://amazon.midrange.com
--
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
athttps://archive.midrange.com/rpg400-l.

Please contactsupport@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link:https://amazon.midrange.com

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.