Feedback
Did this article resolve your question/issue?

   

Article

"Incorrect syntax near '-'" error INNER or OUTER join queries when foreign database name contains hyphen.

Information

 
Title"Incorrect syntax near '-'" error INNER or OUTER join queries when foreign database name contains hyphen.
URL NameIncorrect-syntax-near-error-INNER-or-OUTER-join-queries-when-foreign-database-name-contains-hyphen
Article Number000136146
EnvironmentProduct: OpenEdge
Version: 11.6
OS: All supported platforms
Other: Microsoft SQL Server 2012
Other: ODBC SQL Server Wire Protocol driver 11.6
Question/Problem Description
When an hyphen "-" is used in a SQL database name, the select statements are wrong (the text is not enclosed with quotes) when INNER or OUTER joins are used:
 
MSS -- Execute on 0x4830048 Firehose Connection Level 4 new: SELECT T0."foo", T0."bar", T0.PROGRESS_RECID, T0.PROGRESS_RECID_IDENT_, T0.PROGRESS_RECID_ALT_, 
MSS -- T1."quuz", T1."corge", T1."baz", T1."quux", T1."user_id", T1.PROGRESS_RECID, T1.PROGRESS_RECID_IDENT_, T1.PROGRESS_RECID_ALT_, 
MSS -- T2."quuz", T2."corge", T2."baz", T2."quux", T2.PROGRESS_RECID, T2.PROGRESS_RECID_IDENT_, T2.PROGRESS_RECID_ALT_, 
MSS -- FROM "foo-db"."bar-t"."a_b" T0 INNER JOIN foo-db.bar-t.cuser T1 ON (T0."foo" = T1."user_id") LEFT OUTER JOIN foo-db.bar-t.waldo T2 ON (T1."pers_id" = T2."pers_id") 
MSS -- WHERE (T0."bar" = ?) ORDER BY T1."user_id"


 
Steps to Reproduce
Clarifying Information
The foreign database name contains the hyphen character.
Error MessageDataserver log with qt_debug,SQL reads:

MSS -- SQLState: S1000 
MSS -- Native Error Code: 102 
MSS -- Error Message: [Data Direct][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Incorrect syntax near '-'. (15044) 
MSS -- SQLState: S1000 
MSS -- Native Error Code: 8180 
MSS -- Error Message: [Data Direct][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Statement(s) could not be prepared. (15044)
Defect/Enhancement NumberDefect PSC00359561
Cause
This is a known issue.

Object name used in INNER and OUTER join has been copied from “pmisc” field of ODBCACHE which is not enclosed in quotes. 
This is resulting in a problem where query execution is failing with bad syntax when db/owner/table name contains special characters.
 
Resolution
Fixed in hot fix 11.6.3.028. Refer to the hot fix “readme.txt”  for instructions on how to download and install the hot fix.
Upgrade to OpenEdge 11.6.4.
Upgrade to OpenEdge 11.7.2 or later.

Quotes have now been added to fully qualified table names after OUTER and INNER join queries.
Workaround
Notes
Last Modified Date11/20/2020 7:13 AM
Attachment 
Files
Disclaimer The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.