DB2 Express UDB 9 with db2jcc JDBC driver- Issues with Auto generatedfield

Question posted by: Zunil (Guest) on July 14th, 2008 01:25 PM

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();
}

Not the answer you were looking for? Post your question . . .
190,472 Experts ready to help you find a solution.
Sign up for a free account, or Login (if you're already a member).