Re: SQL in CL. -- MIDRANGE-L

if I want to use EXCSQL, does it have to be special installation? Mark
Waterbury helped me to run the RUNSQLQRY on my iSeries and I love the little
tool. EXCSQL seems like a similar tool, am I right in assuming that? Please
feel free to correct me if I am wrong.

I appreciate all the help.

Jake.

On 4/21/06, vhamberg@xxxxxxxxxxx <vhamberg@xxxxxxxxxxx> wrote:
>
> EXCSQL is a great utility - it uses all generic substitution variables for
> QMQRY. Problem is the same as someone else's suggestion - no way to link the
> results to QMFORM, that I know of easily. So the reporting requirement is
> not handled with this tool.
>
> Regards
> Vern
> -------------- Original message --------------
> From: "Andy Leisk" <ALeisk@xxxxxxxxx>
>
> > Jake;
> > The DBG400 web site which was mentioned in another post to this list a
> few days
> > ago, has something that might do what you want. Take a look at:
> > http://dbg400.net/excsql.html
> >
> >
> > Andy Leisk
> > 920-751-4434
> > -----Original Message-----
> > From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
> midrange-l-bounces@xxxxxxxxxxxx]
> > On Behalf Of vhamberg@xxxxxxxxxxx
> > Sent: Wednesday, April 19, 2006 2:09 PM
> > To: Midrange Systems Technical Discussion
> > Subject: Re: SQL in CL.
> >
> > Hi Jake
> >
> > You might not have the right source member specified when you take its
> default.
> >
> > QMQRY & QMFORM are just about perfect for what you want to do. First, do
> you
> > have the SQL licensed product on your system? You can check by running
> either
> > STRSQL or STRQM (you've probably not seen the latter command).
> >
> > Mark had a suggestion for a generic SQL command processor using QMQRY,
> but I
> > don't think it's what you want - you say now that you want a report, and
> the
> > generic query would be impossible to line up with a report.
> >
> > So you want to go to STRQM. Option 1 lets you create QMQRYs. There is a
> user
> > guide/tutorial in the manuals at www.iseries.ibm.com/infocenter - look
> under
> > database for printable manuals. This will give you a good intro to the
> Query
> > Manager (STRQM) and does not take too long. The QMFORM will give you
> great
> > layout possibilities.
> >
> > Looks like Birgitta already gave you some of this information - hope I
> am not
> > too repetitive. One thing, in a QMFORM you can put in date and time and
> page
> > number and all that. If you do that, I recommend you turn off the
> automatic
> > printing of those items that is set on the STRQMQRY command.
> >
> > Also, watch out for the width of the report - you might need to OVRPRTF
> QPQXPRTF
> > PAGESIZE(*N 132) e.g. - check the pagesize of QPQXPRTF - I think it's
> 80. The
> > nice thing about QM is, a report that is too wide will get printed in
> its
> > entirety in multiple spooled files. Query/400 just truncates.
> >
> > I think QMQRY/QMFORM in combination with a CL command and CPP are
> fantastic for
> > writing quick report apps.
> >
> > HTH
> > Vern
> > -------------- Original message --------------
> > From: "Jake M"
> >
> > > 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 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 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.
> > > >
> > > >
> > > --
> > > 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.
>
>
>
>