Why would I want to execute SQL from a CLP? SQL provides a powerful alternative to database management commands: you can create and maintain Libraries, files, views, authorities using SQL. Sometimes, you need to manipulate data in your CL program, and while an RPG program can do the same, it may be better to hold all logic in a single place - the CL program.
The attached source describes how to create a command (*CMD) called EXECSQL that will execute an SQL string right from your CLP.
There are two sources involved: A CMD called EXECSQL and a REXX program called EXECSQL.
Code for the REXX program:
Place in QREXSRC in a library of your choice.
/*------------ Start of REXX ------*/
/* By Shalom Carmel, 2001 */
/* This REXX script is the CPP of EXECSQL command */
/* It's purpose is the arbitrary execution of SQL code in a CLP program */
/* */
/* */
signal on error name command_error
signal on failure name command_error
signal on syntax name command_error
parse arg lower 'STMT(' QUOTE_SQL_statement ')'
parse arg 'COMMIT(' CommitControl ')'
select
when CommitControl='*CHG' then Commitment='UR'
when CommitControl='*UR' then Commitment='UR'
when CommitControl='*ALL' then Commitment='RS'
when CommitControl='*RS' then Commitment='RS'
when CommitControl='*RC' then Commitment='RC'
when CommitControl='*RR' then Commitment='RR'
when CommitControl='*NONE' then Commitment='NC'
when CommitControl='*NC' then Commitment='NC'
otherwise Commitment='NC'
end
interpret 'SQL_statement= ' QUOTE_SQL_statement
ADDRESS EXECSQL
'EXECSQL SET TRANSACTION ISOLATION LEVEL ' Commitment
'EXECSQL ' SQL_statement
say 'EXECSQL ' SQL_statement
exit
/********************************************************************/
/* command_error : ERROR & FAILURE condition trap */
/********************************************************************/
/* The RC variable contains the actual error message from the AS400 system */
/* This routine can be customized for the specific implementation */
command_error:
parse source _system _start _srcmbr _srcfile _srclib
/* Available SQL information:
SQLCODE
SQLERRMC
SQLERRP
SQLERRD.1
SQLERRD.2
SQLERRD.3
SQLERRD.4
SQLERRD.5
SQLERRD.6
SQLSTATE */
errmsg = _srcmbr 'from' _srclib'/'_srcfile 'has failed: SQLCODE ' SQLCODE
ADDRESS COMMAND
'SNDPGMMSG MSGID(cpf9899) MSGF(QCPFMSG) TOPGMQ(*PRV) MSGTYPE(*info) '
'SNDPGMMSG MSGID(cpf9897) MSGF(QCPFMSG) TOPGMQ(*PRV) MSGTYPE(*escape) ' ,
'MSGDTA('''errmsg''')'
exit
/*-------- End of REXX -----------*/
Code for the CMD definition:
Place in QCMDSRC in a library of your choice.
To create the command, run the following statement:
CRTCMD CMD(mylib/EXECSQL)
PGM(*REXX)
SRCFILE(mylib/QCMDSRC)
SRCMBR(EXECSQL)
REXSRCFILE(mylib/QREXSRC)
REXCMDENV(*EXECSQL)
REPLACE(*YES)
/*--------- Start of CMD-----------*/
EXECSQL: CMD PROMPT('Execute SQL from CLP ')
PARM KWD(STMT) TYPE(*CHAR) LEN(400) MIN(1) +
CASE(*MIXED) PROMPT('SQL command')
PARM KWD(COMMIT) TYPE(*CHAR) RSTD(*YES) DFT(*CHG) +
SPCVAL((*CHG UR) (*NONE NC) (*ALL RS) +
(*UR UR) (*NC NC) (*RC RC) (*RR +
RR) (*RS RS) ) PROMPT('Commitment Control')
/*----- End of CMD -----------*/
This was first published in November 2001