Skip site navigation (1) Skip section navigation (2)

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

From: <noreply(at)pgfoundry(dot)org>
To: noreply(at)pgfoundry(dot)org
Subject: [ psqlodbc-Bugs-1001999 ] PSQLODBC folds identifiers to lower case, causing Excel (MS Query) to fail.
Date: 2007-02-16 18:11:51
Message-ID: 20070216181151.9A8D0217285@pgfoundry.org (view raw or flat)
Thread:
Lists: 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 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?

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

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

Next:From: Gabriel FerroDate: 2007-02-16 22:30:41
Subject: read field bytea from VB6
Previous:From: noreplyDate: 2007-02-16 14:31:22
Subject: [ psqlodbc-Bugs-1002054 ] SQLSetDescField with SQL_DESC_INDICATOR_PTR has no effect.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group