Re: SQL in CL. -- MIDRANGE-L

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

This is possible but is a real PITA. You'd be better off in RPG orperhaps REXX, which does do embedded SQL. And very well, too. And aREXX procedure can be named as the command processor for a CL commandyou 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 acommand line and prompt it - you don't need to be in a program to useit. 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 toget basically and output file, or call an RPG program after anoverride, 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.