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

Well, it's definitely caused by the # in the name. And that explains why this has been happening randomly. Obviously, names without it work fine.
And yes, the table is DDS defined.
But I can't get the --# names to work. I've tried single quotes, double quotes, two single quotes together, all with upper-casing. All give the same result.
I will look for JDBC settings to fool with.

Thanks for the tips. At least now I know what the cause is, and also why there's a difference between the two interfaces.

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Krill, Coy
Sent: Thursday, July 27, 2017 2:40 PM
To: Midrange Systems Technical Discussion
Subject: RE: Syntax error in ACS Run SQL Scripts but not in STRSQL

Single quotes enclose a character value, double quotes enclose the name of an entity. This is the name of the column and since it has a #, I assume it was DDS defined, so it's really all uppercase, if it's DDL defined, then the case may be lower/upper/mixed depending on how they wrote it. STRSQL and JDBC do not make the same configuration assumptions about the connection. That said, my ACS doesn't require me to use the "" when we have a legacy field with a # in it, but our Cognos users have to use them. So there may be a JDBC setting he can change to make it work for him or it could be how the database/schema is configured.

Coy Krill
Core Processing Administrator/Analyst
Washington Trust Bank

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jack Woehr
Sent: 2017 July 27 11:31
To: Midrange Systems Technical Discussion
Subject: Re: Syntax error in ACS Run SQL Scripts but not in STRSQL
Importance: Low

maybe 'derrick#' with quotes?

On Thu, Jul 27, 2017 at 12:28 PM, Krill, Coy <CKrill@xxxxxxxxxxx> wrote:

It's the # in the column names, use "DERRICK#" and "BLOCK#".

Coy Krill
Core Processing Administrator/Analyst
Washington Trust Bank

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Bill Reed
Sent: 2017 July 27 11:20
To: 'Midrange Systems Technical Discussion'
Subject: RE: Syntax error in ACS Run SQL Scripts but not in STRSQL
Importance: Low

It was all on one line in Run SQL Scripts, and I did that
intentionally to make sure there were no line break issues. Typically
however, I would break to a new line on the scripts version, probably
at the "where" for readability. I have tried it both ways with no difference.

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Rob Berendt
Sent: Thursday, July 27, 2017 1:42 PM
To: Midrange Systems Technical Discussion
Subject: Re: Syntax error in ACS Run SQL Scripts but not in STRSQL

I know that doesn't all fit on one line in STRSQL. Perhaps the line
break made a difference? Did you have it as one line in Run SQL Scripts?
The only error I got was that the file did not exist.

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600
Mail
to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com

From: Bill Reed <breed@xxxxxxxxxxxxxx>
To: MIDRANGE-L@xxxxxxxxxxxx
Date: 07/27/2017 01:22 PM
Subject: Syntax error in ACS Run SQL Scripts but not in STRSQL
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>

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