Re: getTables not returning 10 columns etc

On Thu, Dec 8, 2011 at 7:22 PM, the6campbells <the6campbells(at)gmail(dot)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.