Re: SQL in CL.

[Vernon Hamberg <vhamberg@xxxxxxxxxxx>]

No such thing as embedded SQL in CL. The closest thing, IMO, is QM 
queries. These are objects that essentially contain an SQL statement, 
including SELECTs. And they can have substitution variables, so you 
can have your PARM1. The command to run them is STRQMQRY, and it has 
display, print, or outfile output. You could use a DCLF and OVRDBF in 
the right places to read the file using RCVF.

This is possible but is a real PITA. You'd be better off in RPG or 
perhaps REXX, which does do embedded SQL. And very well, too. And a 
REXX procedure can be named as the command processor for a CL command 
you define.

RUNSQLSTM is not a good option, as you cannot pass variables to it. 
You could use an INSERT INTO statement after a CREATE TABLE, maybe, 
but you are still stuck with how to read the result set.

You might consider an oldie but goodie, OPNQRYF. Enter it on a 
command line and prompt it - you don't need to be in a program to use 
it. I don't remember, but I don't think you can use RCVF on it - 
someone correct me if that's wrong, please. You CAN do CPYFRMQRYF to 
get basically and output file, or call an RPG program after an 
override, to process the result set.

HTH
Vern


At 04:20 PM 4/18/2006, you wrote:

> Howdy All;
> I am a newbie and am still trying to learn RPG. I have a question regarding
> CL programming. Is embedded SQL supported in CL programs? If so, are SELECT
> statements supported?
>
> I want to do something like
>
> (only an example SQL statement).
> 'SELECT CSTNUM,  CSNAME,  CSADD1, CSADD2, CSCTST, CSZIP from MYLIB.CSTMST
> WHERE CSTNUM = 'PARM1';
>
> CSTNUM is a character field with length 10.
>
> Would I do a RUNSQLSTM in my CL program?
>
> I posted this question in rpg group but I was advised that it would be more
> appropriate to post it here. One of the members suggested to go the WRKQRY
> route but I already have CL programs which do the selection of fields by
> taking in a parameter 'PARM1' and then search the file's and bring back the
> results. I do not want to go the WRKQRY route. I just want to modify those
> existing CL's so that I can convert them into SQL in CL or something along
> those lines. The CL program's that I am working with are something like the
> following..
>
> ******************************
> *******************************************************************************************************************************
>              PGM
> /*                                                                   */
>              DCL        VAR(&MSG) TYPE(*CHAR) LEN(512) /* TEMP MESSAGE BUILT
> */
>              DCL        VAR(&MSGL) TYPE(*CHAR) LEN(4)  /* LENGTH OF MESSAGE
> */
>              DCL        VAR(&SMSG) TYPE(*CHAR) LEN(2000) /* IN-LINE QUERY
> MES */
>
>     /* ------------------------ */
>     /* BUILD MESSAGE..SINGLE PARM   */
>     /* ------------------------ */
>                      /* IF EXISTING MESSAGE IS NOT BLANK AND IN *ADD */
>                      /*    INTO MESSAGE                              */
>
>              IF         COND(&SMSG *NE ' ') THEN(DO)
>              CHGVAR     VAR(&SMSG) VALUE(&SMSG *BCAT '*AND ')
>                         ENDDO
>                                           /*  1      2    3     4  */
>              CALL       PGM(QRYSELP) PARM('CISTYP' '006' 'L' '0001' +
>                           '*NE' &MSG &MSGL)
>                         /*  5     6    7   */
>
>                        /* ATTACH CREATED MESSAGE ONTO EXISTING MESSAGE */
>              CHGVAR     VAR(&SMSG) VALUE(&SMSG *BCAT &MSG)
>
>              CALL @@BOMB@@        /* KILL CL AND TAKE A DUMP TO SEE RESULTS
> */
>  ENDPGM:     ENDPGM
>
>                  /* --------------------------------------------------*/
>                  /* DESIRED MESSAGE ..  (CISTYP *NE "L")              */
>                  /* --------------------------------------------------*/
>       /*              1.  DATABASE FIELD NAME                            +
>                       2.      LENGTH OF FIELD NAME (NOT FIELD LENGTH)    +
>                       3.  PARMATER TO BE PASSED  (MAX LNGTH 256)         +
>                       4.      LENGTH OF PARAMETER                        +
>                       5.  OPERATION PARM                                 +
>                           VALID PARMS   *EQ   *LT  *LE  *CT              +
>                                         *NE   *GT  *GE                   +
>                       6.  VARIABLE WHERE MESSAGE WILL BE PLACED          +
>                       7.  LENGTH OF VARIABLE                             */
>
>
> ***************************************************************************************************************************************************************
>
> I would really appreciate it if somebody could point me in the right
> direction.
>
> Thanks in advance.
>
> Jake.
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.