Michael,
If the data permits, I usually try to select all rows
into a MODS with something like:
* Start: data to store cursor rows ---------------------------------------
D aa_MaxC C const(3000) max aa Data
D aa_Max S 5I 0 Inz(aa_MaxC) " " "
D aa_@ S 5I 0 Inz(0) index to aa_DS
D aa_DS DS OCCURS(aa_MaxC) Qualified
D ArtNum 9P 0 Inz(*Zero) article number
D ArtNam 30A article name
* End: data to store cursor rows ---------------------------------------
Clear *All aa_DS;
Exec SQL FETCH aa_Csr FOR :aa_Max ROWS INTO :aa_DS;
// Check if aa_DS is at max capacity: exit if it is
If SQLCOD = *Zero And Sqler3 >= aa_Max;
RptMsg = 'aa_DS at max capacity in myPgm.mySubRtn';
PgmErr();
LeaveSR;
Endif;
//Start: loop thru aa_DS ----------------------------------------------
For aa_@ = 1 TO aa_MaxC;
%Occur(aa_DS) = aa_@;
If aa_DS.ArtNum = *Zero;
Leave; // exit loop
Endif;
//code to process aa_DS
EndFor;
//End: loop thru aa_DS ----------------------------------------------
Otherwise I use typical loop code to fetch rows until done
Like you, I have wondered about using a DS array,
but have yet to take time to test if this works.
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Michael Ryan
Sent: Tuesday, November 06, 2012 7:38 AM
To: RPG programming on the IBM i / System i
Subject: Best approach for Embedded SQL SELECT in V7R1
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.
midrange.com
