Why does getGeneratedKeys return a ROWID with no numeric value?
359669Oct 27 2005 — edited Apr 16 2009All,
I was delighted to see that the 10.2.0.1.0 drivers support the JDBC 3.0 getGeneratedKeys feature. However, I can't get the darn thing to work. When I call getGeneratedKeys, I get a ResultSet with one column that contains an oracle.sql.ROWID. This ROWID has no apparent relationship the generated sequence value. How do I get the actual value?
Here is a sample of the code:
String sql = "INSERT INTO FOO (NAME) VALUES ('BAR')";
Statement stmt = connection.createStatement();
stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
ResultSet rs = stmt.getGeneratedKeys();
oracle.sql.ROWID rid = (oracle.sql.ROWID) rs.getObject(1); //getLong and getInt fail
// The following fail
// long l = rid.longValue();
// int i = rid.intValue();
String s = rid.stringValue(); // s equals "AAAXcTAAEAAADXYAAB"
The table FOO has a trigger which executes on insert that puts in the sequence value in FOO_ID. I've verified that the trigger works.
I've also tried this by inlining the sequence call in the insert, e.g. "... VALUES (MYSEQ.NEXTVAL, 'BAR')". I've also tried a PreparedStatement, etc.
Can anyone give me a sample piece of code which retrieves a numeric sequence value using getGeneratedKeys? Others have suggested using output parameters on CallableStatements, but that is not a viable option due to performance differences.
Incidentally, the getGeneratedKeys() method seems to return the ROWID even for tables in which I don't use a trigger or inline the sequence call.
Am I doing something wrong?