Consultor Eletrônico

Status: Unverified

FACT(s) (Environment):

Oracle
Progress 9.x
OpenEdge 10.x

SYMPTOM(s):

Connecting from Oracle Discoverer to Progress via SQL92 ODBC.

Invalid Column Name error when using Oracle tools to Progress via ODBC.

Oracle client immediately produces error Invalid Column Name.

Any reference to a field within a SELECT statement causes a failure.

SELECT * from pub.customer; works fine.

SELECT custnum from pub.customer; fails.

Using Progress SQL92 ODBC driver.

The same query run from a 3rd party SQL query tool that doesn't go through Oracle's Heterogeneous Services works fine.

Connecting from Oracle Tools to Progress via Oracle Heterogeneous Services (SQL92 ODBC) to a Progress database.

CAUSE:

Oracle appears to be creating a lookup table that contains all of the fields of the queried table, and that the table uses an upper case version of all the column names. Thus when lower case version of the column is in the query, it returns error "Invalid column name".

FIX:

There are two workarounds to the problem:
1. Change the SELECT statement to use quotes around the field names. This makes it ignore the lookup table and send it as-is to Progress E.g:

SELECT "custnum" from pub.customer;
2. Change the Progress database schema to be in upper case. However, you may need to ensure that this will not cause problems with features of your application that may rely on the field names (like any XML functionality that includes table/field names, because XML is case sensitive).

FOR EACH _file WHERE _file._file-number >= 1 AND
_file._file-number <= 32767 EXCLUSIVE-LOCK:
_file._file-name = CAPS(_file._file-name).
FOR EACH _field OF _file EXCLUSIVE-LOCK:
_field._field-name = CAPS(_field._field-name).
END.
END.