LOL!

Joel, you may know that our DOCSQL utitlity, which you are using, does use STRQMQRY - it is just so generic that you have to build up the whole string.

There are very nice ways to get quotes in the right places by using a custom *QMQRY - it's pretty easy - and one of the tricks is to use a substitution variable, similar to Michael's suggestion - but for a single quote - then you use that variable &QT, say, everywhere and it just gets handled. There have been other posts here about that - here's where I learned that trick.

Regards
Vern

On 3/30/2012 11:13 AM, Stone, Joel wrote:
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 ...

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].