Re: SQL in CL. -- MIDRANGE-L

Jake:

See:
    http://www.iseriesnetwork.com/resources/code/misccode/sqlcode/
and:
    http://www.iseriesnetwork.com/resources/code/shareware/rtvcatlist_isn.cfm
or
    http://www.as400pro.com/servlet/sql.tipView?key=159&category=SQL

for several related alternatives.  The idea is to create a "generic" *QMQRY 
that consists entirely of parameters (e.g. &P1 &P2 &P3 etc.) and that you can 
run via the STRQMQRY command, passing the entire desired SQL statement as 
"parameters"... 

So, for example, in your CL program, it might look something like this ...

    PGM    PARM(&PARM1)
    DCL    &PARM1 *CHAR 10
    ...
    DCL &SQL *CHAR 500
    ...
    CHGVAR VAR(&SQL) VALUE('SELECT CSTNUM,  CSNAME,  CSADD1, +
        CSADD2, CSCTST, CSZIP FROM MYLIB.CSTMST WHERE CSTNUM = ''' +
        *CAT &PARM1 *CAT '''')
     ...
    RUNSQL STMT(&SQL)
    ...
    RETURN
    ENDPGM

I have seen some variations of the above RUNSQL command that can create an 
*OUTFILE containing the results, which you could then DCLF and RCVF row-by-row 
in your CL *PGM,
e.g. if you are running a SELECT and want to read the results-set.

Hope that helps ...

Mark S. Waterbury

----- Original Message ----- 
> From: "Jake M" <jakeroc@xxxxxxxxx>
> To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
> Sent: Wednesday, April 19, 2006 9:30 AM
> Subject: Re: SQL in CL.
>

> Vern, Scott,
> Thanks a lot for your suggestions. I appreciate it. Scott, you are
> absolutely right...it does make sense to post the right question in the
> right forum. I just did not know about the midrange-l. I will make sure I
> do
> that from here onwards. Now, back to the topic of discussion...hmmmm....so
> there is no way of embedding SQL in a CL program?
>
> This is my situation...I have a few(about 6 to 8) CL programs which take
> the
> user input via a DSPF and process those parm's against the dB files and
> enable the user an option to print the reports. The CL programs look
> something like the one that I already posted.
>
> I am trying to modify these CL programs so that when ever a user feeds in
> a
> parm through the .dspf I would take that parm and give it to a SQL
> statement
> in the CL program and then do the report processing...
>
> I appreciate all your suggestions. Thanks for the input.
>
> Jake.
>
> On 4/19/06, Scott Klement <midrange-l@xxxxxxxxxxxxxxxx> wrote:
>>
>>
>> > 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?
>>
>> In addition to Vern's excellent suggestions of QMQRY, REXX and OPNQRYF,
>> I'd like to add these:
>>
>> a) In V5R3 it's possible to call the CLI APIs from CL. This provides some
>> interesting possibilities.
>>
>> b) In earlier releases, you can run SQL via QShell commands. This can be
>> convienient, since you can build the SQL command on-the-fly.
>>
>> c) Some people advocate creating an RPG program that accepts an SQL
>> statement as a parameter, and then simply prepares and executes it. This
>> way you can call it from CL programs to perform SQL commands.  Of coruse,
>> this doesn't allow data to be returned (such as the result of a SELECT),
>> but you could have SQL write the results to a file and read it from CL
>> using RCVF.
>>
>> > I want to do something like
>> >
>> > (only an example SQL statement).
>> > 'SELECT CSTNUM,  CSNAME,  CSADD1, CSADD2, CSCTST, CSZIP from
>> MYLIB.CSTMST
>> > WHERE CSTNUM = 'PARM1';
>>
>> For that I'd probably use QMQRY.
>>
>> > I posted this question in rpg group but I was advised that it would be
>> more
>> > appropriate to post it here.
>>
>> I'm not sure if you understand why that is, so I'll explain.
>>
>> There are SQL programmers who are not RPG programers.  There are CL
>> programmers who are not RPG programmers.  It makes sense to post your
>> question in the correct forum.  Don't post Java questions in the COBOL
>> forum.  Don't post CL questions in the RPG forum.  It just makes sense!
>>
>> In addition to that, the archives of the mailing lists are very valuable
>> because when you're having a problem you can search them, find out what
>> other people have discussed, and often solve you problems in a few
>> minutes.  To keep these archives valuable, you have to know which lists
>> to
>> search.  For example, if I have a question about CL programming, it
>> wouldn't make sense for me to search the Java mailing list.  Therefore,
>> it's to everyone's benefit if you post your questions to the right lists.
>>
>> Remember, CL is a different language than RPG.  The correct forum for CL
>> discussions is MIDRANGE-L.  The correct forum for RPG discussions is
>> RPG400-L.
>>
>> --
>> 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.
>>
>>
> -- 
> 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.
>