Re: Dynamic SQL fetch into an array

[Vern Hamberg <vhamberg@xxxxxxxxxxx>]

Tim

Perhaps it thinks that the overlay is
an additional field.

Yes, this is probably right.

Take a look at the compile listing and see what SQLxxxx fields are 
generated for the various fields in your SELECT field list - all the 
substrings. In the original version, probably the pre-compiler doesn't 
know what to do with the DIM(x) keyword and creates a field that is only 
3 long.

Although the 21-character "field" has 7 elements, each 3 long, it isn't 
and array - no such creature in SQL that I know of. You need to define a 
single 21-character field in your data structure. Then get that 21-char 
field into the array you want - maybe another DS as suggested below.

I'd say this is a weakness (bug?) of the pre-compiler - you might try 
reporting it to IBM and see what they say about it.

HTH
Vern

On 6/11/2010 4:09 PM, Timothy Adair wrote:
> Thanks for the reply Michael.
>
> This causes the SQL pre-compile to fail with the error:
>
> MSG ID  SEV  RECORD  TEXT
> SQL0312  30     787  Position 27 Variable SCHRECORD not defined or not
> usable.
>
> I suspect it compares the number of fields in the DS versus the number in
> the fetch and barfs all over itself.  Perhaps it thinks that the overlay is
> an additional field.
>
> TA
>
>
>
>
>
> <Michael_Schutte@xxxxxxxxxxxx>  wrote in message
> news:mailman.32385.1276289034.2580.rpg400-l@xxxxxxxxxxxxxxx
>
> change
>
>    d   arrRT                        3  0 dim(7)
>
> To
>
>    d   strRT                       21
>    d   arrRT                        3  0 OverLay(strRT) dim(7)
>
>
> I haven't tested, but it's worth a try.
>
>
>
> --
>
> Michael Schutte
> Admin Professional
>
>
>
> Announcing Bob Evans Bob-B-Q® Road Trip! For a limited time, America's best
> Bob-B-Q® tastes are all at Bob Evans! For more information, visit
> www.bobevans.com/menu/seasonal.aspx
>
>
>
>
>
>               "Timothy Adair"
>               <tadair@prairiefa
>               rms.com>                                                    To
>               Sent by:                  rpg400-l@xxxxxxxxxxxx
>               rpg400-l-bounces@                                          cc
>               midrange.com
>                                                                     Subject
>                                         Dynamic SQL fetch into an array
>               06/11/2010 04:07
>               PM
>
>
>               Please respond to
>                RPG programming
>                on the IBM i /
>                   System i
>               <rpg400-l@midrang
>                    e.com>
>
>
>
>
>
>
> After beating my head against this issue, I've searched the archives and
> the
> Internet with no luck.  I am attempting to fetch a record in embedded SQL
> and this record includes a 7-element array.  I'm fetching from a
> program-described file (QS36F).
>
> I've oversimplified the code below - I just need to get the basic concept.
>
>
>      d SchRecord       ds
>      d   c@                    1      5  0
>      d   typi                  6      6  0
>      d   fschi                 7      7  0
>      d   cyci                  8      8  0
>      d   arrRT                        3  0 dim(7)
>
>        sqlstmt = 'select                     +
>                 substr(K00001,1,5) as c@,    +
>                 substr(K00001,6,1) as typi,  +
>                 substr(K00001,7,1) as fschi, +
>                 substr(K00001,8,1) as cyci,  +
>                 substr(F00001,1,21) as arrRT +
>                 from qs36f/"DS.SCH"
>
>    exec sql
>        prepare P1 from :sqlstmt;
>
>    exec sql
>        declare C1 cursor for P1;
>
>    exec sql
>        open C1;
>
>
>        dou sqlstt<>  '00000';
>
>    exec sql
>        fetch C1 into :SchRecord;
>
>            if sqlstt<>  '00000';
>                leave;
>            endif;
>
> Etc.
>
>
>
> The other fields read in properly but "arrRT" reads in as a single 3-digit
> field instead of an array of seven 3-digit elements.  I defined it as an
> array in the DS - why does SQL ignore that?
>
> I have to do this as dynamic because of the "where" and "order by" clauses
> that are needed.
>
> Any thoughts are appreciated.  I'm starting to feel like Danny Noonan in
> Caddyshack ("right in the lumber yard").
>
> TA
>
> --
> 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.
>
>
>