On 2/15/2018 3:14 PM, Gord Hutchinson wrote:
Is it possible to call an sql procedure from within a CL program/module and
return a value?
I'm envisioning using SQL to extract something from a table and having CL
do something depending on what was extracted.
Like CL, REXX comes installed with every copy of IBM i.
REXX can do many SQL functions.
It's not CALL/PARM, but you can pass data between CL and REXX.
Chuck Pence provided many valuable examples in the archives.
Putting those bits together:
CLP SQLTEST2:
pgm
dcl &date *char 10
dcl &len *int (4) value(10)
dcl &rc *int (2)
/* execute the SQL statement */
strrexprc sqltest2 srcfile(buck/qrexsrc)
call QREXQ ('P' &date &len 0 &rc)
dmpclpgm
endpgm
=======================================
QREXSRC(SQLTEST2):
/* SQLTEST2 */
/* example of using SQL to pass a value back to a CL program */
signal on error name command_error
signal on failure name command_error
signal on syntax name syntax_error
/* SQL runs here... */
/* VALUES INTO does NOT work... no error and no return value */
/* this is apparently recognised by IBM and is a 'will not fix' situation */
/* as of 540, Nov 2009 */
address '*EXECSQL'
PRVDATE = '0001-01-01'
sql_Stmt = ,
'select CHAR(DATE(CURRENT_DATE - 15 DAYS)) ',
' from sysibm/sysdummy1'
'execsql SET OPTION DATFMT = *ISO '
'execsql PREPARE S2 FROM :sql_Stmt '
'execsql DECLARE C1 CURSOR FOR S2'
'execsql OPEN C1 '
'execsql FETCH C1 INTO :PRVDATE'
say PRVDATE
'execsql CLOSE C1'
/* put the value on the external queue */
/* This will allow the calling CLP to retrieve it via */
/* CALL QREXQ PARM('P' &DATE &LEN 0 &RC) */
queue (PRVDATE)
exit
/* ---------------------------------------------------------------- */
command_error:
trace off
parse source system start srcmbr srcfile srclib
say 'Unexpected error at line 'sigl' of REXX program ',
srcmbr' in 'srclib'/'srcfile'. The exception ID is 'rc'.'
exit(right(rc,4))
/* ---------------------------------------------------------------- */
syntax_error:
trace off
parse source system start srcmbr srcfile srclib
say 'Syntax error at line 'sigl' of REXX program ',
srcmbr' in 'srclib'/'srcfile'. The error code is 'rc'.',
' The description is :'
say errortext(rc)
exit(rc)
=======================================
Having provided all that, if you're going to go to this effort you might
as well write an SQLRPGLE sub-procedure that CL can call.
I myself would simply perform my 'CLP tasks' right in my REXX procedure.
An example of that:
ADDRESS EXECSQL
EXECSQL "SET OPTION COMMIT=*NONE"
EXECSQL "declare c1 cursor for ",
"select count(*) from systables ",
"where table_schema = 'QSYS'"
EXECSQL "open c1"
EXECSQL "fetch from c1 into :count"
if SQLCODE < 0 then SIGNAL ERROR
EXECSQL "close c1"
if count > 0 then do
MSGDTA='File has' strip(count) 'records'
ADDRESS COMMAND
"SNDPGMMSG MSGID(CPI8859) MSGF(QCPFMSG) MSGDTA(&MSGDTA) MSGTYPE(*COMP)"
end
EXIT
ERROR: say SQLCODE
=======================================
Having said /that/, if I were going to learn a new language, I would
definitely learn Python instead of REXX. There are way more examples,
way more places to get help, and Python comes with 5733-OPS. It is also
available as a QSYS.LIB port from
http://www.iseriespython.com
This might spark some discussion!
midrange.com
