[ 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=10019 99&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 f
ails 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?

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

Comment By: Frank Brown (frankjbrown)
Date: 2007-02-19 14:50

Message:
Thank you for your Response David... I understand that I could do that and that it would resolve this issue, however, because I am migrating existing data and applications over to PG, that would mean having to modify several other systems which are coded using syntax similar to this... SELECT "XYZ"."ABCD" from "ASDF"."JKL" .

As a workaround I have constructed a second schema of the same name, but in lower case, and then added to it views for each table using Select * from "UPPER"."TABLENAME" . This is kind of a messy way to do it though.




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

Comment By: David Gardner (davidgardner)
Date: 2007-02-16 13:11

Message:
If you create your tables in lower case without qoutes around the table name, this basicly tells PG to be case insensative:
CREATE TABLE test { id serial NOT NULL, CONSTRAINT test_pkey PRIMARY KEY (id)} WITHOUT OIDS;

you can then issue select statements like:
SELECT * FROM TEST;


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

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings