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

Have you considered using RTVMSG? Used to use this technique to setup the
QRYSLT parameter of the OPNQRYF command .


Tweak your message to as needed to get the paramtere lengths and quotes
correct.

ADDMSGD MSGID(TMP0001)
MSGF(mylib/mymsgf)
MSG('SQL STRING for my CL program.')
SECLVL('UPDATE TESTFILE SET LG_ERRMSG=''&1'' LG_ERPATH=''&2''
WHERE LG_DATE=&3 AND LG_TIME=&4 AND LG_ID=''&5''
AND LG_SEQ=''&6'' AND
LG_CLIENT=''&7''')
FMT((*CHAR 7) (*CHAR 10) (*CHAR 10) (*CHAR 10) (*CHAR 10) (*CHAR
10) (*CHAR 10))


And to use the message your code would look like this:

pgm

DCL VAR(&SQLCMD) TYPE(*CHAR) LEN(256)

/* variable
*/

DCL VAR(&p_ERRMSG) TYPE(*CHAR) LEN( 7)
value('ERR0001')
DCL VAR(&p_PATH ) TYPE(*CHAR) LEN(10)
VALUE('aaaaaaaaaa')
DCL VAR(&CUR_YYYYMD) TYPE(*CHAR) LEN(10)
value('2012-03-30')
DCL VAR(&CUR_HHMMSS) TYPE(*CHAR) LEN(10)
value('15:30:00.0')
DCL VAR(&p_ID ) TYPE(*CHAR) LEN(10)
value('ididididid')
DCL VAR(&p_SEQ ) TYPE(*CHAR) LEN(10)
value('SSSSSSSSSS')
DCL VAR(&p_CLIENT) TYPE(*CHAR) LEN(10)
value('CCCCCCCCCC')


/* data structure to pass as message
data*/

DCL VAR(&MSGDTA) TYPE(*CHAR)
LEN(67)
DCL VAR(&M_ERRMSG) TYPE(*CHAR) STG(*DEFINED) LEN( 7)
DEFVAR(&MSGDTA 1)
DCL VAR(&M_PATH ) TYPE(*CHAR) STG(*DEFINED) LEN(10)
DEFVAR(&MSGDTA 8)
DCL VAR(&M_YYYYMD) TYPE(*CHAR) STG(*DEFINED) LEN(10)
DEFVAR(&MSGDTA 18)
DCL VAR(&M_HHMMSS) TYPE(*CHAR) STG(*DEFINED) LEN(10)
DEFVAR(&MSGDTA 28)
DCL VAR(&M_ID ) TYPE(*CHAR) STG(*DEFINED) LEN(10)
DEFVAR(&MSGDTA 38)
DCL VAR(&M_SEQ ) TYPE(*CHAR) STG(*DEFINED) LEN(10)
DEFVAR(&MSGDTA 48)
DCL VAR(&M_CLIENT) TYPE(*CHAR) STG(*DEFINED) LEN(10)
DEFVAR(&MSGDTA 58)


/* set values of MSGDTA data structure to desired replacement
values */
CHGVAR VAR(&M_ERRMSG)
VALUE(&P_ERRMSG)
CHGVAR VAR(&M_PATH ) VALUE(&P_PATH
)
CHGVAR VAR(&M_YYYYMD)
VALUE(&CUR_YYYYMD)
CHGVAR VAR(&M_HHMMSS)
VALUE(&CUR_HHMMSS)
CHGVAR VAR(&M_ID ) VALUE(&P_ID
)
CHGVAR VAR(&M_SEQ ) VALUE(&P_SEQ
)
CHGVAR VAR(&M_CLIENT)
VALUE(&P_CLIENT)


/* retrieve formatted string from second level text */
RTVMSG MSGID(TMP0001) MSGF(mysgf) MSGDTA(&MSGDTA)
SECLVL(&SQLCMD)


SNDPGMMSG MSG(&SQLCMD) TOPGMQ(*EXT) MSGTYPE(*DIAG)
/* DOCSQL &SQLCMD would go here */

endpgm