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



I kind of have a personal vow to never use OPNQRYF in a new program again.
If I even begin to perceive it might have use I will use a different language, like RPG and use imbedded SQL.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Mark Waterbury
Sent: Wednesday, March 27, 2019 11:19 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Storing SQL script options

Hi, Rob,

Most often, when people want to do this (use "embedded SQL" in CL), it is because they want to write some "quick" utility to rifle through some selected rows and "do something" -- e.g. issue some CL commands, right?


You can accomplish this today in CLP or CLLE today ...  here's how ...

https://code.midrange.com/0814749817.html


In this example, I use the IBM-supplied RUNSQL command; you could alter this to work with any of the various RUNSQLxxx commands that are readily available.


Notice that, on the OPNQRYF command, I specify the KEYFLD parameter to specify the order, because SQL VIEWs do not support the "ORDER BY" clause.  (If you do not care about the order that rows are returned and processed, you can omit that parameter.)


NOTE: for DCLF to compile correctly, you need to create the SQL VIEW in QTEMP at (or before) "compile-time."   This can easily be accomplished by using Alan Campin's COMPILE command that allows you to place commands in the source code as specially formatted comments, so you can control exactly what happens when you "COMPILE" a source member ... you can find it at http://www.think400.dk/downloads.htm ; :)   Scott Klement also has a similar "pre-compiler" tool.  And there are several others out there, as well.


Enjoy!
Mark S. Waterbury


On Wednesday, March 27, 2019, 10:44:41 AM EDT, Rob Berendt <rob@xxxxxxxxx> wrote:

<snip>
In my view, rather than spend $0.10 on updating RUNSQLSTM I'd much rather IBM put an SQL precompiler into CLLE, (AKA like every other language has) so there is a CRTSQLCLE command.
</snip>

IBM declines that request.
https://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=98857


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Jim Oberholtzer
Sent: Wednesday, March 27, 2019 9:51 AM
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: Storing SQL script options

Don’t' forget that you can always call the 'db2' interface from CL as well. 

Select into a temporary file anywhere, then process that file as you wish:

CHGVAR &Command ('db2 "' *cat &Command *tcat '"') QSH cmd(&Command) 

Clearly the SQL goes into the variable &Command

There's a small bit of set up, mainly to push the spool files to a null device etc. but it's easy to do, been around for quite some time, and costs nothing.  I use it on V7R1 and earlier machines where the other tools do not exist.

In my view, rather than spend $0.10 on updating RUNSQLSTM I'd much rather IBM put an SQL precompiler into CLLE, (AKA like every other language has) so there is a CRTSQLCLE command.


--
Jim Oberholtzer
Agile Technology Architects

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Steinmetz, Paul via MIDRANGE-L
Sent: Wednesday, March 27, 2019 8:31 AM
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: Steinmetz, Paul <PSteinmetz@xxxxxxxxxx>
Subject: RE: Storing SQL script options

We're using a version of RUNSQL, supplied by one of our 3rd party vendors, that does do SELECTS, and allows the 3 output options by changing your session defaults.

Paul

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Vernon Hamberg
Sent: Wednesday, March 27, 2019 9:21 AM
To: Midrange Systems Technical Discussion
Subject: Re: Storing SQL script options

As a bit more info - the IBM RUNSQL also does not do SELECT statements.

Vern

On 3/27/2019 8:13 AM, Vernon Hamberg wrote:
Paul

There is no option for output for SELECT statements, because SELECT
statements are not allowed in RUNSQLSTM. I've asked innumerable times
for this to be added, but IBM are not going to do it. I really
disagree with this but see no change coming.

If you want to run a SELECT with output to printer or display, get one
of the commands we in the wild have written that are using Query
Management - they usually end up using STRQMQRY, which has an OUTPUT
parameter.

I have one, and I'm giving a presentation at COMMON this year on using
Query Management, as well as a lab. I think Buck Calabro has one, I
kind of recall that I based mine on his some many years ago. These
often use a *QMQRY object that has a "statement" that is a single
substitution variable for the entire statement.

Google for RUNSQL (not the new IBM command) or EXCSQL, you should find
something.

Cheers
Vern

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com

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.