JDBC Question: Getting data from tables with columns that ..

Hi

I'm making a JDBC SQL query that looks something like this:

select <a bunch of columns>, a.shortcode, <some more coumns>,
z.shortcode from Table1 a, Table2 b, Table3 z where <join conditions>,
<more conditions>

I get reasonable values in my JDBC result set.

I've been trying to extract the two "shortcode" column data items that
came from Table1 and Table3 from my result set rows with calls like:

String st1 = new String();
String st2 = new String();
st1 = rs.getString("a.shortcode");
st2 = rs.getString("z.shortcode");

or with

st1 = rs.getString("Table1.shortcode");
st2 = rs.getString("Table3.shortcode");

All of these formulations result in a SQL Exception of field name not
recognized.

Though I haven't tried it, I'm sure I could use indexes to get at the
columns:

st1 = rs.getString(l
st2 = rs.getString(21);

but this would make maintenance a bit rough down the line, if/when the
query changes.

Am I doing something wrong, or does JDBC not know about table names or
table aliases in getNNN(String s) calls on result sets?

For your info, the database is Oracle 9i, the JDBC driver is in
ojdbc14.jar, which was on the computer when I inherited it, and I
presume that's what is connecting me to the database. I'm writing this
from home so I don't have access to the source code, but part of the
connection string inincludes "OCI8" in it followed by ID password and
service (don't remember the order though).

Any advice would be greatly appreciated!

-Lenny Wintfeld

Hi. Most JDBC drivers aren't told by the DBMS what table name
is associated with a data column, so the JDBC driver won't be
able to distinguish. A column may come from 0, 1, or more
tables.
The only two options you have are to get them by index,
or altering the SQL to assign unique aliases to each column.

Joe Weinstein at BEA Systems