Accessing columns using fully qualified name from a Join

1) I'm connecting to SQLServer2000 using Microsoft SQL Server 2000 Driver for
JDBC, Service Pack 2, Version 2.2.0037
2) The query I'm executing is:
select * from employees, dept, city where dept.dpid=employees.dept and " +
"employees.cityofresidence=city.cid order by dept.dpid, empid
3) city and dept tables have a field called 'name'
4) rs.getString("name") gives dept.name (city.name if city appears before
dept in the FROM clause of the query)
5) rs.getString("city.name") throws exception - 'Invalid column name'
How can I use the fully qualified column name to retrieve correct values
irrespective of the order of the appearance of the table names in the FROM
clause of the query
Note: rs.getString(int col_num) always retrieves correct values.
praskam wrote:

> 1) I'm connecting to SQLServer2000 using Microsoft SQL Server 2000 Driver for
> JDBC, Service Pack 2, Version 2.2.0037
> 2) The query I'm executing is:
> select * from employees, dept, city where dept.dpid=employees.dept and " +
> "employees.cityofresidence=city.cid order by dept.dpid, empid
> 3) city and dept tables have a field called 'name'
> 4) rs.getString("name") gives dept.name (city.name if city appears before
> dept in the FROM clause of the query)
That is correct, per JDBC spec.

> 5) rs.getString("city.name") throws exception - 'Invalid column name'
The data/metadata that come back from the DBMS do not contain any reference to the
table from which a column came.

> How can I use the fully qualified column name to retrieve correct values
> irrespective of the order of the appearance of the table names in the FROM
> clause of the query.
You can't. Unless you explicitly define a label for each column in the SQL.
That would involve replacing "select *" with select t1.col1 'table1.col1', t1.col2 'table1.col2' ...
t2.col1 'table2.col1' etc. from table1 t1, table2 t2 etc.
Note that you can use any string for a column label. Using "table1.col1" would suit your
request, but you could tailor it as you see fit.
Joe Weinstein at BEA

> Note: rs.getString(int col_num) always retrieves correct values.
As expected, and is the most reliable.
>