Converting fetched columns into an array

Coming up with a meaningful title for this post proved harder than normal. I was asked a question about how to fetch data, using a SQL Cursor, and convert some of the fetched columns into an array. The questioner had tried various approaches, but was always receiving an error in the SQL pre-compile step of his program.

The file in question contained 35 fields he was interested in. The record would be for an account, by year and month, 31 balance fields one for every day of the month (yes, I know not all months have 31 days), and finally a total monthly change balance. The questioner wanted to put all of the 31 day fields into one array.

Let me start with my version of his file, that I called TESTFILE:

01 A R TESTFILER 02 A ACCOUNTNBR 14P 0 03 A YEAR 4P 0 04 A MONTH 2P 0 05 A BALANCE01 15P 2 06 A BALANCE02 15P 2 07 A BALANCE03 15P 2 08 A BALANCE04 15P 2 09 A BALANCE05 15P 2 10 A BALANCE06 15P 2 11 A BALANCE07 15P 2 12 A BALANCE08 15P 2 13 A BALANCE09 15P 2 14 A BALANCE10 15P 2 15 A BALANCE11 15P 2 16 A BALANCE12 15P 2 17 A BALANCE13 15P 2 18 A BALANCE14 15P 2 19 A BALANCE15 15P 2 20 A BALANCE16 15P 2 21 A BALANCE17 15P 2 22 A BALANCE18 15P 2 23 A BALANCE19 15P 2 24 A BALANCE20 15P 2 25 A BALANCE21 15P 2 26 A BALANCE22 15P 2 27 A BALANCE23 15P 2 28 A BALANCE24 15P 2 29 A BALANCE25 15P 2 30 A BALANCE26 15P 2 31 A BALANCE27 15P 2 32 A BALANCE28 15P 2 33 A BALANCE29 15P 2 34 A BALANCE30 15P 2 35 A BALANCE31 15P 2 36 A MONTHLYCHG 15P 2

He said that he had many more fields in his file, but this is the minimum data set he needed. I am sure his file is keyed, but for my example I don't need a key.

In his version he was using a single row fetch from a SQL Cursor. It is more efficient to use a multiple row fetch from a Cursor, which is why I will be using that in my example program.

This is my example program:

01 **free 02 ctl-opt main(Main) option(*srcstmt) ; 03 dcl-proc Main ; 04 dcl-ds GotData extname('TESTFILE') qualified ; 05 end-ds ; 06 dcl-s Rows uns(10) inz(%elem(GotData : *max)) ; 07 dcl-s Counter like(Rows) ; 08 dcl-ds BalanceRow extname('TESTFILE') ; 09 BalanceArray packed(15:2) samepos(Balance01) dim(31) ; 10 end-ds ; 11 exec sql DECLARE C0 CURSOR FOR SELECT * FROM TESTFILE FOR READ ONLY ; 12 exec sql OPEN C0 ; 13 exec sql FETCH NEXT FROM C0 FOR :Rows INTO :GotData ; 14 exec sql CLOSE C0 ; 15 Rows = %elem(GotData) ; 16 for Counter = 1 to Rows ; 17 eval-corr BalanceRow = GotData(Counter) ; 18 dsply %char(BalanceArray(10)) ; 19 endfor ; 20 end-proc ;

Line 1: All of my code is now free format modern RPG. All of yours should be too.

Line 2: Using a Main procedure is more efficient as the RPG compiler does not include all the code necessary for the RPG cycle, even if I don't use it. And I have my favorite RPG control option I add to all my RPG programs.

Line 3: Start of the Main procedure, that ends at line 20.

Lines 4 and 5: This is the data structure array I will fetching the results from the Cursor into. The EXTNAME tells the compiler to use the file TESTFILE for the definition of the data structure's subfields. For a file with "hundreds of fields", as the questioner said his file had, this is the easy way to have all of the file's fields in the data structure without having to code them all manually into the program. As this is a data structure array it needs to be qualified, so that is used as the array's name. The array is an auto extending array, which means it will only contain the number of elements I move into it, up to 9,999.

Line 6: This is the definition of a variable that contains the maximum number of elements the GotData data structure array can contain.

Line 7: As its name suggests this variable will be used as a counter for a For-group.

