Getting RUNSQL custom to return a value to a CL Variable

pts.
Tags:
AS/400
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Ashnirody
    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
    100 pointsBadges:
    report
  • JPLamontre
    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')
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following