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

Has the statement or connection be closed while reading the result set? — Arne Burmeister, Aug 17 '10 at 11:50
@Arne: that would rather have thrown a `SQLException: ResultSet is closed`. — BalusC, Aug 17 '10 at 12:14
In the future, add the stacktrace and point the line. Seeing the accepted answer the line where it was thrown was **absolutely not** in the code which you posted in your question. You should practice more with interpreting stacktraces. I deleted my answer. — BalusC, Aug 17 '10 at 12:55
Yup, was absolutely not in the code because I figured it :-) Always i've seen this error was caused for same issue. — sourcerebels, Aug 17 '10 at 12:58

sourcerebels · Accepted Answer · 2018-03-21T07:39:36.320

36

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 :)

edited Mar 21 '18 at 07:39

answered Aug 17 '10 at 12:50

sourcerebels

5,140
1
32
52

Simple and Clear, Thanks – The Guest Oct 20 '15 at 22:25
will it create the same exception for other databases(like mysql, sql server etc)? – Ajay Jayavarapu Jun 13 '16 at 06:12
I don't know. Probably yes. I supose it depends on database vendor :-) – sourcerebels Jun 15 '16 at 07:44
1

white space between '! =' is not allowed :) – Muhammad Suleman Mar 21 '18 at 06:49
Thanks. Changed my answer. – sourcerebels Mar 21 '18 at 10:17

score 4 · Answer 2 · answered Aug 17 '10 at 11:50

4

Try this:

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

answered Aug 17 '10 at 11:50

cadrian

7,332
2
33
42

zmorris · Answer 3 · 2014-05-08T17:01:06.840

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.

edited May 08 '14 at 17:01

answered Apr 14 '14 at 14:30

zmorris

1,057
2
12
23

score 2 · Answer 4 · edited Mar 24 '15 at 18:16

2

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);
}

edited Mar 24 '15 at 18:16

jh314

27,144
16
62
82

answered Mar 24 '15 at 17:56

Jose Anand

35
4

score 2 · Answer 5 · answered Aug 22 '16 at 16:24

2

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.

answered Aug 22 '16 at 16:24

Apurva

21
1

score 1 · Answer 6 · answered Jun 04 '13 at 00:14

1

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

answered Jun 04 '13 at 00:14

Rohit

11
1

score 1 · Answer 7 · edited Nov 28 '17 at 10:19

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.

score 0 · Answer 8 · answered May 28 '14 at 08:32

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);
                    )
                )

score 0 · Answer 9 · answered Mar 29 '18 at 16:30

0

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.

answered Mar 29 '18 at 16:30

Prakhar Sharma

21
1
3

java.sql.SQLException: Exhausted Resultset

9 Answers9

Linked

Related