Lines 8 – 10: This data structure is defined, with the EXTNAME, to have all the subfields that the file TESTFILE has for fields. On line 9 I am defining an array that will contain all of the balance fields in the file, 31 elements, and the SAMEPOS tells the compiler that this array starts at the first balance subfield.

Line 11: The definition of the Cursor I will be using to retrieve the data from TESTFILE.

Line 12: Open the Cursor.

Line 13: I fetch up to 9,999 rows from TESTFILE into the data structure array GotData.

Line 14: As I have all the data I need I close the Cursor.

Line 15: I could have used the SQL GET DIAGNOSITICS to return the number of rows I fetched from the Cursor. As I am using an auto extending array the number of elements in the array is the same as the number of rows of results I returned.

Lines 16 – 19: I am using a For-group to move the rows from the data structure array, GotData, into the data structure, BalanceRow, using the Eval corresponding operation code, EVAL-CORR. This means that only the subfields with the same names have their values moved to the BalanceRow data structure. On line 18 I display the value in the tenth element of the array. As I did not have the QUALIFIED keyword on Balance I can just use the array's name, BalanceArray, without needing to qualify it with the data structure's name.

As I mentioned above I added two records to TESTFILE. In the first I gave each balance field the same value as its name, i.e. the fields BALANCE10 contains 10.00 . In the second record all the balance fields contain 0.01 .

After compiling this program I started debug and added a breakpoint at line 18.

When I called the program and debug stopped at line 18 I could see what the array BalanceArray contains:

> EVAL balancearray BALANCEARRAY OF BALANCEROW(1) = 0000000000001.00 BALANCEARRAY OF BALANCEROW(2) = 0000000000002.00 BALANCEARRAY OF BALANCEROW(3) = 0000000000003.00 BALANCEARRAY OF BALANCEROW(4) = 0000000000004.00 BALANCEARRAY OF BALANCEROW(5) = 0000000000005.00 BALANCEARRAY OF BALANCEROW(6) = 0000000000006.00 BALANCEARRAY OF BALANCEROW(7) = 0000000000007.00 BALANCEARRAY OF BALANCEROW(8) = 0000000000008.00 BALANCEARRAY OF BALANCEROW(9) = 0000000000009.00 BALANCEARRAY OF BALANCEROW(10) = 0000000000010.00 BALANCEARRAY OF BALANCEROW(11) = 0000000000011.00 BALANCEARRAY OF BALANCEROW(12) = 0000000000012.00 BALANCEARRAY OF BALANCEROW(13) = 0000000000013.00 BALANCEARRAY OF BALANCEROW(14) = 0000000000014.00 BALANCEARRAY OF BALANCEROW(15) = 0000000000015.00 BALANCEARRAY OF BALANCEROW(16) = 0000000000016.00 BALANCEARRAY OF BALANCEROW(17) = 0000000000017.00 BALANCEARRAY OF BALANCEROW(18) = 0000000000018.00 BALANCEARRAY OF BALANCEROW(19) = 0000000000019.00 BALANCEARRAY OF BALANCEROW(20) = 0000000000020.00 BALANCEARRAY OF BALANCEROW(21) = 0000000000021.00 BALANCEARRAY OF BALANCEROW(22) = 0000000000022.00 BALANCEARRAY OF BALANCEROW(23) = 0000000000023.00 BALANCEARRAY OF BALANCEROW(24) = 0000000000024.00 BALANCEARRAY OF BALANCEROW(25) = 0000000000025.00 BALANCEARRAY OF BALANCEROW(26) = 0000000000026.00 BALANCEARRAY OF BALANCEROW(27) = 0000000000027.00 BALANCEARRAY OF BALANCEROW(28) = 0000000000028.00 BALANCEARRAY OF BALANCEROW(29) = 0000000000029.00 BALANCEARRAY OF BALANCEROW(30) = 0000000000030.00 BALANCEARRAY OF BALANCEROW(31) = 0000000000031.00

When the Display operation code, DSPLY, was reached the following was displayed:

DSPLY 10.00

Which is the same as what the field BALANCE10 contains.

The next time through the For-loop the following was displayed:

DSPLY .01

As all of the balance fields in the second record contain 0.01 the value displayed is also what I expected.

The question has been answered in a simple and easy to understand way, K.I.S.S.

 

This article was written for IBM i 7.5, and should work for some earlier releases too.