RE: SQL in CL. -- MIDRANGE-L

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