java.sql.SQLException: Exhausted Resultset error

Question

I'm facing the problem with this exception java.sql.SQLException: Exhausted Resultset in following code. I'm sure my query returns only one value. Even If I don't use rs.next(); it throws the error java.sql.SQLException: ResultSet.next was not called. Could you please help?

FYI, I'm using the another result set in main where this menthod from another class is called. will it affect?

Thanks

public static String getdispname(Connection conn, String resname) 
throws SQLException, Exception {

            //String resname = "";
            String returnValue = "";
            String querystring = "";

            //Query to select the displayname from resid

            querystring += "select distinct display_name";
            querystring += " from cust_rally_team_member";
            querystring += " where display_name like '%"+ resid +"%'";

            // Create select statement
            Statement stmt = conn.createStatement();

            try {
                // Execute statement
                ResultSet rs = stmt.executeQuery(querystring);
                if (rs!= null) { 
            while (rs.next()) {
            returnValue = rs.getString("display_name");
            } catch (SQLException ex) {
                throw new SQLException(ex);
            } catch (Exception ex) {
                throw new Exception(ex);
            }
            // Close statement
            finally {
                stmt.close();
            }

            return returnValue;

Hi guys, Thanks for all your answers. I think the result set has no value. I believe `querystring += " where display_name like '%resid%'";` may not work with like statement with a parameter. Correct me If I'm wrong — user2335123, Apr 22 '14 at 13:03
Check whether your sql query with like does return any rows in backend. — Jacob, Apr 22 '14 at 13:06
By the way on a separate note, `statement` you are closing in `finally`, similarly do not forget to close `resultset` and `connection`. It is very important. — Jacob, Apr 22 '14 at 13:09
Yes its providing the one result as expected. But when I add it in Java, it dont give result. When I replace variable %resid% in the query to %amit%, it provides value. Is the query string the correct reprsentation? — user2335123, Apr 22 '14 at 13:11
Found the correct statement '%"+ resid +"%', this solved the issue — user2335123, Apr 22 '14 at 13:26

score 5 · Accepted Answer · answered Apr 22 '14 at 12:37

5

Try as

if (rs! = null) { 
 while (rs.next()) {
  returnValue = rs.getString("display_name");
}
......

answered Apr 22 '14 at 12:37

Jacob

14,463
65
207
320

Got the error ** Uncompilable source code - incompatible types required: boolean found: java.sql.ResultSet** – user2335123 Apr 22 '14 at 12:48
for the above code you should use while(rs.hasNext()) because while expects boolean but you have passed rs.next() which returns resultset so its not matching and hence the compile time error..change it to rs.hasNext() – Karibasappa G C Apr 22 '14 at 12:52
@user1763507 `java.sql.ResultSet` does not have a `hasNext()` method, and `next()` does return a boolean. – Mark Rotteveel Apr 22 '14 at 12:56
@user2335123 Post your modified code by editing your question so that others would be able to point out what causing the error. – Jacob Apr 22 '14 at 12:58
Thanks @Polppan. I used your resultset statement and modified my like statement. I updated my code with the updates as advised – user2335123 Apr 22 '14 at 13:29
@user2335123 Glad I could help. Have a look at [this](http://stackoverflow.com/questions/3271249/difference-between-statement-and-preparedstatement) as well. – Jacob Apr 22 '14 at 14:09
Sure will use Prepared statement. Thanks for the hint – user2335123 Apr 22 '14 at 14:17

Paul · Answer 2 · 2014-04-22T14:47:57.687

Try:

returnValue = rs.next() ? rs.getString("display_name") : null;

You don't need to check if the rs is null. It won't be - assuming the executeQuery() returned rather than raising an exception. (Though the returned result set might have 0 rows).

You also don't need to loop over the result set, assuming you really know that you expect back a single row. (Though, given the query, that seems unlikely.)

score 0 · Answer 3 · answered Apr 22 '14 at 12:54

0

use by your modification like below

if (rs != null && rs.first()) {
    do {
        returnValue = rs.getString(("display_name");
    } while (rs.next());
}

answered Apr 22 '14 at 12:54

Karibasappa G C

2,686
1
18
27

Duplicate of your other answer: http://stackoverflow.com/a/23219863/466862 and still as wrong as `first()` may throw an exception when called on a forward-only result set. – Mark Rotteveel Apr 22 '14 at 12:57

Sachindra N. Pandey · Answer 4 · 2015-09-23T08:44:11.613

0

I am using Oracle 10g database , i found same error "java.sql.SQLException: Exhausted Resultset error". I just grant permission in database and solved my probblem.

SQL> grant insert,update,delete on "table-name" to "database_name";

edited Sep 23 '15 at 08:44

answered Sep 22 '15 at 07:31

Sachindra N. Pandey

1,177
17
15

java.sql.SQLException: Exhausted Resultset error

4 Answers4