DB2 Express UDB 9 with db2jcc JDBC driver- Issues with Auto generated field

[Zunil]

Hi All,
We are testing one of our component with JDBC + DB2 using db2jcc
driver. We afced a strange issue.
Scenario is like this.
1. We insert 1 record to a table having IDENTITY column as the primary
key and retrieve the newly inserted identity value using
getGeneratedKeys() API of JDBC 3.0. It works fine
2. Immediate after that we insert a record into another table which is
having no identity column. But after insert if I call
getGeneratedKeys(), it is returning me a valid ResultSet and if get
the value, it is returning the identity value of the first insert.

Why it is happening like that? This creates trouble as the client
application keeps on inserting records , some to tables having
IDENTITY column and some to tables without IDENTITY. But once an
insert to tables having IDENTITY happens, all other insert to tables
without identity returns theat idenity value.

We earlier tested MS SQL server 2005 , Oracle 10g and MySQL 5. All
worked fine.

This is the sample code.
{
String sql = "INSERT INTO TEST_TABLE_AUTOID(COL2, COL3) values(?,?)";
ResultSet rs = null;

PreparedStatement stmt = connection1.prepareStatement(sql,
PreparedStatement.RETURN_GENERATED_KEYS);
stmt.setString(1, "Test11");
stmt.setString(2, "Test11");
int ret = stmt.executeUpdate();
rs = stmt.getGeneratedKeys();
if(rs != null) {
while(rs.next()) {
System.out.println("Inside ");
String data = rs.getString(1);
// Get automatically generated key
// values
System.out.println("automatically generated key value = " +
data);

}
}
rs.close();
stmt.close();
connection1.commit();
}

{
String sql = "INSERT INTO TEST_TABLE(COL1, COL2)
values(?,?)";
ResultSet rs = null;

PreparedStatement stmt = connection1.prepareStatement(sql,
PreparedStatement.RETURN_GENERATED_KEYS);
stmt.setInt(1, 14);
stmt.setString(2, "Test14");
int ret = stmt.executeUpdate();
rs = stmt.getGeneratedKeys();
if(rs != null) {
while(rs.next()) {
System.out.println("Inside");
java.math.BigDecimal idColVar = rs.getBigDecimal(1);
//String data = rs.getString(1);
// Get automatically generated key
// values
System.out.println("automatically generated key value = " +
idColVar);

}
}
rs.close();
stmt.close();
connection1.commit();
}

[joe.we...@gmail.com]

Hi. It's clearly a driver bug. The driver should nullify any of a
statement's values related to an execution
when the statement is re-executed.
Joe Weinstein at Oracle

[Serge Rielau]

joeN...@bea.com wrote:
> Hi. It's clearly a driver bug. The driver should nullify any of a
> statement's values related to an execution
> when the statement is re-executed.

Yes, I have discussed with the JCC team and it's a known issue.
If you want to ensure it's fixed in a timely fashion probably best to
open a PMR, so you can track when/that it's done.

> Joe Weinstein at Oracle
Are you the one (ex-BEA) who survived?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab