Getting RUNSQL custom to return a value to a CL Variable
Hi All,
We created a standard version of the RUNSQL command to allow us to exectue SQL statements in CL programs. What I want is to run a select statement over a file for a specific key value and have it return the selected field(s) to locally declared CL Variables so I can use these values for subsequent SQL statements...
I am sure I could dump my results into an outfile in QTEMP and read that but was hoping to make it a little cleaner...and not have to call another program/procedure etc to get me the data elements.
Any ideas?
Thanks,
Rayden.
Software/Hardware used:
Software/Hardware used:
ASKED:
February 16, 2006 1:32 PM
UPDATED:
November 30, 2009 3:38 AM
Answer Wiki:
to have something that externally update a cl field, this thing must be embedded in a *CMD that is designed to update the filed(s).
need a sample ? look particularly at PARM KWD(RTNLIB) TYPE(*CHAR) LEN(10) RTNVAL(*YES) PROMPT('Variable pour RTNLIB (10)') from http://jplamontre.free.fr/AS400/FINDMBR.htm
you have to add some parameters to your RUNSQL.
I never have try to put a list of variables into a RTNVAL(*YES) parameter.
nota : What to do when select returns more than one line ? return the first ? the last ? return an error ?
It seem to me you are not using the good language. Have a look at stored procedure or an embbed sql in rpg (or cobol or c), it would be very easier.
==============================================================
A *CMD has no relationship to whether a value can be returned or not. If the environment can return a value, it can be returned in an output parameter. If a return value isn't allowed, it won't be allowed in a *CMD parm. Note that CALL is a *CMD that allows return values in parms. A *CMD different from CALL won't make it work any better.
The problem is that a standard generic RUNSQL has no way to execute a SELECT (FETCH) that can return values at all. An embedded SQL program will be required. The values returned from any form of SELECT have to be placed into variables that are declared in the program.
One possibility might be a REXX proc that can rewrite itself as it runs. A SQLDA might be auto-generated to receive the FETCHed (<b>not</b> SELECTed) values. The values could be returned through the REXX external queue.
Tom
To see all answers submitted to the Answer Wiki: View Answer History.
Hi,
I have a similar problem. I need to run a CL command in my SQL script. I can think of many ways to accomplish this but I am looking on a ‘clean’ solution. I want to remove a trigger before SQL zaps the file.
Thank you for your help.
Ash
To run a CL cmd in a SQL script you have to do 2 things :
1) create a stored proc that calls a cl:
CREATE PROCEDURE JPL/SYSCMD(
IN AS400COMMANDE CHARACTER (6000 )
)
LANGUAGE CL
NOT DETERMINISTIC
NO SQL
EXTERNAL NAME JPL/JPLSYSCMD
PARAMETER STYLE GENERAL
(put it in your work library, here it is JPL)
2) mke the corresponding CL :
PGM &cmd
DCL VAR(&CMD) TYPE(*CHAR) LEN(6000)
/*===========GESTION-DES-MESSAGES-D’ERREUR————————–*/
DCL VAR(&MSGID) TYPE(*CHAR) LEN(7)
DCL VAR(&MSGDTA) TYPE(*CHAR) LEN(512)
DCL VAR(&MSGF) TYPE(*CHAR) LEN(10)
DCL VAR(&MSGFLIB) TYPE(*CHAR) LEN(10)
/*-RENVOI-DES-MESSAGES-VERS-L’APPELANT——————*/
MONMSG MSGID(CPF0000) EXEC(GOTO CMDLBL(STDMONMSG))
GOTO CMDLBL(DEBUTPGM)
STDMONMSG:
/*———–CAPTURE-DU-MESSAGE———————-*/
RCVMSG MSGTYPE(*LAST) RMV(*NO) MSGDTA(&MSGDTA) +
MSGID(&MSGID) MSGF(&MSGF) +
SNDMSGFLIB(&MSGFLIB)
/*———–PROMOTION-DU-MESSAGE——————–*/
SNDPGMMSG MSGID(&MSGID) MSGF(&MSGFLIB/&MSGF) +
MSGDTA(&MSGDTA) MSGTYPE(*ESCAPE)
RETURN
DEBUTPGM:
/*===========CORPS-DU-PROGRAMME————————————-*/
CALL PGM(QCMDEXC) PARM(&CMD 6000)
FIN: ENDPGM
Now, when running SQL you can execute :
CALL SYSCMD (‘CLRPFM alib/afile’)