Re: SQL in CL. -- MIDRANGE-L

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" <jakeroc@xxxxxxxxx> 

> 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. 
>