Win a copy of 97 Things Every Java Programmer Should Know this week in the Java in General forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
  • Campbell Ritchie
  • Paul Clapham
  • Jeanne Boyarsky
  • Junilu Lacar
  • Henry Wong
  • Ron McLeod
  • Devaka Cooray
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Frits Walraven
  • Tim Holloway
  • Carey Brown
  • Piet Souris
  • salvin francis
  • fred rosenberger

result set access with multiple tables, same column name

Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am attempting to access a result set from a query that has the same column name in both tables. This is on a DB2 database using WebLogic 8.

for example:
select * from TABLE_A A, TABLE_B B where A.columnname = B.columnname

when I attempt to access the result set:
ResultSet rs = statement.getResultSet();;

I get DB2Exception: [IBM][JDBC Driver] CLI0611E Invalid column name. SQLSTATE=S0022

I also tried fully qualifying the name USER.TABLE_A.columnname, but I received the same error.

Any ideas?


Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
  • Get the columns returned by ID, not name (recommended best practice). If you get the columns out of order your code is not guaranteed to work (strange, but true).
  • Try getDouble("A_column")
  • If you don't like that idea, try A.*, B.* instead of plain old *. I don't think that'll achieve much though
  • Check the ResultSetMetaData and get the column name actually being returned. You can probably find this out by running the query directly against your database and seeing what the column headings come out like.

  • Hope that helps.

    Posts: 24
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    I am having the same problem. I am having 2 tables which has several columns which has the same name.
    for ex select * from tab1 t1,tab2 t2; let us say that tab1 and tab2 has same column names ex: col1 and col2.
    when i try to access without a fully qualified table name then i am getting the value of the second table.
    so for ex if i say resultset.getString("col1"). then i get the value of the tab2 col2. but when i query the DB directly then the order of column is same as expected meaning it gives tab1.col1.

    if i use fully qualified name then it says that the column name was not fond.

    did anybody into this kind of problem.

    i am using postrgres 8.3.3. on windows OS.
    any help is appreciated.
    Ranch Hand
    Posts: 2458
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Do not use SELECT *. Use column names and if duplicate names occur, use unique column aliases.

    Posts: 4173
    jQuery Eclipse IDE Java
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Wow reviving a dead thread. Anyway, I disagree about getting by ID being a best practice, its a recipe for disaster in my experience. Bauke is correct about "SELECT *" that should never be in a line of code, ever. When in doubt you can always re-write your query as "SELECT x.myVal AS somethingElse FROM myTable x,...." or some such variation.
    Posts: 1
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    if you so not have control over the query being used....try WebRowSet Interface.. it can give you all information about the meta data in xml format. you can use this xml to get which index in resultset belongs to column of which table.

    ResultSet rs = stmt.executeQuery("select * from user, admin where user.firstname = admin.firstname");

    WebRowSet wrs = new WebRowSetImpl();

    try {
    wrs.writeXml(rs , System.out);
    } catch (IOException e) {

    hope this helps....
    Story like this gets better after being told a few times. Or maybe it's just a tiny ad:
    Devious Experiments for a Truly Passive Greenhouse!
      Bookmark Topic Watch Topic
    • New Topic