RE: Dynamic SQL fetch into an array

["Christen, Duane" <Duane.Christen@xxxxxxxxxx>]

TA;

Why not do this:

D sqlData	DS		
D  k00001		21A

D record	DS		Based(recordBase)
D... (your original definition here)

recordBase = %Addr(sqlData);

sqlstm = 'select SubStr(K0001, 1 , 21) +
          from qs36f/"DS.SCH"';

...

exec sql 
    fetch C1 into :sqlData;

...

This will put the first 21 bytes of data into sqlData, which is "overlaid" with by the record DS.

Duane Christen

--

 	 	 	 	
 	 	Duane Christen
Senior Software Engineer
(319) 790-7162
Duane.Christen@xxxxxxxxxx
	 	
Visit PAETEC.COM	  	
 	 	

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Timothy Adair
Sent: Friday, June 11, 2010 4:10 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: Dynamic SQL fetch into an array

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(r) Road Trip! For a limited time, America's best Bob-B-Q(r) 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.


--
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.