× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.