On Wed, 2003-09-10 at 17:54, Andrew Ayers wrote:
> Greg Campbell wrote:
> [snip]
> > The ODBC driver is seeing lower case and I have to double-quote the
> > lower case in the string to make it work. Lower-casing and
> > double-quoting all tables names and field name is the kind of backend
> > specific sql changing I wanted to avoid. Is there a setting on the ODBC
> > Driver for Windows that will recognize the mixed case properly?
>
> IIRC, the setting isn't in the ODBC driver settings, but rather in the
> setup of PG.
>
> Strangely enough, with my application I converted a bunch of Access
> tables to PG, and noticed that on the backend, all of them seemed to be
> lowercased (from the POV of psql). On the frontend (a VB6 app), the SQL
> selects and such were mixed case (as that is how the original Access
> tables were defined), but the driver handles it all fine (I am using a
> DSN-less connection, so no special flags are being sent in the
> connection string, other than setting ksqo to false).
The reason is that, in accordance (almost) with the SQL standard, all
identifiers are case-folded unless they are protected by double quotes.
(The standard actually says to fold to upper case.) If you consistently
leave out the quotes, the mixed case names will be translated
automatically to lower case. You only get problems if one part of your
application quotes them and another does not.
As a matter of design, it is better not to use letter case to identify
words in SQL identifiers. It's a Microsoftism, and Microsoftisms only
lead to trouble.
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Draw near to God and he will draw near to you.
Cleanse your hands, you sinners; and purify your
hearts, you double minded." James 4:8