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

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].