Re: Embedded SQL in CL

["Robert J Blackburn" <blackburn_robert@xxxxxxxxxxxx>]

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