Re: Loading Variable Array From SQL Caveat

[Thomas Burrows <thomas.burrows.1957@xxxxxxxxx>]

Sam:
How did you get your Data Structure to compile?

       dcl-ds csz_a dim(*auto : 50000) qualified;
Have been told by Jon Paris that the system needs to be at least V7R4 to
get the
DIM(*auto : number) to compile. But my tests on V7R5 still has a compile
error
on this line.

Can you show your complete DCL-DS statement?
What release level are you on? Any special PTF's?

Thomas in Texas
        exec sql fetch from csz_cur for 42735 rows into :csz_a;


On Mon, Feb 12, 2024 at 2:02 PM Sam_L <lennon_s_j@xxxxxxxxxxx> wrote:

> I found that while loading a variable length array with SQL you may not
> load all the rows you expect...
>
> I wanted to load 42,000+ rows using a technique like this:
>         dcl-ds csz_a dim(*auto : 50000) qualified;
>         exec sql fetch from csz_cur for 42735 rows into :csz_a;
> But I got only 32767 rows loaded.
>
> And I couldn't find a way to do a loop with a fetch like this:
>         exec sql fetch from csz_cur for 42735 rows into :csz_a(inx);
> because SQL won't accept it.
>
> I ended up fetching one record at a time into a single element array and
> adding that to the full array. It was still blazing fast and the program
> is rarely executed anyway.
>
> Maximum rows that can be fetched is probably documented somewhere in the
> SQL manual though I didn't think to look. And probably most people won't
> want to load this many rows--mine is maybe a unique use case and I
> wanted to experiment.
>
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/rpg400-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> questions.