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

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