I'm running PostgreSQL 8.0 with version 08.01.0003 of the psqlodbclibpq
ODBC driver on Microsoft Windows Server 2003. I've noticed that
PostgreSQL stores unquoted identifiers as all lower case internally.
With the ODBC catalog functions such as SQLTables, if I don't specify
the table name with all lower case, it will not find it. For example,
if I create a table:
CREATE TABLE ABC (ID INTEGER);
Then calling SQLTables with the upper case table name returns no rows:
SQLTables(hstmt, NULL, 0, "%", 1, "ABC", 3)
But calling SQLTables with the lower case table name does return the
row:
SQLTables(hstmt, NULL, 0, "%", 1, "abc", 3)
According to the ODBC documentation in Microsoft's MSDN Library at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/ht
m/odbcsqltables.asp, you should be able to force SQLTables, SQLColumns,
etc. to be case insensitive by setting the SQL_ATTR_METADATA_ID
statement attribute to SQL_TRUE. However, this has no effect with the
current version of the PostgreSQL ODBC driver.
Searching through this mailing list, I found one reference to this
problem with a proposed solution that appears to be invalid in the
message at
http://archives.postgresql.org/pgsql-odbc/2005-08/msg00006.php. From my
perspective, the correct solution is to change one line in statement.h
from:
#define SC_is_lower_case(a, b) (a->options.metadata_id,
b->connInfo.lower_case_identifier)
and change it to:
#define SC_is_lower_case(a, b) (a->options.metadata_id ||
b->connInfo.lower_case_identifier)
or to simply:
#define SC_is_lower_case(a, b) (a->options.metadata_id)
because b->connInfo.lower_case_identifier never appears to be set to
true even if I manually change the value of the LowerCaseIdentifier
setting in the Windows registry to 1 (I can't find a way to set this
value in the UI) and reconnect to the database.
What is the chance that a fix for this case insensitive problem will be
made to the official PostgreSQL ODBC driver?
--
Chris Ingram
Software Developer, Intellisync Corporation
cingram@intellisync.com
www.intellisync.com