Kbase P11495: Error occurs when running a SQL-92 query that contains a hyphen, dash, or reserved word in a table o
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/05/2010 |
|
Status: Verified
SYMPTOM(s):
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
Table/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)
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 ("")
SELECT statements return data when tables and field names do not contain hyphens
FACT(s) (Environment):
Running SQL-92 statements that use SQL reserved words
Progress 9.x
OpenEdge 10.x
All Supported Operating Systems
CAUSE:
This is the expected behavior the use of the reserved characters requires use of particular format using double quotes, quotes and parenthesis. Dashes and hyphens in table or field names are considered special characters. This error behavior is consistent with 2 causes:
1. The field name contains a hyphen, but is not enclosed within double quotes.
2. The date field is enclosed in double quotes instead of single quotes
FIX:
There are two ways to reference a table or field name with dashes, hyphens or reserved words:
1) Use double quote on table names. For example:
SELECT * FROM pub."Customer-Name"
Order is a SQL Reserved Word and needs to be in quotes. For example, SELECT * FROM pub."Order".
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'