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 */
/* no error in Plant Transaction; indicate xact is complete */
COMMIT /* transaction completed; make it so */
monmsg cpf0000 exec(callprc BadThing) /* Eeek! The horror! */

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 */
/* no error in Plant Transaction; indicate xact is complete */
callprc PlantXactCompleted (&plantcode) rtvnval(&pxcsts)
if (&pxcsts *ne 1) then(callprc BadThing) /* Eeek! The horror!*/

Regards, Chuck

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 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].