12

Possible Duplicate:
PLSQL JDBC: How to get last row ID?

I have problem getting ID from tables. I have two tables AJPES_TR and TR_LOG and PK from TR_LOG table is set as foreign key in AJPES_TR table.

In TR_LOG table I just write from which file data was imported and I want to link that PK into main table. In mySQL I was doing just fine with getID.last(); int j = getID.getInt(TR_LOG_ID); but now in Oracle this doesn't work anymore.

These are my PreparedStatements:

PreparedStatement insertData = 
  con.prepareStatement(
    "INSERT INTO T_AJPES_TR(rn,sSpre,reg,eno,davcna,Ime,Priimek) VALUES (?,?,?,?,?,?,?)"
  );
PreparedStatement select_file_log = 
  con.prepareStatement("SELECT * FROM T_AJPES_TR_LOG WHERE File_import = ?"
);
PreparedStatement getID = con.prepareStatement("SELECT * FROM T_AJPES_TR_LOG");
PreparedStatement insertFile = 
  con.prepareStatement(
    "INSERT INTO T_AJPES_TR_LOG(Date_import,File_import) VALUES (?,?)"
  );

In mySQL IDs were set as autoincrement.

How can I get ID value from TR_LOG and write that value in AJPES_TR table?

Community
  • 1
  • 1
Igor
  • 253
  • 3
  • 5
  • 14

4 Answers4

12

If a trigger is configured to automatically set the primary key field with the next value from a sequence, then you can modify your INSERT statement as follows:

INSERT INTO table (field1, field2, field3)
  VALUES (?, ?, ?)
  RETURNING primary_key_field INTO ?

Then, add the parameter values for the INSERT, an output parameter at the end for the primary key, and execute the query.

After the query is executed, grab the value of the output parameter. It should contain the value of the primary_key_field.

Matthew Rodatus
  • 1,393
  • 9
  • 18
6

In Oracle for auto-increment values used sequences

Next value is SEQUENCE_NAME.NEXTVAL, last used SEQUENCE_NAME.CURRVAL

Michael Pakhantsov
  • 24,855
  • 6
  • 60
  • 59
  • Thanks for answer. Well I saw that autoincrement is already done when admin created table. So now only I only need help about getting ID from TR_LOG table. :) – Igor Dec 16 '10 at 09:39
4

If I understood you properly you want to insert a record in one table T1, get PK of the inserted record and use is as a FK in another table T2. In this case Oracle returning clause is very useful, you can use it like this (I'm sorry I don't know hot to use it in Java, but you should get the idea):

declare
  fId int;
begin
  insert into T1(id) values seq1.nextval returning id into fId
end;

After the insert you'll have the created record id in the fId variable.

andr
  • 1,626
  • 8
  • 10
1

You are able to signal the auto increment columns in the prepare statement call

Example:

PreparedStatement ps = con.prepareStatement(sql, pkColumns);

After the insert of the database row:

ResultSet keys = ps.getGeneratedKeys();

Important: This only works, if the auto-increment values has been automatically set by a DB-Trigger via sequences

SirMad
  • 19
  • 2
  • 5
    You're also dependent on the JDBC driver used. None of the Oracle-supplied JDBC drivers supports this. – BalusC Dec 23 '10 at 13:36