Midrange News for the IBM i Community


Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
Chicagoland
SQL SELECT INTO Array in RPG
has no ratings.
Published: 20 Nov 2013
Revised: 17 Mar 2015 - 830 days ago
Last viewed on: 24 Jun 2017 (7117 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

SQL SELECT INTO Array in RPG Published by: Bob Cozzi on 20 Nov 2013 view comments(4)

The V7R1 docs indicate that a multiple occurrance data structure may be used when fetching more than one row at a time.

Here's what I want. I have defined an array with DIM(50), it is character with a length of 30.

The SQL statement is supposed to grab all the records that match a specific code (normally under 10 rows, but sometimes 10, 11 or even 12 exist). So I coded the array as DIM(50).

I'm only using a SELECT statement to get the data, as follows:

D myArray             S                   30A  Dim(50)

EXEC SQL SELECT ORDTEXT INTO :myArray FROM ORDMSG WHERE code = :ordCode
             FETCH FIRST 50 ROWS ONLY;

This isn't compiling. It's complaining about the MYARRAY variable being not valid. I've never actually had to do this (read into an array), and I thought it just worked.   Maybe someone else knows if this is possible and can show me the way?

[UPDATE]

The accepted way to use a DS array is to only reference it in a DECLARE/FETCH sequence, not via a stand-alone SELECT/INTO statement.

H MAIN(TESTARR)  DFTACTGRP(*NO)                            
H OPTION(*SRCSTMT)  EXTBININT(*YES)                        
                                                           
D myArray         DS                  Qualified Dim(50)    
D  ordText                      30A                        
                                                           
P TESTARR         B                                        
D TESTARR         PI                  EXTPGM('TESTARR')  
 /free                                                     
      EXEC SQL SET OPTION naming = *SYS, COMMIT = *NONE;   
                                                           
      EXEC SQL DECLARE FMITA CURSOR FOR                    
               SELECT ordText                              
               FROM   TESTORD;                             
                                                           
      EXEC SQL OPEN  FMITA;                                
      EXEC SQL FETCH FMITA FOR 50 ROWS INTO :myArray;      
      EXEC SQL CLOSE FMITA;                                
 /end-free                            

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: SteveCCNJ
Premium member *
Egg Harbor Twp., NJ
Comment on: SQL SELECT INTO Array in RPG
Posted: 3 years 7 months 3 days 13 hours 9 minutes ago

I haven't had to do this, but I would declare your SELECT as a cursor and put the FETCHes into a DO loop while incrementing the elements. 

Posted by: SteveCCNJ
Premium member *
Egg Harbor Twp., NJ
Comment on: SQL SELECT INTO Array in RPG
Posted: 3 years 7 months 3 days 13 hours ago

Actually, the problem may be that it wants to have DS subfields defined that will match your SELECT statement.  I think then it would do the SELECT INTO as you fantasized.

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: SQL SELECT INTO Array in RPG
Posted: 3 years 7 months 3 days 10 hours 54 minutes ago

Looks like it only works (so far) with an UNQualified Multiple Occurrance Data Structure. Meaning the OCCURS(50) keyword is accepted but DIM(50) is not. 

Haven't used an Occurs kwd since V5R2 was announced.

Posted by: JerryG
Premium member *
Comment on: SQL SELECT INTO Array in RPG
Posted: 2 years 3 months 15 days 8 hours 31 minutes ago
Edited: Wed, 11 Mar, 2015 at 17:30:40 (836 days ago)

Posting to an old thread (after having hit the same problem) in case anyone else comes upon this.

SteveCCNJ is correct; a multiple-row FETCH requires a data structure (DS), not standalone field (S), even if that DS has just one field. HTH.