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

Question

I am trying to get the row count of a result set by:

rs.last();
int row_count = rs.getRow();

but im getting an Invalid operation for forward only resultset : last error. The result set is getting its data from an Oracle 10g database.

Here is how i set up my connection:

    Class.forName("oracle.jdbc.driver.OracleDriver");
    String connectionString = "jdbc:oracle:thin:@" + oracle_ip_address + ":" + oracle_db_port + ":" + oracle_db_sid;
    Connection conn = DriverManager.getConnection(connectionString, oracle_db_username, oracle_db_password);

score 41 · Accepted Answer · edited Jan 08 '18 at 11:59

41

ResultSet.last() and other "absolutely-indexed" query operations are only available when the result set is scrollable; otherwise, you can only iterate one-by-one through the forward-only result set.

The following example (from the javadocs) demonstrates how to create a scrollable ResultSet.

Statement stmt = con.createStatement(
    ResultSet.TYPE_SCROLL_INSENSITIVE,
    ResultSet.CONCUR_READ_ONLY
);
ResultSet rs = stmt.executeQuery("SELECT a, b FROM TABLE2");

Keep in mind that there are performance implications to using scrollable queries. If the goal of this particular ResultSet is only to grab its last value, please consider refining your query to return only that result.

edited Jan 08 '18 at 11:59

Mark Rotteveel

100,966
191
140
197

answered Jan 25 '12 at 17:30

cheeken

33,663
4
35
42

2

As per latest specification, we have to add two parameters to the createStatement() - `Statement statement = con.createStatement(resultSetType, resultSetConcurrency);` as described by cheeken, we have to pass `ResultSet.TYPE_SCROLL_INSENSITIVE` but with that we also have to pass one more parameter - please refer this - `resultSetType a result set type; one of ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_INSENSITIVE, or ResultSet.TYPE_SCROLL_SENSITIVE resultSetConcurrency a concurrency type; one of ResultSet.CONCUR_READ_ONLY or ResultSet.CONCUR_UPDATABLE` – Paresh Mar 09 '16 at 04:49
Please help me it's saying `The method createStatement() in the type Connection is not applicable for the arguments (int)` when I try to execute `Statement stmt = con.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE ); ` – Squareoot Jan 12 '17 at 14:12
@StringForever refer to what PareshKumar has mentioned. – Skynet Jul 28 '17 at 12:02
1

@danielad Actually, that method signature has always required two parameters. The code in the original was always incorrect. I have now corrected it (and updated the broken link). – Mark Rotteveel Jan 08 '18 at 11:58

score 5 · Answer 2 · edited Mar 08 '18 at 06:27

5

PreparedStatement ps = conn.prepareStatement ("SELECT * FROM
         EMPLOYEE_TABLE WHERE LASTNAME = ?" ,
         ResultSet.TYPE_SCROLL_INSENSITIVE , 
         ResultSet.CONCUR_UPDATABLE ,
         ResultSet.HOLD_CURSOR_OVER_COMMIT) ;

For prepared statements, you must specify, at a minimum, both the type and the concurrency mode for last() and isLast() to work.

edited Mar 08 '18 at 06:27

Sagar Pudi

4,634
3
32
51

answered Dec 03 '13 at 10:16

Mahdi Esmaeili

555
7
11

isLast() does not require the cursor to be scrollable. last() requires it. – isaac.hazan Jun 28 '17 at 09:46
@isaac.hazan, not true for any driver: https://stackoverflow.com/questions/10597068/islast-method-for-a-type-forward-only-result-set – Vadzim Feb 09 '19 at 08:22
ResultSet.CONCUR_UPDATABLE is NOT recommended to be used in commercial projects. Please avoid if possible. – Dhruv Singhal Jul 21 '19 at 17:46

score 0 · Answer 3 · edited Jan 01 '23 at 09:20

0

String rowCount = "SELECT COUNT(*) as numberOfRows FROM (SELECT * FROM names)";
Statement stat = con.createStatement();
ResultSet rs = stat.executeQuery(rowCount);
int lastRow = rs.getInt("numberOfRows");
System.out.println(lastRow);

Instead of SELECT * FROM names use your query.

edited Jan 01 '23 at 09:20

F. Müller

3,969
8
38
49

answered Dec 31 '22 at 13:31

juropico

1
1

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

3 Answers3

Linked

Related