java.sql.SQLException: Exhausted Resultset

Question

I get the error java.sql.SQLException: Exhausted ResultSet to run a query against an Oracle database. The connection is via a connection pool defined in Websphere. The code executed is as follows:

if (rs! = null) (
    while (rs.next ()) (
        count = rs.getInt (1);
    )
)

I note that the resultset contains data (rs.next ())

Thanks

Answer 1

I've seen this error while trying to access a column value after processing the resultset.

if (rs != null) {
  while (rs.next()) {
    count = rs.getInt(1);
  }
  count = rs.getInt(1); //this will throw Exhausted resultset
}

Hope this will help you :)

Answer 2

Try this:

if (rs != null && rs.first()) {
    do {
        count = rs.getInt(1);
    } while (rs.next());
}

Answer 3

If you reset the result set to the top, using rs.absolute(1) you won't get exhaused result set.

while (rs.next) {
    System.out.println(rs.getString(1));
}
rs.absolute(1);
System.out.println(rs.getString(1));

You can also use rs.first() instead of rs.absolute(1), it does the same.

Answer 4

When there is no records returned from Database for a particular condition and When I tried to access the rs.getString(1); I got this error "exhausted resultset".

Before the issue, my code was:

rs.next();
sNr= rs.getString(1);

After the fix:

while (rs.next()) {
    sNr = rs.getString(1);
}

Answer 5

This exception occurs when the ResultSet is used outside of the while loop. Please keep all processing related to the ResultSet inside the While loop.

Answer 6

This occurs typically when the stmt is reused butexpecting a different ResultSet, try creting a new stmt and executeQuery. It fixed it for me!

Answer 7

Problem behind the error: If you are trying to access Oracle database you will not able to access inserted data until the transaction has been successful and to complete the transaction you have to fire a commit query after inserting the data into the table. Because Oracle database is not on auto commit mode by default.

Solution:

Go to SQL PLUS and follow the following queries..

SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 28 15:29:43 2017

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: scott
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> desc empdetails;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ENO                                                NUMBER(38)
 ENAME                                              VARCHAR2(20)
 SAL                                                FLOAT(126)

SQL> insert into empdetails values(1010,'John',45000.00);

1 row created.

SQL> commit;

Commit complete.

Answer 8

Please make sur that res.getInt(1) is not null. If it can be null, use Integer count = null; and not int count =0;

Integer count = null;
    if (rs! = null) (
                    while (rs.next ()) (
                        count = rs.getInt (1);
                    )
                )

Answer 9

You are getting this error because you are using the resultSet before the resultSet.next() method.

To get the count the just use this:

while (rs.next ()) `{ count = rs.getInt (1); }

You will get your result.