Re: Wrong column names in ResultSetMetaData

Kris Jurka wrote:
> > 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).
>
> What do they return for getColumnLabel()? Previously we returned the same
> value for both methods simply because we couldn't implement getColumnName
> as we do now. If they all return the alias in getColumnLabel that would
> be telling.

Most return the same thing as getColumnName(); not surprising
since that's a good default display title. I've heard that
some drivers return a string containing extra metadata fluff
about the column type.

Consider:

SELECT c1 + c2 FROM t1

The name/label distinction allows the DBMS/driver, if it wants,
to give the column a label for display purposes of, say,
"Expr c1+c2" or "Result, type=decimal(9,2)". But the column
has no SQL name. Nor could it have a name like "Expr c1+c2"
since that's not a valid SQL identifier.

> > 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.
>
> To allow client software to do the updates or allow the user to navigate
> from a query result column to the base table or to/through foreign keys on
> it. There could be a number of uses.

What about:

SELECT col1 AS price FROM t1
UNION
SELECT col2 AS price FROM t2

What is the "true" column name? SQL says that query produces a
table with one column named "PRICE".

Client code simply can't reverse-engineer query result columns to
find their origins. If a user is trying to do so for updating,
that's what updatable result sets are for, and that's why only a
small subset of queries are updatable.

>From http://java.sun.com/j2se/1.3/docs/guide/jdbc/spec2/jdbc2.1.frame5.html:

Due to differences in database implementations, the JDBC API
does not specify an exact set of SQL queries which must yield
an updatable result set for JDBC drivers that support
updatability. Developers can, however, generally expect
queries which meet the following criteria to produce an
updatable result set:

1. The query references only a single table in the database.
2. The query does not contain any join operations.
3. The query selects the primary key of the table it references.

In addition, an SQL query should also satisfy the conditions
listed below if inserts are to be performed.

4. The query selects all of the non-nullable columns in the
underlying table.
5. The query selects all columns that don't have a default
value.

Mike