RE: Embedded SQL in CL

[Roger Boucher <RBoucher@xxxxxxxxxxx>]

Again, you may want to try a QMQRY.  It even accepts replacement
variables so that you would be able to insert anything you want just
about anywhere!

-----Original Message-----
From: Gary Lehman [mailto:Gary_Lehman@mail.mchcp.org]
Sent: Friday, December 18, 1998 7:14 AM
To: 'MIDRANGE-L@midrange.com'
Subject: RE: Embedded SQL in CL


Actually I want to insert a value into a field in a file inside the CL,
but
I didn't want to have to call another program(COBOL or RPG) to do it.  I
wanted to keep everything inside the CL itself.  Normally I would just
write
a little program, but I didn't want to create another object just to
insert
a date into a file.  Not to sound ignorant, but is there an easy way to
update a file from inside a CL program w/o calling another program?

-----Original Message-----
From: Robert J Blackburn [mailto:blackburn_robert@jpmorgan.com]
Sent: Friday, December 18, 1998 8:17 AM
To: MIDRANGE-L@midrange.com
Subject: Re: Embedded SQL in CL


We have created a RUNSQL command that uses logic that was published in
the
AS/400 PowerTools book.  The CPP breaks down the SQL string and passes
it
to query manager.  Below is the main part:

PGM        PARM(&LINES &TYPE &OUTFILE &OUTMBR &PRTFILE)

IF (&TYPE = '*OUTFILE') +
  THEN(DO)
    STRQMQRY  QMQRY(RUNSQLQ) OUTPUT(&TYPE)  +
              OUTFILE(&OLIB/&OFIL) OUTMBR(&OMBR  +
              &ORPL) ALWQRYDFN(*YES) SETVAR((LINE01 &LINE01) +
              (LINE02 &LINE02) (LINE03 &LINE03) +
              (LINE04 &LINE04) (LINE05 &LINE05) +
              (LINE06 &LINE06) (LINE07 &LINE07) +
              (LINE08 &LINE08) (LINE09 &LINE09) +
              (LINE10 &LINE10))
             MONMSG MSGID(QWM0000) EXEC(DO)
            SNDPGMMSG  MSGID(CPF9898) MSGF(QCPFMSG) +
            MSGDTA('Syntax Error in SQL Statement.  See Previous +
            messages for details.') MSGTYPE(*DIAG)
    ENDDO
ELSE    (DO)
  OVRPRTF   FILE(QPQXPRTF) TOFILE(&PLIB/&PFIL)
  STRQMQRY  QMQRY(RUNSQLQ) OUTPUT(&TYPE)  +
            ALWQRYDFN(*YES) SETVAR((LINE01 &LINE01) +
            (LINE02 &LINE02) (LINE03 &LINE03) +
            (LINE04 &LINE04) (LINE05 &LINE05) +
            (LINE06 &LINE06) (LINE07 &LINE07) +
            (LINE08 &LINE08) (LINE09 &LINE09) +
            (LINE10 &LINE10))
            MONMSG MSGID(QWM0000) EXEC(DO)
            SNDPGMMSG  MSGID(CPF9898) MSGF(QCPFMSG) +
            MSGDTA('Syntax Error in SQL Statement.  See Previous +
            messages for details.') MSGTYPE(*DIAG)
        ENDDO
  DLTOVR    FILE(QPQXPRTF)
  ENDDO

The QMQRY source is:

H QM4 05 Q 01 E V W E R 01 03 98/12/18 08:53
V 1001 050 Run SQL Variables
&LINE01&LINE02&LINE03&LINE04&LINE05&LINE06&LINE07&LINE08&LINE09&LINE10

The is better than calling an SQLRPGLE program.  But, If you are doing
an
INSERT with values, sometimes the RPG program is easier.

Bob


+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to
MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator:
david@midrange.com
+---
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to
MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator:
david@midrange.com
+---
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---