Re: Best approach for Embedded SQL SELECT in V7R1

[Alan Campin <alan0307d@xxxxxxxxx>]

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.