Re: embedded SQL loop leaves multiple files open

Peter,

I don't know if this will help. But I posted an example of a program that
generates an SQL result set based on row filter criteria within a loop of
300 iterations, using a our high-level "cursor" API.

I don't fully understand your SQL select criteria, but it seems like you
might be able to use a high-level SQL CLI API like this that would only
create 4 open data paths for the 4 files in question, rather than 1,200
open data paths.

http://code.midrange.com/0783add3f1.html

Nathan.



On Mon, Jul 7, 2014 at 2:00 PM, Peter Connell <Peter.Connell@xxxxxxxxxx>
wrote:

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
--
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.