Re: Wrong column names in ResultSetMetaData - Mailing list pgsql-jdbc

From Mike Martin
Subject Re: Wrong column names in ResultSetMetaData
Date
Msg-id cebd4c$2ku1$1@news.hub.org
Whole thread Raw
In response to Re: Wrong column names in ResultSetMetaData  ("Mike Martin" <mmartin@vieo.com>)
Responses Re: Wrong column names in ResultSetMetaData
List pgsql-jdbc
"Kris Jurka" wrote:
> > For programmatic purposes the column name concept is pretty
> > well defined by the docs on ResultSet.
>
> I am not sure I see where it states that.  Your logic makes some sense,
> but I don't see anywhere it says the above explicity.  The problem is how
> to return both pieces of information (the alias and the underlying column
> name) within the JDBC API.  Certainly the alias makes more sense as the
> label when you have to pick between the two of them.

It depends on how explicit you need it to be.  :)

Unfortunately the JDBC spec itself lacks much of anything that
explicit.  The Javadoc for ResultSet says:

    "The column name option is designed to be used when column
    names are used in the SQL query that generated the result set.
    For columns that are NOT explicitly named in the query, it
    is best to use column numbers."

I read "named" to mean named according to the usual SQL rules.
Combine that with the other references to the semantics of column
name (findColumn(), getXXX(colName)) and it seems like the
reasonable reading.

My other support is circumstantial:

1.  An SQL query produces a result set which is itself a table,
    that table has columns, those columns have names, and those
    names are defined according to SQL rules.  The column being
    named is that of the result table, not that of the underlying
    source(s) that contributed to it.  I can't imagine
    getColumnName() referring to any other concept.

2.  Every other JDBC driver I'm familiar with behaves that way
    (with the exception, I hear, of MimerSQL, which confuses
    getColumnLabel() with SQL alias the way our new code does).

I'm not clear why a JDBC client would want or need to know the
"underlying" column name, if there even is one.  I can see why
the driver might need to know that internally for purposes of
updateability but that's something different.

> Would you expect the results of getTableName() to return the underlying
> table or the table's alias in the query?  To be consistent with your
> argument you'd have to claim the alias name which is useless here.

The alias.  By SQL naming rules every column may also have a
table name component that can be used to disambiguate it from
other columns of the same name.  If a query represents a single
table expression, and that expression has been given an alias
name (aka "range variable"), then every column takes that alias
as its table name.

Likewise with getCatalogName(int) except I believe a column loses
any catalog affiliation as soon as it's aliased in any way.

> Further code doesn't make a whole lot of sense doing:
>
> rs.getString(rsmd.getColumnName(1));
>
> Why wouldn't it just do:
>
> rs.getString(1);

In real life you would.  I was just illustrating the expected
semantics.

Mike



pgsql-jdbc by date:

Previous
From: Kris Jurka
Date:
Subject: Re: PreparedStatement.getMetaData() and Creator
Next
From: Kris Jurka
Date:
Subject: Re: Bug in 7.4_213 driver: returns VARCHAR instead of