RE: Syntax error in ACS Run SQL Scripts but not in STRSQL

That was it. I was not using packages, but did just uncheck "Check SQL Portability" under options, and now it will work with double quotes and upper case. And by double quotes I mean the single character version, not two separate single quotes.
Thank you!


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Sue Romano
Sent: Thursday, July 27, 2017 3:10 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: Syntax error in ACS Run SQL Scripts but not in STRSQL

In ACS, one of the options in the Options tab is Check SQL Portability.
You must have that selected. When you have this option checked, an additional syntax check is made against every statement that is run to see if it contains anything that is not part of the SQL standard. When you get the SQL0362 warning, there is a prior message in the joblog that indicates exactly what was flagged as non-standard. In your situation, it is telling you it doesn't like the column name that contains a #; this is not a valid character in names in the SQL standard.

If you are not interested in this information, uncheck the box and the warnings will no longer be issued. STRSQL does not have an option to provide this information, so you will never see this message through that interface.

Sue Romano
IBM i SQL Development

Occasionally I find that when trying to do an UPDATE or a DELETE using

ACS Run SQL Scripts, I will get a syntax error which prevents the

operation, but if I copy and paste the statement string into a

green-screen STRSQL session, it works just fine. (This only happens with

UPDATE and DELETE, not with SELECT.)

Here is an example:
-----------------------------------------------------------------------
-------------------------------------------------
update rsprdlog set nonprod = 'Y' where derrick# = 'PAG' and block# =

17513;

-----------------------------------------------------------------------
-------------------------------------------------

This is met with the following result (there should have been two rows

updated):

-----------------------------------------------------------------------
-----------
SQL State: 0168I
Vendor Code: 362
Message: [SQL0362] Flagging detected an error in the current SQL

statement. Cause . . . . . : The SQL statement contains syntax that is
not >standard at position 41. Recovery . . . : See previous messages in
the job log for a more complete description of the error. If >conformance to the standard is needed, change the statement to conform and try the request again.

Statement ran successfully, with warnings (3 ms)
0 rows were affected by the statement
-----------------------------------------------------------------------
-----------

Position 41 is the first letter of the conditioning column name

"derrick#". And yes, there are spaces between all the words even if they're >hard to see.

Job log shows:
-----------------------------------------------------------------------
----------- At token derrick#, the syntax of the SQL statement deviates
from the ANS

standard. The previous two tokens were 'Y' and WHERE. One of the >tokens may be a reserved word.

-----------------------------------------------------------------------
----------- Well, yes, WHERE is a reserved word, but what's wrong with
it here?
So (a) what am I doing wrong, and (b) if there is an error, why is
STRSQL

more forgiving?

System is 7.1
ACS is 1.1.7.1 (just updated, but this happened with 1.1.6 as well).

Thanks,
Bill Reed

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: http://amzn.to/2dEadiD