0

Possible Duplicate:
rs.last() gives Invalid operation for forward only resultset : last

So I'm trying to understand the result set cursor and I'm having an issue with where the cursor is apparently.

I have a very small application that assigns a new integer id automatically, which will be the last entry into the database, therefore the highest integer. I'm trying to get to the last entry like this (rs is result set) so I can use its value:

rs.last

and then assigning the_new_id to rs.getInt(1)...

However, I get the "Invalid operation for forward only resultset : last" sql exception.

Right now I have a big "kludge" to make this work:

  while(rs.next())
       your_new_id = rs.getInt(1);

and then I just assign the new id that way. :-\

How can I implement this same behavior more elegantly using last?

Any help is appreciated.

Community
  • 1
  • 1
Matt
  • 5,408
  • 14
  • 52
  • 79
  • 1
    Its not safe . What if some one inserts in between ? You wont get latest – Hardik Mishra Nov 20 '12 at 05:34
  • most databases have a way to generate unique values for use in synthetic key fields. In db2, I believe you're supposed to use [a sequence](http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.intro%2Fsrc%2Ftpc%2Fdb2z_sequences.htm) – Lee Nov 20 '12 at 05:36
  • Good point Hrdik, and I have thought about this. This is the **only** place that inserts will be made however. So for now it's guaranteed not to cause any problems. – Matt Nov 20 '12 at 05:37
  • @Lee Right, I did use a sequence to implement the increment. – Matt Nov 20 '12 at 05:38
  • so, why not just `select * from mytable order by id desc`. Then the *first* row will contain your desired result. You can add `fetch first 1 rows only` to the end to improve performance if you're only interested in the first row. – Lee Nov 20 '12 at 05:46
  • @Lee Thanks, I think that's a good idea. I still can't get it to work though. So I get the query in DESC order and then with that rs I use rs.first() and then assign the new id like new_id = rs.getInt(1). But now I have a "missing expression" sql exception. :-\ EDIT:: Sorry, not missing exp, rather "Invalid op for forward only resultse: first" – Matt Nov 20 '12 at 06:01
  • 2
    If you **are** using a sequence, you should use `getGeneratedKeys()` to get the ID that was generated during your `INSERT`. That saves you a lot of roundtrips to the database. –  Nov 20 '12 at 08:38

2 Answers2

1

By default, result sets are forward-only, meaning that the only thing you can do to change the position of the cursor is next() (which is all you need if you order by ID in descending order).

The javadoc explains it:

A default ResultSet object is not updatable and has a cursor that moves forward only. Thus, you can iterate through it only once and only from the first row to the last row. It is possible to produce ResultSet objects that are scrollable and/or updatable. The following code fragment, in which con is a valid Connection object, illustrates how to make a result set that is scrollable and insensitive to updates by others, and that is updatable. See ResultSet fields for other options. 

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                     ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT a, b FROM TABLE2");
// rs will be scrollable, will not show changes made by others,
// and will be updatable
JB Nizet
  • 678,734
  • 91
  • 1,224
  • 1,255
1

Apparently you are trying to retrieve the ID that was generated by a previous INSERT statement. You should not use a separate SELECT statement for that (which is not transaction safe and does impose an unnecessary load on the database).

To retrieve a generated ID, use the following JDBC calls:

String insert = "insert into some_table (... ";
PreparedStatement pstmt = con.prepareStatement(insert, new String[] {"ID"});
int rowsInserted = pstmt.executeUpdate();
ResultSet idResult = pstmt.getGeneratedKeys();
int newId = -1;
if (rs.next()) {
  newId = rs.getINt(1);
}

This will retrieve the value that was generated for the ID column during the INSERT. This will be faster than doing a SELECT to get the latest ID, but more importantly it is transaction safe.