Since the SQL has support for SQL String Delimiter of *QUOTESQL for
the COBOL, one would have hoped that they would have finally exposed a
capability for SET OPTION SQLSTRDLM=*QUOTESQL for use from the CL,
having provided that CL command :-( I never understood why that feature
could not be made available generally, as an OPTION.? If standards were
an issue, that capability would be no worse than *SYS naming option.?
In similar command interfaces, I had dealt with the apostrophe issue
using various means of supporting replacement variables in the SQL
statement string, and having the CPP replace those /variables/ with
respective values passed in another parameter; e.g. RUNSQLSTMT
SQLSTMT('insert into qtemp/srcpf values(0000.01, 000000, :SRCDTASTR)')
SETVAR((SRCDTASTR *CHAR 100 *N &SRCDTACLV)).
In the article I noticed that the first INSERT statement example
using the RUNSQL, with delimited character literals specified in the
VALUES(), does not deal properly with those apostrophes as string
delimiter [per limited to the SQLSTRDLM(*APOSTSQL)]. Assuming the
matching delimiter and closing parenthesis for the SQL() parameter had
been inadvertently dropped [now added\corrected in the text copied from
the article and pasted below], the following would still not be valid in
CL; i.e. having failed to escape each embedded apostrophe with an extra
apostrophe:
RUNSQL SQL('Insert into SAMPLE Values('FIRST', 1, '2012-04-13')')
Looking at that CL string is surely deceptive for the possibilities;
i.e. dealing with the apostrophes remains a PITA, no matter what the
above might appear to enable. Admittedly the next two examples do
expose the expected ugliness, having to double the apostrophe on each
side of the literal. But the above would needed to have been instead
[ignoring the removed spaces per possible line wrap effects]:
RUNSQL SQL('Insert into SAMPLE Values(''FIRST'',1,''2012-04-13'')')
FWiW the article also suggests for that command string in the next
sentence, that "this RUNQRY command", which should instead suggest "this
RUNSQL command" string. Anyhow, the described as possible error could
be avoided by having coded the WITH NC clause on the INSERT statement,
irrespective the COMMIT parameter specification.
Had there been actual forethought, the RUNSQL command would have
defaulted to using the quotation mark as the [default] string delimiter,
instead of the apostrophe. Not so much of a stretch to have considered,
since the OPNQRYF CL command developers obviously had already made such
consideration way back when. Sadly, the OPNQRYF now looks better by
comparison for how string literals are handled; for that reason alone.
It is not like there are some /standards/ that need to be met for when
the interface is the proprietary IBM i CL language interface to the SQL.
Regards, Chuck
On 30-Mar-2012 07:56 , Jeff Crosby wrote:
I just read it and I like it.
http://www.mcpressonline.com/cl/the-cl-corner-introducing-the-new-run-sql-command.html
On Wed, Mar 28, 2012 at 6:47 PM, Bruce Vining<bvining@xxxxxxxxxxxxxxx>wrote:
This coming Friday (March 30, 2012) you may want to look at my CL
Corner column of MC Press Tips 'n Techniques. The column will
address some of your questions [?]
midrange.com
