Loading arrays from a file with a single SQL fetch

I found a way to load a large table into an array with a single SQL fetch
and thought I would share the technique in case anyone else had a need to do
so. I originally only needed to load one field into one array and used the
first technique. Then I needed to load two fields into two arrays and am
now using the second technique. Both of these are working in V5R4 and the
first technique, with one array, worked in V5R2 and V5R4.

d ref ds occurs(400)
d fld30 30a

// load DS from file
d fmds ds occurs(400)
d fmfld like(fld30)

dind_array ds occurs(400)
d inds 5i 0 dim(1)

// load arrays fm from DS fmds
d fmptr s * inz(%addr(fmds))
d fm s 30a dim(400) based(fmptr)


As you can see, the array is based on a pointer over the MODS fmds, and the
block fetch from the following subprocedure loads the MODS fmds, loading the
array.

p LoadArrayFM b

d LoadArrayFM pi

/free

exec sql
include sqlda;

exec sql
declare c1 cursor for
select field1
from table1;

exec sql
open c1;

exec sql
fetch next from c1 for 400 rows into :fmds :ind_array;

exec sql
close c1;

/end-free

p LoadArrayFM e
/eject



If you need to load 2 arrays from the same file the technique is very
similar.


d ref ds occurs(400)
d fld10 10a
d fld30 30a

// load DS from file
d fmds ds occurs(400)
d idfld like(fld10)
d fmfld like(fld30)

dind_array ds occurs(400)
d inds 5i 0 dim(2)

// load arrays id & fm from DS fmds
d arrayds ds based(fmptr)
d form 40a dim(400)
d id 10a overlay(form: 1)
d fm 30a overlay(form: *next)

d fmptr s * inz(%addr(fmds))


//****************************************************************
// Load Array FM with Form List from FORMTABL01
//****************************************************************
p LoadArrayFM b

d LoadArrayFM pi

/free

exec sql
include sqlda;

exec sql
declare c1 cursor for
select field1, field2
from table1;

exec sql
open c1;

exec sql
fetch next from c1 for 400 rows into :fmds :ind_array;

exec sql
close c1;

/end-free

p LoadArrayFM e
/eject


Hope this helps someone. Perhaps one of the gurus here even has a way to
improve this process?

Gene