× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. RPG400-L
  3. August 2021

RE: How to fetch into an array using dynamic SQL

Mark you have the following options:



1. Use a data structure with the same fields as your select statement
has. You can then select or fetch into :dsName. Use a externally defined
data structure.
2. Specify the fields you need in the SELECT statement. Define a data
structure with those fields only. Then SELECT or FETCH into :dsNAME. Here
you will have to manually define the data structure and all the fields.
3. Specify the fields you need in the SELECT. Define new or use
existing fields for each field in the Select statement. Then SELECT field_1,
field_2, field_3 into :new_field_1, :new_filed_2, :new_field_3 from FILE.
Here you may need to define d-specs or dcl-s for each field you do not have.



It does not matter if you use these in a simple SQL command or in dynamic
SQL. Whatever you choose to do, you will need either a data structure to
receive the values into or defined individual field as in 3 above.



Darryl Freinkel

A4G

Telephone: 770.321.8562 Mobile: 678.355.8562



-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of mlazarus
Sent: Friday, August 13, 2021 11:58 AM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: How to fetch into an array using dynamic SQL



Birgitta,



Does that mean, since the columns are all in the same record, I would need
to specify the entire list of fields in the INTO?



-mark



On 8/13/2021 10:41 AM, Birgitta Hauser wrote:

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< <mailto:rpg400-l-bounces@xxxxxxxxxxxxxxxxxx>
rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of

mlazarus

Sent: Freitag, 13. August 2021 15:29

To: RPG programming on IBM i< <mailto:rpg400-l@xxxxxxxxxxxxxxxxxx>
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< <mailto:rpg400-l-bounces@xxxxxxxxxxxxxxxxxx>
rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of

mlazarus

Sent: Freitag, 13. August 2021 03:02

To: <mailto:RPG400-L@xxxxxxxxxxxxxxxxxx> 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

--

This is the RPG programming on IBM i (RPG400-L) mailing list To post

a message email: <mailto:RPG400-L@xxxxxxxxxxxxxxxxxx>
RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe,

unsubscribe, or change list options,

visit: <https://lists.midrange.com/mailman/listinfo/rpg400-l>
https://lists.midrange.com/mailman/listinfo/rpg400-l

or email: <mailto:RPG400-L-request@xxxxxxxxxxxxxxxxxx>
RPG400-L-request@xxxxxxxxxxxxxxxxxx

Before posting, please take a moment to review the archives at

<https://archive.midrange.com/rpg400-l>
https://archive.midrange.com/rpg400-l.



Please contact <mailto:support@xxxxxxxxxxxxxxxxxxxx>
support@xxxxxxxxxxxxxxxxxxxx for any subscription

related questions.



Help support midrange.com by shopping at amazon.com with our

affiliate



link:



<https://amazon.midrange.com> https://amazon.midrange.com







--

This is the RPG programming on IBM i (RPG400-L) mailing list To post a

message email: <mailto:RPG400-L@xxxxxxxxxxxxxxxxxx>
RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe,

or change list options,

visit: <https://lists.midrange.com/mailman/listinfo/rpg400-l>
https://lists.midrange.com/mailman/listinfo/rpg400-l

or email: <mailto:RPG400-L-request@xxxxxxxxxxxxxxxxxx>
RPG400-L-request@xxxxxxxxxxxxxxxxxx

Before posting, please take a moment to review the archives at

<https://archive.midrange.com/rpg400-l>
https://archive.midrange.com/rpg400-l.



Please contact <mailto:support@xxxxxxxxxxxxxxxxxxxx>
support@xxxxxxxxxxxxxxxxxxxx for any subscription

related questions.



Help support midrange.com by shopping at amazon.com with our affiliate
link:

<https://amazon.midrange.com> https://amazon.midrange.com





--

This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: <mailto:RPG400-L@xxxxxxxxxxxxxxxxxx>
RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list
options,

visit: <https://lists.midrange.com/mailman/listinfo/rpg400-l>
https://lists.midrange.com/mailman/listinfo/rpg400-l

or email: <mailto:RPG400-L-request@xxxxxxxxxxxxxxxxxx>
RPG400-L-request@xxxxxxxxxxxxxxxxxx

Before posting, please take a moment to review the archives at
<https://archive.midrange.com/rpg400-l>
https://archive.midrange.com/rpg400-l.



Please contact <mailto:support@xxxxxxxxxxxxxxxxxxxx>
support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.



Help support midrange.com by shopping at amazon.com with our affiliate link:
<https://amazon.midrange.com> https://amazon.midrange.com


As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.