PreparedStatement, Oracle and getting generated keys
Given the method below (which will insert a new row and return the auto generated row number for that row and works fine for MySQL and DB2):
publicstaticint autoIncInsertImpl(PreparedStatement pstmt, String autoIncCol)
throws SQLException
{
int retVal = -1;
ResultSet rs =null;
try
{
pstmt.executeUpdate();
rs = pstmt.getGeneratedKeys();
if (rs.next())
{
retVal = rs.getInt(1);
}
}
catch (SQLException e)
{
throw e;
}
finally
{
if (rs !=null)try{ rs.close();}catch (Exception e){}
if (pstmt !=null)try{ pstmt.close();}catch (Exception e){}
}
return retVal;
}
I'm having difficulty understanding how I can implement the same method for Oracle, given that it doesn't have auto-increment columns...
thanks
[2018 byte] By [
LG_01a] at [2007-11-27 7:03:02]
# 1
Oracle can generate keys using a sequence. The SQL to create a sequence is something like "create sequence foo". I'd suggest a separate sequence for each table or ID column, so if you had a table PERSON with a column PERSON_ID, then something like "create sequence seq_person_id" would work.
When you insert, you can use the sequence like this:
insert into PERSON(PERSON_ID, PERSON_NAME)
values (seq_person_id.nextval, 'john smith');
You can't make a sequence a default value for a column, but you can use a trigger to fake it. Probably you can find examples from Google.
The hard / lame part is getting the ID back after an insert.
Starting with 10gR2 getGeneratedKeys() should work (http://java-x.blogspot.com/2006/09/oracle-jdbc-automatic-key-generation.html). With older versions (back to 8?), I think you have to use a callable statement with a "returning" clause. It looks something like this:
CallableStatement cstmt = con.prepareCall("begin insert into t_person(person_id, person_name) values(seq_person_id.nextval, ?) returning person_id in ?; end;");
cstmt.setString(1, "john smith");
cstmt.registerOutParameter(2,Types.INTEGER);
cstmt.execute();
retVal = cstmt.getInt(2);
I think this is available since Oracle 8. It's kind of lame that it's so hard and you have to change existing INSERT queries.
Jemiah
Message was edited by:
fishninja007
