On 02-Apr-2012 14:21 , Stone, Joel wrote:
CRPence on Monday, April 02, 2012 3:27 PM wrote:
<<SNIP>> mostly the SQL attempts from CL probably would just be
replaced with call(s) to HLL SQL programs or utilities. Deferred
to programs with the desired embedded or dynamic SQL statement(s)
coded, where the variables already are a known SQLTYPE, there is
no need for the CL to delimit the strings. <<SNIP>>
OK it sounds like you are stating that there is no need to call SQL
stmts from CL, rather you are suggesting to call an HLL pgm with
embedded SQL.
Not so much making a suggestion as simply conceding what is or is
often determined to be, a generally better choice for implementing SQL
work; and for various reasons, beyond just escaping apostrophes. Some
initially perceived benefits from being able to have effected the work
completely and dynamically within the CLP, will often surrender to a
realization that the specific work would best be accomplished by another
means. Accomplished more efficiently [in both coding and run-time],
within a good design\structure\model, and under source control from
which better cross-reference\where-used and reuse can be achieved.
Even so, I do accept that in some cases, an SQL statement may indeed
be better to have coded directly in the CLP as part of some brute\grunt
work; e.g. I often did so, for implementing "operator" tasks, though
mostly just because that was informal non-production software. And I do
prefer having a simple means to perform some ad-hoc SQL work from a CL
command line or in a CLP; obviously, since I have created my own
RUNSQL-like features in the past. However such CL tools are not meant
to enable undermining good design, bypassing change management
controls\intentions, or supplanting work via CLP which best fits
elsewhere; i.e. some of, for what I had alluded that "mostly the SQL
attempts from CL probably would just be replaced" with tooling that
avoids composing the SQL statement strings directly in the CLP.
Either when such tooling allows for the CL to provide the SQL
statement string still somewhat directly with some means to avoid the
effort to deal with escaping apostrophes using CL string handling, or
perhaps just because the statements being sent to such tooling rarely
have to deal with escaping apostrophes, the conspicuousness of the value
in conceding that the CLP is a poor choice for encapsulating the SQL is
diminished. That is why my RUNSQL-like variations explicitly dealt with
the escaping issue... and why I had hoped that the SQL would have just
exposed the quotation mark as string-delimiter with a SET OPTION, so as
to enable avoiding having to code solutions myself... and reason why I
ended up mostly just using REXX instead of a CLP more generally whenever
I wanted to do SQL from a CLP, just as for whenever I had wanted to
manipulate strings within a CLP.
I run into situations such as the following:
Records are waiting in a file to be sent out EDI or XML with a status
code "N" (not yet sent).
Let's say the records have now been picked up and sent on their way.
It seems that a simple and effective and self-documenting method of
flipping the flags to sent=Y would be
RUNSQL ('update transactionfile set processed-flag = ''Y'' where
processed-flag = ''N'' and plant-code = ' || &plantCode ')
(for an overly simple example)
IMO the presented scenario should be reviewed for what appears to be
a problem with the design. Whatever process had some rows "picked up
and sent on their way" should have updated the "processed-flag" within
that one unit of work; i.e. effectively as one transaction.
And FWiW, as a "simple" example, there is already a problem.
Ignoring the invalid column names, the CL string is not properly formed,
and perhaps neither the SQL. With that alone, the argument for a CLP is
not so great IMO; i.e. apparently not nearly so simple as alluded.? ;-)
Seriously... Similarly, that the "Subject:" question was even asked
with the original example and further expounded on, having suggested how
much "a nightmare trying to get all the quotes, double quotes, + CL
continuations, etc just right" is for using the SQL in a CLP. I thought
that also, had already sufficiently implied why such CL commands are not
a preferred option for performing the SQL in a CLP.
The simple response to any comment suggesting that "There should be a
better way" is a suggestion to avoid the CL to do what is already a
better fit with parameterized dynamic or embedded SQL in a HLL with
precompiler support or similar [e.g. REXX, QSQPRCED, SQL CLI, or even a
QMQRY could more easily implement the above SQL]. In consideration of
extending an existing RUNSQL-like command to work more like embedded or
dynamic from within a CLP [i.e. variables], might just have one thinking
about those as alternatives. Again, conceding that the CLP for the
fully dynamic SQL statement might simply be a poor choice.
Isn't this more desirable than embedding in an HLL pgm when no RPG
pgm is needed?
IMO that statement is a reflection of a conclusion that "no RPG pgm
is needed" directing an implementation rather than the reflection of an
actual design allowing any implementation. That is, there is already
the desire to avoid coding a separate program becomes there is a desire
to keep the SQL in the CLP, rather than implementing a design for which
a program with embedded SQL might be the most appropriate. So IMO, no,
that CL request is not more desirable than a separate object [i.e. not
necessarily limited to a program object nor embedded] to encapsulate the
SQL.
Typically transactions are not being performed within the CLP;
instead, just invoked from the CL. Invoked by the CL, the means to
indicate the transaction has been completed would probably be part of
the same CL request that invoked them; i.e. the work should be atomic,
rather than requiring an after-the-fact reactive means to indicate the
outcome. If deference to the CL was actually appropriate, then the CL
would best do no more than ROLLBACK or COMMIT the work which already had
updated the status.
XACTPLANT PLANTCODE(&PlantCode) /* perform Plant Transaction */
monmsg xct##00 exec(do) /* Error in perform Plant Transaction */
ROLLBACK /* discard partial transaction */
callprc BadThing /* Notify of error via std mechanism */
return /* do not continue processing */
enddo
/* no error in Plant Transaction; indicate xact is complete */
COMMIT /* transaction completed; make it so */
monmsg cpf0000 exec(callprc BadThing) /* Eeek! The horror! */
return
Even if the CL might instead do the noted SQL update as reactionary
work instead of either effecting the atomic work or finalizing the
atomicity of the work, then I would argue the following is more
desirable [where the SQL UPDATE has been encapsulated in a procedure as
business logic, callable from any ILE programs, rather than coded as
purely dynamic SQL in a CLP]:
XACTPLANT PLANTCODE(&PlantCode) /* perform Plant Transaction */
monmsg xct##00 exec(do) /* Error in perform Plant Transaction */
callprc BadThing /* Notify of error via std mechanism */
return /* do not continue processing */
enddo
/* no error in Plant Transaction; indicate xact is complete */
callprc PlantXactCompleted (&plantcode) rtvnval(&pxcsts)
if (&pxcsts *ne 1) then(callprc BadThing) /* Eeek! The horror!*/
return
Regards, Chuck
midrange.com
