Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Getting exception when trying to get the id of the new inserted row

 
Raghu Sundar
Ranch Hand
Posts: 40
Chrome Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am trying to get the primary key of the newly inserted record(which is an identity field) The code snippet is as below



When i try to execute i am getting the exception com.microsoft.sqlserver.jdbc.SQLServerException: A result set was generated for update. which points to executeUpdate statement.I am using sqljdbc4 jar and SQL version is SQL server 2008. Please help to fix this issue.Thanks in advance.
 
Ravi Sree
Ranch Hand
Posts: 64
Java MySQL Database PHP
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

If you need to call a stored procedure, java.sql.CallableStatement is what you use & not PreparedStatement.

But then again why store procedure if could use PreparedStatement (while inserting/updating the record).

Is it long time after the insertion/updation that you need to fetch the auto-generated id..?

Specify the scenario and whatever you have tried fully.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It is actually possible to call a stored procedure using PreparedStatement. I don't think the problem lies here. PreparedStatement doesn't allow to register output parameters for stored procedures, as CallableStatement does, but the Statement.RETURN_GENERATED_KEYS uses a different mechanism anyway. I have only seen this mechanism used with INSERT SQL statements, however, and I don't think it is going to work with stored procedures.

If your procedure creates a row and you want to get back Id generated for that row, then I'd suggest adding an output parameter to the procedure, or converting it to a function. The procedure (or function) would insert the row and return the generated ID to the caller (you'll probably use database-specific code in your stored procedure to obtain the generated key). You'd then use CallableStatement to call that procedure (or function) and properly register output parameters to obtain it.

Astonishingly, the Oracle's JDBC tutorial don't seem to cover using CallableStatement very well. They are mentioned a bit here. You might also read the CallableStatement javadoc (it is actually linked from here).
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic