0

I've seen this(and similar) questions explaining how to insert and get the generated ID. Unfortunately, none of the solutions that I've found so far work in my current setting.

My code is currently like the following:

rs.moveToInsertRow();
rs.updateObject(...)
rs.updateObject(...)
rs.updateObject(...)
...
rs.insertRow();

None of the inserted fields contains the ID. The ID is generated by the database. After rs.insertRow(); I want to get the ID of that row.

using rs.getObject("ID"); after inserting gives me

com.microsoft.sqlserver.jdbc.SQLServerException: The result set has no current row.

Is there any way to make this work as is, or do I have to redo this to drop the cursors and use an INSERT statement with returnkeys?

Community
  • 1
  • 1
Cruncher
  • 7,641
  • 1
  • 31
  • 65
  • Can you include the sql query that you are using? – Jenn Jul 18 '14 at 17:22
  • possible duplicate of [Getting index of inserted rows from a MySQL database](http://stackoverflow.com/questions/4483568/getting-index-of-inserted-rows-from-a-mysql-database) – Hannes Jul 18 '14 at 17:22
  • @Hannes I don't see how that's a duplicate. That's exactly what I said I found how to do. This question is about cursors. If the answer is that you cannot do it with the cursor, then so be it. But that doesn't make it a duplicate. I'm also not using MySQL. – Cruncher Jul 18 '14 at 17:26
  • @Jenn it's just a select * from a table. I can convert this to an insert statement. I would prefer not to change too much of the code that's already there though. If this cannot be done with cursors I will accept that though – Cruncher Jul 18 '14 at 17:27
  • @Cruncher I'm really confused about what you are asking. I thought you were trying to get the id that is generated by the database after you do an insert, but you are actually using a select? You cannot add data to a table without an insert statement. – Jenn Jul 18 '14 at 17:34
  • @Jenn You can with cursors. After selecting, if you use `moveToInsertRow` then `updateObject` for each field, then `insertRow` it will insert a row. – Cruncher Jul 18 '14 at 17:38
  • @Jenn http://docs.oracle.com/javase/6/docs/api/java/sql/ResultSet.html#insertRow() – Cruncher Jul 18 '14 at 17:47
  • @Cruncher I did not know that you could do that with cursors. That's pretty sweet. – Jenn Jul 18 '14 at 19:14

1 Answers1

1

The following code seems to work for me(in all cases I've observed so far):

rs.moveToInsertRow();
rs.updateObject(...)
rs.updateObject(...)
rs.updateObject(...)
...
rs.insertRow();

rs.last();
return rs.getObject("ID");

This is with SQL Server 2012, using auto increment by 1.

It may be possible that with other drivers, or configurations that this may not work.

Cruncher
  • 7,641
  • 1
  • 31
  • 65
  • This is the [documented way](https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-usagenotes-last-insert-id.html#connector-j-examples-autoincrement-updateable-resultsets) to do this for MySQL/MariaDB using Connector/J as well. (This answer may help users of other databases as well, so I thought I'd mention it). – Christopher Schultz Dec 10 '19 at 17:37