Re: FW: How to fetch into an array using dynamic SQL

[mlazarus <mlazarus@xxxxxxxxxxxx>]

 Birgitta, thanks for the response. I guess I'll have to specify all 
the fields individually as the recipients of the INTO. Out of curiosity, 
is there some function that could pivot the individual fields into a UDT?

-mark

On 8/13/2021 2:07 PM, Birgitta Hauser wrote:
> It is exactly the DIM which makes the difference!
> An array cannot be used in composition with embedded SQL.
>
> It is possible to read multiple rows in a multiple row fetch into an array
> data structure, but then you also have to specify how many rows you want to
> repeat.
> FETCH Next FROM YourCursor For x Rows INTO :ARRDS;
>
> Mit freundlichen Grüßen / Best regards
>
> Birgitta Hauser
>
>
> "Shoot for the moon, even if you miss, you'll land among the stars." (Les
> Brown) "If you think education is expensive, try ignorance." (Derek Bok)
> "What is worse than training your staff and losing them? Not training them
> and keeping them!"
> „Train people well enough so they can leave, treat them well enough so they
> don't want to.“ (Richard Branson)
>
>
> -----Original Message-----
> From: Birgitta Hauser<Hauser@xxxxxxxxxxxxxxx>
> Sent: Freitag, 13. August 2021 16:41
> To: 'RPG programming on IBM i'<rpg400-l@xxxxxxxxxxxxxxxxxx>
> Subject: RE: How to fetch into an array using dynamic SQL
>
> Genau der DIM ist es!
> Eine Feldgruppe ist nicht zulässig in SQL.
>
> Du kannst mehrere Datensätze mit einem multiple row fecht in eine Array
> Datenstruktur lesen, aber dann musst Du auch im FETCH die Anzahl der Zeilen,
> die Array-Datenstruktur einlesen:
> FETCH Next FROM YourCursor For x Rows INTO :ARRDS;
>
> Mit freundlichen Grüßen / Best regards
>
> Birgitta Hauser
>
>
> "Shoot for the moon, even if you miss, you'll land among the stars." (Les
> Brown) "If you think education is expensive, try ignorance." (Derek Bok)
> "What is worse than training your staff and losing them? Not training them
> and keeping them!"
> „Train people well enough so they can leave, treat them well enough so they
> don't want to.“ (Richard Branson)
>
>
> -----Original Message-----
> From: RPG400-L<rpg400-l-bounces@xxxxxxxxxxxxxxxxxx>  On Behalf Of mlazarus
> Sent: Freitag, 13. August 2021 15:29
> To: RPG programming on IBM i<rpg400-l@xxxxxxxxxxxxxxxxxx>
> Subject: Re: How to fetch into an array using dynamic SQL
>
> Hi Birgitta,
>
> D CodesDS ds
> D Codes Like( RC01 )
> D Dim( 5 )
>
> I also tried, which shouldn't make a difference:
>
> D Codes s Like( RC01 )
> D Dim( 5 )
>
> -mark
>
> On 8/13/2021 1:49 AM, Birgitta Hauser wrote:
>    
> > Can you show how you defined CODES?
> >
> > Mit freundlichen Grüßen / Best regards
> >
> > Birgitta Hauser
> >
> >
> > "Shoot for the moon, even if you miss, you'll land among the stars."
> > (Les
> > Brown)
> > "If you think education is expensive, try ignorance." (Derek Bok)
> > "What is worse than training your staff and losing them? Not training
> > them and keeping them!"
> > „Train people well enough so they can leave, treat them well enough so
> > they don't want to.“ (Richard Branson)
> >
> >
> > -----Original Message-----
> > From: RPG400-L<rpg400-l-bounces@xxxxxxxxxxxxxxxxxx>   On Behalf Of
> > mlazarus
> > Sent: Freitag, 13. August 2021 03:02
> > To: RPG400-L@xxxxxxxxxxxxxxxxxx
> > Subject: How to fetch into an array using dynamic SQL
> >
> >     Is is possible to fetch multiple fields into an array with dynamic SQL?
> > I'm using dynamic because the file name is passed to the program.
> > The actual number of "repeating" fields is much larger than the five I
> > am displaying.
> >
> >      StmtSQL = 'SELECT ' +
> >                 'max(RC01), max(RC02), max(RC03), max(RC04), max(RC05)' +
> >                 ' FROM ' + InputFileName ;
> >
> >      Exec SQL PREPARE S1 FROM :StmtSQL ;
> >      Exec SQL DECLARE C1 CURSOR FOR S1 ;
> >      Exec SQL OPEN C1 ;
> >
> > // Codes is defined as an array with the corresponding number of
> > buckets as // the fields I'd like to populate.
> >      Exec SQL FETCH C1 INTO :Codes ;
> >
> >     The SQL pre-compiler complains:
> > SQL0312: Position 32 Variable CODES not defined or not usable. Reason:
> > No declaration for the variable exists, the declaration is not within
> > the current scope, or the variable does not have an equivalent SQL data
> >      
> type.
>    
> >     Is there a syntax that would work to populate the array?
> >
> >     -mark