AW: SQL in CL.

["Hauser, Birgitta" <Birgitta.Hauser@xxxxxxxxxxx>]

Hi,

you might have a look at QMQuery and calling them from CL.
It's possible to pass parameters to QMQRY

Example QMQry:
Select Fld1, Fld2, Fld3, ... FldN                             
  from MyTable

  where    Fld1 = &PFLD1  and Fld7 = &PFld7 .... 
  order by Fld2, Fld4

&PFLD1 and &PFLD7 are variables that can be passed from the CL-Programm
(Fld1 is numeric and Fld7 is character)

CL-Programm:
PGM        PARM(&ParFld1 &ParFld7)
DCL        VAR(&ParFld1) TYPE(*DEC)  LEN(3 0)  
DCL        VAR(&QryFld1) TYPE(*CHAR) LEN(3) 
                                                            
DCL        VAR(&ParFld7)  TYPE(*CHAR) LEN(15) 
DCL        VAR(&QryFld7)  TYPE(*CHAR) LEN(17)

ChgVar     Var(&QryFld1) Value(&ParFld1)
ChgVar     Var(&QryFld7) Value('''' *cat &ParFld7 *Cat '''') 

StrQmQry   QmQry(MyLib/MyQMQRY)            +         
           Output(*PRINT)                  +         
           QmForm(*QMQRY)                  +         
           AlwQryDfn(*No)                  +         
           SetVar((PFld1   &QryFld1)       +         
                  (PFld7   &QryFld7))


Birgitta

"If you think education is expensive, try ignorance"
(Derek Bok)
 

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Jake M
Gesendet: Mittwoch, 19. April 2006 15:30
An: Midrange Systems Technical Discussion
Betreff: 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.
>
>