4

I'm trying to retrieve the last generated auto-increment id in JSP something like the PHP mysql_insert_id() function or the MySQL last_insert_id() function using the getGeneratedKeys() method as mentioned in this question (I'm using Oracle 10g though).

I have a TRANSPORTER table in Oracle database which has a column named TRANSPORTER_ID of type NUMBER(35, 0) which is mapped to a BigDecimal type in Java and it is a sequence generated primary key.

Connection con;
ResultSet rs;
PreparedStatement ps;
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String user = "root";
String pwd = "root";

Class.forName("oracle.jdbc.OracleDriver").newInstance();
con = DriverManager.getConnection(url, user, pwd);

ps = con.prepareStatement("INSERT INTO transporter(transporter_name, transporter_website)VALUES(?, ?)");
ps.setString(1, "New");
ps.setString(2, "New Website");
ps.executeUpdate();

BigDecimal id = new BigDecimal(0);
rs = ps.getGeneratedKeys();

while (rs.next()) {
    id = rs.getBigDecimal("transporter_id");
}

out.println("The generated id is : " + id);

After the insertion operation is performed, the code attempts to invoke this method rs = ps.getGeneratedKeys();above the preceding while loop and it fails with following exception.

javax.servlet.ServletException: java.sql.SQLException: operation not allowed

I have tried with the Oracle JDBC Driver version - 10.2.0.5.0 and when it failed I downloaded a higher version which is 11.2.0.3.0 but to no avail. What might be the reason?

Community
  • 1
  • 1
Tiny
  • 27,221
  • 105
  • 339
  • 599
  • Have a look at [this](http://stackoverflow.com/questions/4458734/get-last-id-on-inserted-row-in-oracle-db), might be useful and [this](http://1ikun.wordpress.com/2011/06/14/how-to-get-the-auto-generated-keys-with-jdbc-in-oraclesqlserver/) – Jacob Nov 08 '12 at 20:17
  • @Polppan - Those links turned out to be very helpful and ultimately led me towards the solution I wanted. Thank you very much. – Tiny Nov 08 '12 at 21:12

2 Answers2

5

I found some documentation about this. It's for 11g, but the situation probably won't be better for 10g.

The proximal cause of your error is probably the limitation that:

You need to access the ResultSet object returned from getGeneratedKeys method by position only

It seems the Oracle driver also requires you to identify the key column in order for it to retrieve the key column instead of just the ROWID. Sample code for this is included in the linked documentation.

Samuel Edwin Ward
  • 6,526
  • 3
  • 34
  • 62
  • 2
    I have tried as mentioned in [this](http://stackoverflow.com/a/3552353/1391249) answer and both the approaches worked. In the first approach (the one with the `PreparedStatement`) however, I had to remove the sequence generated id from the SQL statement i.e `seq_mytable.NEXTVAL()` as specified in that answer. Thank you vary much for your useful answer. – Tiny Nov 08 '12 at 20:58
0

ps = con.prepareStatement("INSERT INTO transporter(transporter_name, transporter_website)VALUES(?, ?)", new String[] {"transporter_id"});

Guilherme Oliveira
  • 121
  • 1
  • 8