× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. July 2017

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


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

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


---------------------------------------------------------------------
This electronic mail message and any attachments may contain
confidential or privileged information and is intended for use solely
by the above-referenced recipient. Any review, copying, printing,
disclosure, distribution, or other use by any other person or entity
is strictly prohibited under applicable law. If you are not the named
recipient, or believe you have received this message in error, please
immediately notify the sender by replying to this message and delete
the copy you received

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

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




--
Absolute Performance, Inc.
12303 Airport Way, Suite 100
Broomfield, CO 80021

NON-DISCLOSURE NOTICE: This communication including any and all attachments is for the intended recipient(s) only and may contain confidential and privileged information. If you are not the intended recipient of this communication, any disclosure, copying further distribution or use of this communication is prohibited. If you received this communication in error, please contact the sender and delete/destroy all copies of this communication immediately.
--
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


---------------------------------------------------------------------
This electronic mail message and any attachments may contain confidential or privileged information and is intended for use solely by the above-referenced recipient. Any review, copying, printing, disclosure, distribution, or other use by any other person or entity is strictly prohibited under applicable law. If you are not the named recipient, or believe you have received this message in error, please immediately notify the sender by replying to this message and delete the copy you received

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


As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2025 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].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.