Re: easier SQL statements in CL

On 17 May 2012 08:32, Mark S Waterbury wrote:

You could use the CL CALL command to implement a very simple syntax
for "embedded SQL" in CL programs, like this:

CALL SQL PARM( SELECT DISTINCT * FROM 'QGPL/QAUOOPT' )

Leverage what the CALL command provides by writing a little "SQL"
program that concatenates all of its arguments, (e.g. using *BCAT),
to form the SQL query string to pass to your RUNSQL command (or the
one now provided by IBM.)

To ease the use of single-quotes, use a "special value" of *QUOTE,
that tells the SQL program to "en-quote" the next argument ... so in
this case:

CALL PGM(SQL) PARM( SELECT * FROM 'QGPL/QAUOOPT' +
WHERE OPTION = *QUOTE &OPT )

the value of &OPT will have single-quotes around it in the query
string.

<<SNIP>>

While the idea may seem somewhat generally plausible, IMO that design fails too easily. As both the variety and complexity of the statements increase, many irritating issues\restrictions\nuances and straight-up problems would almost surely plague the user. Certainly what would be required is non-intuitive for having to delimit things that otherwise might not be just to maintain case, allow special characters, or just to allow them to be accepted by the command parser as arguments; or uppercase the normally non-delimited values because they are now passed as separate strings. And the ever unpleasant issue with the 32-byte allocation default for variables would probably get overlooked exceedingly often, resulting in unpredictable results just like so often when CALL is used with variables in place of *CMD parameters.

Regards, Chuck