RE: Is there a better way to run SQL in a CL pgm?

["Stone, Joel" <Joel.Stone@xxxxxxxxxx>]

Agreed!  There should be a better way to build SQL than:


I don't think our employers want us to waste time debugging this simple stuff with all the quotes, but there seems to be no better method.

You should see this with '''' instead of &qt - really ugly!


        CHGVAR     &SQLCMD ('UPDATE TESTFILE                        + 
 SET   LG_ERRMSG = ' *cat &QT *cat &P_ERRMSG   *cat &QT_C  *cat   + 
      'LG_PATH   = ' *cat &QT *cat &P_PATH     *cat &QT    *cat   + 
                                                                  + 
'WHERE LG_DATE   = ' *cat &CUR_YYYYMD *tcat '                     + 
 AND   LG_TIME   = ' *cat &CUR_HHMMSS *tcat '                     + 
 AND   LG_ID     = ' *cat &QT *cat &P_ID     *tcat &QT *tcat '    + 
 AND   LG_SEQ    = ' *cat &QT *cat &P_SEQ    *tcat &QT *tcat '    + 
 AND   LG_CLIENT = ' *cat &QT *cat &P_CLIENT *tcat &QT)             
        RUNSQL     (&SQLCMD)                                        



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of John Yeung
Sent: Friday, March 30, 2012 11:03 AM
To: Midrange Systems Technical Discussion
Subject: Re: Is there a better way to run SQL in a CL pgm?

On Fri, Mar 30, 2012 at 10:28 AM, Michael Schutte <mschutte369@xxxxxxxxx> wrote:
> Has anyone suggested STRQM and STRQMQRY?
>  You can pass parameters in the STRQMQRY command.
>
> i.e.
>
> CHGVAR &LIB2 ('''' *tcat &LIB *tcat '''')
> CHGVAR &FIL2 ('''' *tcat &FIL *tcat '''')
> OVRPRTF    FILE(QPQXPRTF) PAGESIZE(*N 132)
> STRQMQRY   QMQRY(MSCHUTTE/FLDDEF)  +
>           SETVAR((FILE &FIL2) (LIBRARY &LIB2))
>
> Just a thought.

Well, the OP's issue is not which command or tool to execute the SQL.
It's how to avoid CL's clunky string handling.  Your &LIB2 and &FIL2
show just how ugly it is to compose even the simplest of strings in
CL, especially if they need to contain single-quotes.

John