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 [?]



This thread ...

Replies:

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].