Re: SQL in CL.

["Mark S. Waterbury" <mark.s.waterbury@xxxxxxx>]

RUNSQLQRY is essentially the same as "EXCSQL" ... both work in essentially 
the same way, invoking a "generic" *QMQRY consisting of all substitution 
variables for the SQL statement ... both support output to *, *PRINT or 
*OUTFILE ... ;-)  Written by different folks at different times, but based 
on the same idea ... to "leverage" RUNQMQRY, esp. for sites that don't have 
the SQL Development Tools (e.g. STRSQL) installed.

----- Original Message ----- 
From: <vhamberg@xxxxxxxxxxx>
To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Sent: Friday, April 21, 2006 5:04 PM
Subject: Re: SQL in CL.


> Jake, are you talking RUNSQLSTM or RUNSQLQRY? I just tried but could not 
> find the latter in Google. You'd need to check the DBG400 site but EXCSQL 
> is a command and a couple programs and maybe a file or two, I am guessing. 
> It'd be in its own library, probably. MC Press used to have source for a 
> similar utility, I've written my own, we have commands like it in several 
> of our products.
>
> RUNSQLSTM is for running SQL statements. It can do most statements but not 
> SELECTs. You can put multiple statements into a single source member, so 
> it is quite convenient for building a database - creating tables, views, 
> indexes, adding records, etc. But you cannot use substitution variables - 
> the main use of that would be in SELECTs anyway, where you might want to 
> change the compare values in the WHERE clause. EXCSQL executes a statement 
> you build on the fly, sort of like for QCMDEXC, and regular QMQRY, on 
> which it is based, also lets you do this in a fashion tailored to your 
> business.
>
> HTH
> Vern
>
> -------------- Original message -------------- 
> From: "Jake M" <jakeroc@xxxxxxxxx>
>
> > 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 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"
> > >
> > > > 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.
> > >
> > >
> > >
> > >
> > --
> > 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.