Re: Repeated CLOSE and OPEN of SQL cursors -- RPG400-L

Alan,Run the same SQL statements in ACS and compare the output. You'll see which rows are missing and perhaps be closer to figuring out why.Share with us your test for end of file in your do-loop.Yours truly,Glenn Gundermannglenn.gundermann@xxxxxxxxx(416) 317-3144---
-------- Original message --------From: Daniel Gross <daniel@xxxxxxxx> Date: 2022-10-10 10:10 a.m. (GMT-05:00) To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx> Subject: Re: Repeated CLOSE and OPEN of SQL cursors 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 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> -- > 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-- This is the RPG programming on IBM i (RPG400-L) mailing listTo post a message email: RPG400-L@lists.midrange.comTo subscribe, unsubscribe, or change list options,visit: https://lists.midrange.com/mailman/listinfo/rpg400-lor email: RPG400-L-request@lists.midrange.comBefore posting, please take a moment to review the archivesat 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