SQLException: Exhausted Resultset

Question

I have a problem with my JDBC code. This is the relevant code:

/** method for checking password into the Oracle database */
public String CheckUserDB(String userToCheck) throws SQLException {
  String storedPassword;
  if (ds == null) throw new SQLException("No data source");      
  Connection conn = ds.getConnection();
  if (conn == null) throw new SQLException("No connection");      

  try {
    conn.setAutoCommit(false);
    boolean committed = false;
    try {
      PreparedStatement passwordQuery = conn.prepareStatement(
        "SELECT passwd from USERS WHERE userz = ?");
      passwordQuery.setString(1, userToCheck);

      ResultSet result = passwordQuery.executeQuery();

      result.next();

      storedPassword = result.getString("passwd");                          

      conn.commit();
      committed = true;
    } finally {
      if (!committed) conn.rollback();
    }
  }
  finally {               
    conn.close();
  }       
  return storedPassword;
}

This is the exception:

java.sql.SQLException: Exhausted Resultset
    oracle.jdbc.driver.OracleResultSetImpl.getString(OracleResultSetImpl.java:1270)
    oracle.jdbc.driver.OracleResultSet.getString(OracleResultSet.java:494)
    org.jboss.jca.adapters.jdbc.WrappedResultSet.getString(WrappedResultSet.java:1359)
    com.dx.sr_57.user_check.CheckUserDB(user_check.java:100)
    com.dx.sr_57.user_check.user_compare(user_check.java:123)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:601)
    org.apache.el.parser.AstValue.invoke(AstValue.java:196)
    org.apache.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:276)
    com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:105)
    javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:88)
    com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:102)
    javax.faces.component.UICommand.broadcast(UICommand.java:315)
    javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:794)
    javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:1259)
    com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:81)
    com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
    com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:118)
    javax.faces.webapp.FacesServlet.service(FacesServlet.java:593)

How is this caused and how can I solve it?

i think this link will help you to trace your problem due to result set http://stackoverflow.com/questions/3502005/java-sql-sqlexception-exhausted-resultset — Chandra Sekhar, Dec 13 '11 at 20:51

score 5 · Accepted Answer · answered Dec 13 '11 at 20:43

5

result.next();

storedPassword = result.getString("passwd");

You are not checking the return value of next. If you have no rows you get into trouble...

if(result.next()){
   storedPassword = result.getString("passwd");
}

answered Dec 13 '11 at 20:43

Udo Held

12,314
11
67
93

score 4 · Answer 2 · answered Jun 28 '13 at 15:27

The problem might not be with the code but instead could be the database. Double check that the TABLE IS NOT EMPTY. You get this error if the table is empty. Keep in mind that databases like Oracle require a commit after all your insert, update, alter statements .Your changes might not be visible outside the database till you run a commit over the your db, I was having this problem for quite a long time. I kept on checking the table with select statement but the problem with my oracle db was that I had not issued a commit over my db.

score 2 · Answer 3 · answered Mar 12 '14 at 16:54

use the varchar data type instead of char in the oracle database.

if you use char then actual size with blank space of database is compared with query parameter

and query doesnt get exucuted that is result gets false.

So use the varchar datatype in oracle database.

SQLException: Exhausted Resultset

3 Answers3

Linked

Related