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