RE: Using Array Values in Insert Query ?

[<SRamanujan@xxxxxxxxxxxxxxxxxx>]

This is great! Thanks, -s

Sudha Ramanujan
SunGard Futures Systems
sramanujan@xxxxxxxxxxxxxxxxxx
(312) 577 6179
(312) 577 6101 - Fax

-----Original Message-----
From: rob@xxxxxxxxx [mailto:rob@xxxxxxxxx] 
Sent: Thursday, February 17, 2005 8:38 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: Using Array Values in Insert Query ?

By the way, I did test this on a V5R3 machine and it works great:

     d TestAry         ds                  dim(25) qualified
     d  name                         25a
     d  age                           5i 0
     d x               s              5i 0

      /free
       for x=1 to %elem(TestAry);
         TestAry(x).name='Fred';
         TestAry(x).age=x+5;
       ENDFOR;
      /end-free

     C/EXEC SQL
     C+ insert into rob/thatfile
     C+ 25 rows
     C+ values (:testary)
     C/END-EXEC

And this worked also:
       x=5;
      /end-free

     C/EXEC SQL
     C+ insert into rob/thatfile
     C+ :x rows
     C+ values (:testary)
     C/END-EXEC

Be warned.  I tried not setting x to 5 and the value of x at the sql
time 
was set to 26.  Just a minor thing but don't assume that x is equal to
25 
at the end of the FOR loop.  If that's what you want then try:
x=%elem(TestAry);
Just prior to the INSERT.

Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"Wilt, Charles" <CWilt@xxxxxxxxxxxx> 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
02/17/2005 09:10 AM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
"RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
cc

Subject
RE: Using Array Values in Insert Query ?






The better way of doing this would be to insert the entire block of rows

at once.

c/exec SQL
c+  insert into luqman/cont_item
c+    25 rows values (:CITEMSTR)
c/end-exec


Note that your DS needs to be a Multiple Occurrence DS and not an array
DS 
(unless you are at v5r3 ???).

     D CITEMSTR        DS                  occurs(25)
     D  MCN2                         15
     D  MSN                           5I 0
     D  MACODE1                      10
     D  MQTY1                         5I 0
     D  MPRICE1                      12  2
     D  MAMOUNT1                     12  2


note the use of the OCCURS keyword to create a Multiple Occurrence DS 
(MODS) as opposed to the use of DIM to create an array DS.

HTH,


Charles Wilt
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 

> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Luqman
> Sent: Thursday, February 17, 2005 5:31 AM
> To: rpg400-l@xxxxxxxxxxxx
> Subject: Using Array Values in Insert Query ?
> 
> 
> How can I use the Array Values in Insert Query.
> 
> I tried following but its giving error.
> 
> 0138.04 C                   FOR       Z=1 TO 5
> 0138.06 C/EXEC SQL
> 0138.07 C+ INSERT INTO LUQMAN/CONT_ITEM
> 0138.08 C+
> VALUES(:MCN2(Z),:MSN(Z),:MACODE1(Z),:MQTY1(Z),:MRATE1(Z),:MAMOUNT1(Z))
> 0138.09 C/END-EXEC
> 0138.10 C                   ENDFOR
> 
> Best Regards,
> 
> Luqman
> 
> 
> 
> 
> -- 
> This is the RPG programming on the AS400 / iSeries (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 AS400 / iSeries (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 AS400 / iSeries (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.