Re: getTables not returning 10 columns etc - Mailing list pgsql-jdbc

On Fri, Dec 9, 2011 at 1:50 AM, Samuel Gendler
<sgendler@ideasculptor.com> wrote:
>
>
> On Thu, Dec 8, 2011 at 7:22 PM, the6campbells <the6campbells@gmail.com>
> wrote:
>>
>>
>> was expecting 10 not 5 columns and the names of the result columns for
>> the metadata methods to be independent in terms of how RDBMS vendors
>> may hold their system catalog.
>> assuming JDBC result column names are as per the docs UPPER CASE.
>>
>
> The java documentation you link to certainly does seem to imply that all 10
> columns should be there and doesn't seem to make allowances for any of them
> being optional. As for the lower case thing, I recently noticed that all of
> my column names come back in lower case, even if explicitly aliased to a
> mixed case or upper case name.  A bit of googling revealed that the sql
> standard says nothing is case sensitive unless double quoted, so something
> like this:
>
> select columnName as "columnName" from table
>
> should return a mixed-case column name and anything else is fair game for
> any form of capitalization or lack thereof.  Alternatively, if the table is
> created with column names in double quotes:
>
> create table "MixedCase" ("MyColumn" varchar);
>
> the case of the letters will be preserved without the explicit alias - and
> you will also have to use mixed case and double quotes when referring to the
> table and column names in any queries.  Since the java docs you pointed to
> don't specify that the names should be anything other than the usual
> case-insensitive style of column names that sql says is the default, the
> driver clearly feels free to return them in all lower case.  A quick test
> shows that it doesn't appear that the db even remembers your original
> capitalization if you don't use double quotes in the create statement, so it
> probably simply isn't possible for the dd or driver to offer a switch that
> would force the columns to come back in their original form. I have always
> used all-lower-case until I inherited code from elsewhere that assumed mixed
> case in queries would deliver mixed case in column metadata. Once I found
> the problem, I simply started writing code that never assumes case
> sensitivity with table and column names, which is arguably more correct.
>  The unfortunate thing is that if someone should write code that assumes
> lower case, a bug isn't likely to be detected unless/until the behaviour of
> the code changes or you switch/upgrade databases.  It's unfortunate that the
> widespread use of ORM makes database switches easy enough in many cases that
> they may be performed with insufficient testing of dependent code. That
> said, it is hard to see where someone modifying an underlying db, even just
> upgrading to a new version, without testing sufficient to detect this
> problem has anyone to blame but themselves.  That's the argument I expect
> you'd get from the developers, anyway.  Assuming the more simple argument of
> "all databases force everything to lower case" isn't valid.  I don't know,
> since I've been postgres-only for so long, I can't remember what anything
> else does.
>
> Sio the short answer is that unless you explicitly request them in a
> case-sensitive manner, all column names should be handled in a
> case-insensitive manner - so your code shouldn't complain about the
> capitalization.  The missing columns does appear to be a real problem, based
> on my very quick perusal of the documentation, but I'm hardly an expert.
>
>


Yes, the missing columns are a real issue. JDBC 2.0 used to only require 5.

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca