RE: Best approach for Embedded SQL SELECT in V7R1 -- RPG400-L

Hi Alan
I would be EXTREMELY interested in your gracious offer

Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Campin
Sent: Tuesday, November 06, 2012 11:09 AM
To: RPG programming on the IBM i / System i
Subject: Re: Best approach for Embedded SQL SELECT in V7R1

I use SQL exclusively for I/O in screen programs and was pleasant surprised when I first used it that it actually made coding simpler and yes I always use a data structure or records array to read records and I almost always map to a user space. It makes it very, very fast.

If you are interested, I can send a module doing a single page load written using subfiles and SQL.

On Tue, Nov 6, 2012 at 7:37 AM, Michael Ryan <michaelrtr@xxxxxxxxx> wrote:

Hi folks...looking for opinions/ideas here. I'm working with a program
that declares a cursor, then does a FETCH NEXT to iterate through the
result set and fill a subfile. It looks like this:

DECLARE C1 CURSOR FOR
Select * From Inventory
Where...<where and order by clauses> ...
Exec SQL
Fetch Next
From C1
Into :WkInventory;

Where WkInventory is an externally described DS based on the Inventory
file. The data is then moved from WkInventory to the subfile, and the
subfile is eventually displayed.

I *think* that performance would improve by FETCHing multiple rows
into a MODS or an array in a data structure, rather than esentially
performing RLA with SQL. Recently upgraded to V7R1, so I'm looking for
the best approach at this OS level - I know I can do more than I could
do in V5R4. My first thought is a MODS and specifying a number on the
FETCH. But then I need to be concerned about 're-FETCHing' when I run
out of rows for my subfile, right? Would an array make more sense than a MODS?

I want to use SQL where it makes sense and RLA where it makes sense. I
need to work through this concept so I can use this technique as I go forward.
Ideas, opinions, code samples all welcome. Thanks in advance!
--
This is the RPG programming on the IBM i / System i (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 is the RPG programming on the IBM i / System i (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.

Disclaimer: This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. Any views or opinions presented are solely those of the author and do not necessarily represent those of the company.