[ psqlodbc-Bugs-1001999 ] PSQLODBC folds identifiers to lower case, causing Excel (MS Query) to fail.

Bugs item #1001999, was opened at 2007-02-14 11:55
You can respond by visiting:
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1001999&group_id=1000125

Category: None
Group: None
Status: Open
Resolution: None
Priority: 3
Submitted By: Frank Brown (frankjbrown)
Assigned to: Nobody (None)
Summary: PSQLODBC folds identifiers to lower case, causing Excel (MS Query) to fail.

Initial Comment:
PG converts unquoted identifiers in SQL statements to lower case. This can be a problem when using inflexible applications such as MS Excel (MS Query wizard). Here is my example. We are converting some old HP Image database and Oracle tables into postgres 8.2. These old existing tables were named in all upper case, and it was decided to maintain the upper case names to make the transition less stressful for the users. Using the new tables in MS Access is very easy, by just using the Linked Table Manager to reconnect to the new tables. Excel on the other hand, is a problem. The MS Query wizard correctly displays the tables and column names in upper case and allows you to select the fields to query. However viewing the generated SQL shows that none of the names are quoted, and so the PSQLODBC converts the all the identifiers to lower case upon execution, and so the query fails saying that the schema name (in lower case) doesn't exist, which of course it can not
. One can rightly argue that this is really an MS Query issue, but that is not helpful. While I can just type out a correctly quoted SELECT statement, we can not expect the users to do that. They will only use the wizard. Since the conversion to lower case is the way PG was built, it would be impractical to just change it now, however, some means needs to be found to address the incompatibility with SQL standards and accommodate the issue. Therefore, perhaps this is more of a new feature request. Would it be possible to add a selection on Data Source definition in ODBC Manager for how the conversion should be done? Otherwise perhaps it could just maintain the exact way it appears in the generated SQL statement.

In the mean time, would it be possible for me to modify the driver to force upper case instead of lower?

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

You can respond by visiting:
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1001999&group_id=1000125