There are a number of issues related to column names in ResultSets.

1. In JDBC API, ResultSetMetadata has separate getColumnName(index)
and getColumnLabel(index) functions. This suggests a column name is
the name used in a table and column label is the name used in the
result set which may or may not be the name used in the table.

The current v.200 JDBC driver returns the correct label used for a
calculated or function field when getColumnLabel(index) is called but
returns "" for getColumnName(index) in those cases.

2. JDBC API is not well thought out. So there is findColumn(name) but
no findColumn(label) in ResultSet.

3. SQL result sets can have multiple columns with the same name or
label. So the result of getXXX(name) is not necessarily unambiguous.
For example:

Select t1.name, t2.name, t1.email from t1, t2 where t1.email=t2.email
and t1.name!=t2.name;

In my opinion it would to silly to be able to refer to calculated and
function fields by name if you have not given them a name. As for
user-defined labels it should be simple enough to implement this
feature. For now, you can do something like:

PreparedStatement ps = ....;
ResultSet rs = ps.executeQuery();
int count = rs.getMetaData().getColumnCount();
Map hm = new HashMap ();
for ( int i = 1; i <= count ; i++ )
hm.put(rs.getMetaData().getColumnLabel(i), new Integer(i));


// Later you can:
int index = ((Integer) hm.get(label)).intValue();
Object object = rs.getObject(i);


Fred

--- In IB-Java@y..., Dietrich Schulten <ibo@s...> wrote:
> Hi,
>
> Interclient has a problem with getXXX(colname) under the following
> circumstances, and I think David Jencks' JDBC driver might be
affected as
> well.
>
> First some background:
> Interbase seems to return no column name for calculated columns or
a
> possibly ambiguous column name, depending what you are calculating.
> Aggregate functions use the name of the function as column name
(MAX),
> calculations such as col1 * col2 return an empty column name.
>
> The effect is, getXXX(colname) cannot find calculated columns
because
> resultset.findColumn(colname) fails. This is no surprise,
considering the
> column naming scheme for calculated columns.
> However, using aliases doesn't help either. The only thing that
helps are
> ordinal numbers instead of column names: getXXX(int) works.
>
> For a JDBC driver this is quite unfortunate, because you usually
expect
> to be able to use getXXX(colname) in applications, especially in
> applications that rely heavily on metadata to allow user defined
queries etc.
>
> I'm not sure what would be the best way, maybe it wouldn't be very
> difficult to guarantee a meaningful column name on engine level. If
not,
> the driver would have to build an internal map, make up unique
column
> names for calculated columns and handle findColumn(String) based on
this
> map.
> I tend to think that this should be done on engine or at least on
driver
> level. Otherwise we force applications to rely solely on ordinal
numbers
> and implement a name-alias-ordinal number map themselves.
>
> Opinions? Has anybody started to work on this already?
>
> Dietrich Schulten