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
getGeneratedKey s() 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
getGeneratedKey s(), 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_AUTO ID(COL2, COL3) values(?,?)";
ResultSet rs = null;
PreparedStateme nt stmt = connection1.pre pareStatement(s ql,
PreparedStateme nt.RETURN_GENER ATED_KEYS);
stmt.setString( 1, "Test11");
stmt.setString( 2, "Test11");
int ret = stmt.executeUpd ate();
rs = stmt.getGenerat edKeys();
if(rs != null) {
while(rs.next() ) {
System.out.prin tln("Inside ");
String data = rs.getString(1) ;
// Get automatically generated key
// values
System.out.prin tln("automatica lly generated key value = " +
data);
}
}
rs.close();
stmt.close();
connection1.com mit();
}
{
String sql = "INSERT INTO TEST_TABLE(COL1 , COL2)
values(?,?)";
ResultSet rs = null;
PreparedStateme nt stmt = connection1.pre pareStatement(s ql,
PreparedStateme nt.RETURN_GENER ATED_KEYS);
stmt.setInt(1, 14);
stmt.setString( 2, "Test14");
int ret = stmt.executeUpd ate();
rs = stmt.getGenerat edKeys();
if(rs != null) {
while(rs.next() ) {
System.out.prin tln("Inside");
java.math.BigDe cimal idColVar = rs.getBigDecima l(1);
//String data = rs.getString(1) ;
// Get automatically generated key
// values
System.out.prin tln("automatica lly generated key value = " +
idColVar);
}
}
rs.close();
stmt.close();
connection1.com mit();
}
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
getGeneratedKey s() 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
getGeneratedKey s(), 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_AUTO ID(COL2, COL3) values(?,?)";
ResultSet rs = null;
PreparedStateme nt stmt = connection1.pre pareStatement(s ql,
PreparedStateme nt.RETURN_GENER ATED_KEYS);
stmt.setString( 1, "Test11");
stmt.setString( 2, "Test11");
int ret = stmt.executeUpd ate();
rs = stmt.getGenerat edKeys();
if(rs != null) {
while(rs.next() ) {
System.out.prin tln("Inside ");
String data = rs.getString(1) ;
// Get automatically generated key
// values
System.out.prin tln("automatica lly generated key value = " +
data);
}
}
rs.close();
stmt.close();
connection1.com mit();
}
{
String sql = "INSERT INTO TEST_TABLE(COL1 , COL2)
values(?,?)";
ResultSet rs = null;
PreparedStateme nt stmt = connection1.pre pareStatement(s ql,
PreparedStateme nt.RETURN_GENER ATED_KEYS);
stmt.setInt(1, 14);
stmt.setString( 2, "Test14");
int ret = stmt.executeUpd ate();
rs = stmt.getGenerat edKeys();
if(rs != null) {
while(rs.next() ) {
System.out.prin tln("Inside");
java.math.BigDe cimal idColVar = rs.getBigDecima l(1);
//String data = rs.getString(1) ;
// Get automatically generated key
// values
System.out.prin tln("automatica lly generated key value = " +
idColVar);
}
}
rs.close();
stmt.close();
connection1.com mit();
}
Comment