Nathan,
I had not considered an SQL CLI approach. I too am a little wary of SQLRPGLE. I hate stepping into the pre-compiled code.
Had not considered the view approach either. The process is very simple.
For index=1 To 300
Chain (index) SQLStmtFile
Exec sql execute immediate: SQLStmt // each stmt joins 4 files in a different way
EndFor
The system performs a new open for each of the 4 files and every iteration. But since the entire iteration completes in 4 secs then I guess the system knows what it's about.
Peter
-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Nathan Andelin
Sent: Tuesday, 8 July 2014 6:49 a.m.
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: embedded SQL loop leaves multiple files open
Peter,
Years ago, I wrote high-level wrappers around IBM's SQL CLI procedures, which today supports 99.9% of our SQL interface requirements. I never really got into using SQLRPGLE interfaces much, which seem somewhat clunky to me in comparison; That might just be personal preference. One nice thing about the SQL CLI approach is that you have full control over the open, navigation, and close of SQL result sets via program logic. Have you ever considered something like that?
I don't know what you mean by a program running 300 SQL statements and generating 300 values. You say the program joins only 4 DB tables. Couldn't you just create an SQL View joining the 4 tables, then refresh the View 300 times using different "where" clause criteria? Seems like SQL CLI could handle that very easily with very readable code.
We always run SQL CLI interfaces using "server" mode, where the statements (open, navigation, close, etc.) run in an instance of one of the QSQLSVR prestart jobs, rather than the Job which uses the result sets. SQL CLI appears to use shared memory for communicating with Jobs which use the result sets.
Nathan.
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
#####################################################################################
This correspondence is for the named person's use only. It may contain confidential
or legally privileged information, or both. No confidentiality or privilege is waived
or lost by any mistransmission. If you receive this correspondence in error, please
immediately delete it from your system and notify the sender. You must not disclose,
copy or rely on any part of this correspondence if you are not the intended recipient.
Any views expressed in this message are those of the individual sender, except where
the sender expressly, and with authority, states them to be the views of Veda.
If you need assistance, please contact Veda on either :-
Australia customerassistance@xxxxxxxxxxx or New Zealand +64 9 367 6200
midrange.com
