Hi David,

David Jencks <davidjencks@...> schrieb:

I'm in a typical go-between situation: OpenOffice's jdbc connection
(www.openoffice.org) can't work with Interbase calculated columns and
aliases, and that is the primary "itch" I want to scratch.
Now the question is: fix the driver or fix the application. Since we're
talking OSS I'm in this happy situation :-)

In OpenOffice (and BTW StarOffice 5.2, too) the gui database components
are based on column names. This is astonishing first, but from the
component user's perspective it makes sense. You want to drop an edit
field, assign a column name and go on, you don't want to use ordinal
numbers or draw distinctions between labels and column names. If you have
ambiguous names you use aliases to be sure which field you're referring
to.

In JDBC the situation seems to be the exact opposite:

"When a getXXX method is called with a column name and several columns
have the same name, the value of the first matching column will be
returned. "

-> Never rely on unique names in applications. I've heard this and listened
as well ;-)

But the resultset javadoc continues:
"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. If
column names are used, there is no way for the programmer to guarantee
that they actually refer to the intended columns."

-> The question is what that means. It could mean: you should completely
avoid column names and only use column numbers. It could also mean: while
the primary way are ordinal numbers, you *might* use getXXX(colname) if the
user explicitly named every column in the query using AS.

The JDBC driver mmMySql, for instance, seems to allow getXXX(colname) and
findColumn(colname) for aliased columns. The effect is, there are no
problems with mySql and Star/OpenOffice as long as you use aliases for
ambiguous column names.
In Interclient both colname based methods fail for every aliased column,
not only calculated columns. IOW, no Interbase calculated columns and no
aliases in StarOffice.

If you want to offer column names to the end user, you must map ordinal
numbers, real column names and labels somewhere - either in the driver or
in every application that uses JDBC. The mmMySQL approach to invent the
wheel only once isn't completely silly, if we set the murky JDBC spec
aside for a moment.

The key to this is the possibility to use aliases in getXXX(colname) and
findColumn(colname). The Interclient approach is clean and well, as long
as you don't want to use a calculated column in a gui component.

At least this whole issue is worth thinking about. A minimal approach
could be to allow findColumn(colname) and getXXX(colname) to find
aliases. If I'm not mistaken that would mean first search aliases
(getColumnLabel), then real column names (getColumnName). E.g. in your
example where you swapped two columns this would lead to the expected result.

> The only other resource to consult I can think of is the cts. Have you
> looked at it? Pointers to tests there would be appreciated.

I'd like to take a look. What is the cts?

Greetings,
Dietrich