Article

Error occurs when running a SQL-92 query that contains a hyphen, dash, or reserved word in a table or field name

Information

 
Article Number000010930
EnvironmentProduct: Progress
Version: 9.x
Product: OpenEdge
Version: 10.x, 11.x
OS: All supported platforms
Question/Problem Description
Error occurs when running a SQL-92 query that contains a hyphen, dash, or reserved word in a table or field name.
Error 7519 running a SQL-92 query that contains a hyphen, dash, or reserved word in a table or field name.
Error 10713 running a SQL-92 query that contains a hyphen, dash, or reserved word in a table or field name.
SELECT statements fails when a hyphen is contained in the field or table name.
Cannot query table names or field names that contain hyphens in SQL statements.
Unable to query field names containing reserved alphanumeric characters.
Unable to query field names containing dashes.
Date field enclosed in double quotes ("").
Error occurs when creating a VIEW whose name is similar to Name-Of-View (groups of letters separated by dashes).
Steps to Reproduce
Clarifying Information
SELECT statements return data when tables and field names do not contain hyphens
Error MessageTable/View/Synonym not found (7519)

=== SQL Exception 1 ===
SQLState=42S02
ErrorCode=-20005
[JDBC Progress Driver]:Table/View/Synonym not found (7519)
[DataDirect-Technologies][ODBC PROGRESS driver][PROGRESS]Table/View/Synonym not found (7519)
Syntax error at or about (statement excerpt). (10713)

=== SQL Exception 1 ===
SQLState=HY000
ErrorCode=-210056
[DataDirect][OpenEdge JDBC Driver][OpenEdge] Syntax error in SQL statement at or about "<statement excerpt>" (10713)
[DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Syntax error in SQL statement at or about "<statement excerpt>" (10713)
Defect/Enhancement Number
Cause
This is the expected behavior. The use of the reserved characters, such as hyphens, in field or table names, or SQL-92 reserved words in a SQL statement requires that the to be encapsulated in double quotes. Dashes and hyphens in table or field names are considered special characters. This error behavior generally occurs in the following circumstances:

1. The field or table name contains a hyphen, but is not enclosed within double quotes.
2. The field or table name is a SQL-92 reserved word.
3. The date field is enclosed in double quotes instead of single quotes.
 
Resolution

There are two ways to reference a table or field name with dashes, hyphens or reserved words:

1) Use double quotes on table names. For example: 

SELECT * FROM pub."Order"

Order is a SQL Reserved Word and needs to be in quotes. 

The second statement inserts a new record into the Customer table and inserts the value 1 into the Number field. The word Number is also a SQL Reserved Word that must be in quotes. For example:

INSERT INTO pub.customer ("Number") VALUES (1)

The third statement selects all records from the order-line table. SQL-92 does not allow dashes in the field or table name and hence it must be enclosed in the double quotes. For example:

SELECT * FROM pub."order-line"


2) Create a view to reflect the same table with all the fields selected. For Date Fields enclose them in single quotes. For example:  

SELECT * FROM pub.invoice WHERE "invoice-date" = '1993-02-08'

3) The same is true for creating a VIEW.
Assume the addition of the field Date-Of-Death to records in the EMPLOYEE table of the sports2000 database.
Further assume the addition of an employee named Donald Duck, EmpNum 9876543 who died 12/21/2018.
Further assume the desire to create a VIEW named Dead-Emp (show employees that have died).

To create a VIEW of employees that died on 12/21/2018, the following is required to successfully create the VIEW:

CREATE VIEW "Dead-Emp" AS SELECT EmpNum, LastName FROM Pub.EMPLOYEE WHERE "Date-Of-Death" = '12/21/2018';

To use the VIEW, the following is required:

SELECT * FROM "Dead-Emp";

Using sqlexplorer, this results in the following:

EmpNum    LastName
-------------   ---------------
9876543     Duck
 

Workaround
For table names that are reserved words, it is also possible to create a synonym for the table. A synonym is an alias that SQL statements can use instead of the name specified when the table, view, or synonym was created. This would avoid the requirement to add quotation marks around the table name. For example,

CREATE PUBLIC SYNONYM sequencetable FOR pub."sequence";

The above techniques work in the ESQL/C interface as well as the ODBC and JDBC interfaces. ESQL/C is deprecated as of OpenEdge 10.
 
Notes
References to other documentation:
SQL-92 Guide and Reference, "Progress SQL-92 Reference Information > Progress SQL-92 Reserved Words"
OpenEdge® Data Management: SQL Reference, "OpenEdge SQL Reserved Words"

SQL Development: OpenEdge SQL and Advanced Business Language Interoperability: ABL and OpenEdge SQL interaction in an
OpenEdge Application: Naming objects for OpenEdge SQL and ABL databases.
https://documentation.progress.com/output/OpenEdge117/openedge117/#page/dmsdv%2Fnaming-objects-for-openedge-sql-and-abl-database.html%23

SQL Reference: OpenEdge SQL Reserved Words:
https://documentation.progress.com/output/OpenEdge117/openedge117/#page/dmsrf%2Fopenedge-sql-reserved-words.html
 
Progress Article(s):
000001449, How to Connect to SQL-92 Server Using ODBC and SQL Explorer
000049334, List of SQL-92 reserved words by version

Note: By default SQL identifiers may contain letters, underscores and digits. In addition identifiers must start with a letter. An identifier must not be a reserved word. An identifier adhering to these rules are known as an ordinary or regular identifier. To be able to include characters such as spaces and dashes, the identifier must be surrounded by double quotes. Such an identifier is known as a delimited or quoted identifier.
Attachment 
Last Modified Date12/26/2018 2:05 PM


Feedback
 
Did this article resolve your question/issue?

   

Your feedback is appreciated.

Please tell us how we can make this article more useful. Please provide us a way to contact you, should we need clarification on the feedback provided or if you need further assistance.

Characters Remaining: 1025