com.ibm.db2.jcc.b.SqlException: Invalid argument: unknown column name COL1

Question

Hi, i was getting this error com.ibm.db2.jcc.b.SqlException: Invalid argument: unknown column name COL1 when trying to access my resultSet with rs.getString("COL1").
My SQL Query is: Select UPPER(COL1) from table1. the same query and Java code is working fine with DB2 v8(Type 2 driver) but it is throwing the above exception when using with DB2 v9(Type 4 driver).

However i am able to resolve this error by adding an alias, modified query: Select UPPER(COL1) COL1 from table1.

the above query is working with both DB2 v8 and v9. does this mean, in DB2 9 we must provide an alias when using with functions like (upper,trim,..)???

thanks

Answer 1

Nothing in DB2 or SQL requires the column names of functions to be specific values.

It may be that the earlier drivers (or even the t2 drivers, which I would generally never use anymore, preferring t4 myself) gave you a column name you expected but I wonder what it would have given you for col1 | '.' | col2.

Your query really should be what you have to get it working:

select upper(col1) as col1 from table1

This guarantees the column name is col1.

If you really want to know what the column names are for your query, you can retrieve the meta data for the result set with the following calls (examples only):

ResultSet rs = <get your result set here>;
ResultSetMeatData meta = rs.getMetaData();
for (int i = 1; i < meta.getColumnCount(); i++) {
    System.out.println (getColumnName (i));
}

But you should be using the as clause to ensure the names are as expected.