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

From
Subject [ psqlodbc-Bugs-1001999 ] PSQLODBC folds identifiers to lower case, causing Excel (MS Query) to fail.
Date
Msg-id 20070216181151.9A8D0217285@pgfoundry.org
Whole thread Raw
List pgsql-odbc
Bugs item #1001999, was opened at 2007-02-14 08: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
applicationssuch as MS Excel (MS Query wizard).    Here is my example.   We are converting some old HP Image database
andOracle tables into postgres 8.2.   These old existing tables were named in all upper case, and it was decided to
maintainthe upper case names to make the transition less stressful for the users.   Using the new tables in MS Access
isvery 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
fieldsto query.  However viewing the generated SQL shows that none of the names are quoted, and so the PSQLODBC
convertsthe all the identifiers to lower case upon execution, and so the query fails saying that the schema name (in
lowercase) 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
acorrectly quoted SELECT statement, we can not expect the users to do that.  They will only use the wizard.     Since
theconversion to lower case is the way PG was built, it would be impractical to just change it now, however, some means
needsto be found to address the incompatibility with SQL standards and accommodate the issue.   Therefore, perhaps this
ismore of a new feature request.  Would it be possible to add a selection on Data Source definition in ODBC Manager
forhow 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: David Gardner (davidgardner)
Date: 2007-02-16 10: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=1001999&group_id=1000125

pgsql-odbc by date:

Previous
From:
Date:
Subject: [ psqlodbc-Bugs-1002054 ] SQLSetDescField with SQL_DESC_INDICATOR_PTR has no effect.
Next
From: Gabriel Ferro
Date:
Subject: read field bytea from VB6