Re: Using Array Values in Insert Query ?

["Luqman" <pearlsoft@xxxxxxxxxxxx>]

Thats really nice of you, all of you given me  very beneficial solutions. I
really appreciate.

One thing I would like to clarify by myself that its not necessary that I
have to Insert 25 records all the time, it could be even 1 or 2 records, so
how can I check this and put that number in Insert Block Statement.

For example: say I have just 2 records to be inserted out of 25 and
remaining 23 array elements are not used, will Insert Query detect this
automatically or I have to put number of rows to be inserted.

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


Best Regards,

Luqman



<SRamanujan@xxxxxxxxxxxxxxxxxx> wrote in
message news:FAC2A70D63CFF249BFE0291EE9001E52024238FB@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.
>
>
> --
> 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.
>
>