Re: SQL in CL. -- MIDRANGE-L

Birgitta,
Thanks a bunch for the reply. I was looking for exactly something like
that...But I am stuck a little bit though. The following command fails...

CRTQMQRY QMQRY(TESTING/MYQMQRY) SRCFILE(TESTING/QQMQRYSRC)

This is the step where I intend to create a file called MYQMQRY and place my
SQL statement in it. I do not know if there is any care to be taken about
the positioning of the statement but I was just going to put the following
in the file...

'SELECT CSTNUM, CSNAME FROM TESTING.CSTMST WHERE CSTNUM = &PFLD1'

My CLLE program would look like this..

             PGM        PARM(&PARFLD1)

             DCL        VAR(&PARFLD1) TYPE(*CHAR) LEN(10)
             DCL        VAR(&QRYFLD1) TYPE(*CHAR) LEN(10)

             ChgVar     Var(&QRYFLD1) Value('''' *cat &PARFLD1 *Cat '''')

             STRQMQRY   QmQry(TESTING/MYQMQRY) Output(*PRINT) QmForm(*QMQRY)
AlwQryDfn(*No) +
                          SetVar((PFLD1   &QRYFLD1))
             ENDPGM

Any help would be much appreciated...

Jake.

On 4/19/06, Hauser, Birgitta <Birgitta.Hauser@xxxxxxxxxxx> wrote:
>
> 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.
> >
> >
> --
> 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.
>
>