Hi Friends, Here i'm facing a problem not able to findout the solution. Can someone help me ? Here is the error :
[Wed Sep 05 18:29:24 GMT+05:30 2001] java.sql.SQLException: Exhausted Resultset
[Wed Sep 05 18:29:24 GMT+05:30 2001] at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:114)
[Wed Sep 05 18:29:24 GMT+05:30 2001] at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:156)
[Wed Sep 05 18:29:24 GMT+05:30 2001] at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:219)
[Wed Sep 05 18:29:24 GMT+05:30 2001] at oracle.jdbc.driver.ScrollableResultSet.getOracleObject(ScrollableResultSet.java:382)
[Wed Sep 05 18:29:24 GMT+05:30 2001] at oracle.jdbc.driver.ScrollableResultSet.getString(ScrollableResultSet.java:394)
[Wed Sep 05 18:29:24 GMT+05:30 2001] at oracle.jdbc.driver.OracleResultSet.getString(OracleResultSet.java:1308)
[Wed Sep 05 18:29:25 GMT+05:30 2001] at Gold.goldCreate(Gold.java, Compiled Code)
[Wed Sep 05 18:29:25 GMT+05:30 2001] at pagecompile._gold._spot_xjsp._jspService(_spot_xjsp.java, Compiled Code)
[Wed Sep 05 18:29:25 GMT+05:30 2001] at newatlanta.servletexec.JSP10HttpJspPage.service(JSP10HttpJspPage.java)
[Wed Sep 05 18:29:25 GMT+05:30 2001] at newatlanta.servletexec.JSP10Servlet.service(JSP10Servlet.java)
[Wed Sep 05 18:29:25 GMT+05:30 2001] at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
[Wed Sep 05 18:29:25 GMT+05:30 2001] at newatlanta.servletexec.ServletExec.CallServletService(ServletExec.java)
[Wed Sep 05 18:29:25 GMT+05:30 2001] at newatlanta.servletexec.ServletExec.processServletRequest(ServletExec.java)
[Wed Sep 05 18:29:25 GMT+05:30 2001] at newatlanta.servletexec.ServletExec.processServletAlias(ServletExec.java, Compiled Code)
[Wed Sep 05 18:29:25 GMT+05:30 2001] at newatlanta.servletexec.ServletExec.ProcessRequest(ServletExec.java, Compiled Code)
[Wed Sep 05 18:29:25 GMT+05:30 2001] at newatlanta.servletexec.ServletExec.ProcessRequest(ServletExec.java)
[Wed Sep 05 18:29:25 GMT+05:30 2001] at newatlanta.servletexec.ServletExecService.processServletRequest(ServletExecService.java)
[Wed Sep 05 18:29:25 GMT+05:30 2001] at newatlanta.servletexec.ServletExecService.Run(ServletExecService.java, Compiled Code)
[Wed Sep 05 18:29:25 GMT+05:30 2001] at newatlanta.servletexec.HttpServerRequest.run(HttpServerRequest.java)
[Wed Sep 05 18:29:25 GMT+05:30 2001] Exception Occured while inserting data into DB !! :
Exhausted Resultset
Thanks in advance.
Anil.

This type of exception is usually reported when you forget to call ResultSet.next(). The ResultSet initially points to the on-before-first element. You need to call next() before you use it to make it point to the first element.

Check it out and drop a message about how it went, there are other less common things to check if this solution fails.

Gal

Thanks Gal. But the problem is not that i think. coz. some times it works fine. sometimes ti doesnt. anyway i'm calling the method. but the thing is like :first i queried then by checking the queried resultset i'm constructing another query and then adding it to batch. once i finish the resultset i'm executing the batch. if restart the ServletExec(my servlet engine) it goes fine for some time. again the problem starts.
Thanks again for participating.

Hello friends,
  I am not giving any solution to the your mentioned problem. But I also got same error.

Environment : AIX, EJB 1.0, JDBC 2.0, Oracle Thin Driver, WebSphere Application Server 3.5, JDK 1.2

1 . We have created a DataSource in WAS to connnect to the Database.

If number of records is small (exact limitation can not tell), our application works fine and a loop of resultset.next it retrieves all the records

if there is large data, say 40,000 records, then it after retrieving certain number of records it was
                       throwing "java.sql.SQLException : Exhausted ResultSet"

Actually for a record we were invoking every getXXX method twice. So after surfing on net, I replaced those getXXX calls with one for each column. But this time I got "Reference Counting Error"

Please let me know if anybody has a solution for it

2. Actually were using java.sql.DataSource, etc classes. So I changed the code to use

OracleDataSource, OracleConnection, etc classes. So that I can use OracleConnection.setDefaultRowPrefetch( int )
But while looking up datasource, I am getting ClassCastExcpetion.

cheers
Raju

hello friends,
               I am trying to get the data from a bean which actually gets the data from the database using the resultset..but whenever i am calling the beans getData() method from servlet i am getting exhusted resultset error please help me out..

 THE ERROR::::::::


java.sql.SQLException: Exhausted Resultset
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java)
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java)
        at oracle.jdbc.driver.OracleStatement.prepare_for_new_get(OracleStatemen
t.java)
        at oracle.jdbc.driver.OracleStatement.getStringValue(OracleStatement.jav
a)
        at oracle.jdbc.driver.OracleResultSetImpl.getString(OracleResultSetImpl.
java)
        at oracle.jdbc.driver.OracleResultSet.getString(OracleResultSet.java)
        at ElectionPollLogic.setData(ElectionPollLogic.java:120)
        at SetDataServlet.doPost(SetDataServlet.java:34)
        at SetDataServlet.doGet(SetDataServlet.java:41)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
icationFilterChain.java:247)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
ilterChain.java:193)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperV
alve.java:256)
        at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContex
t.invokeNext(StandardPipeline.java:643)
        at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.jav
a:480)
        at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)

        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextV
alve.java:191)
        at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContex
t.invokeNext(StandardPipeline.java:643)
        at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.jav
a:480)
        at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)

        at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:
2416)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.j
ava:180)
        at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContex
t.invokeNext(StandardPipeline.java:643)
        at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatche
rValve.java:171)
        at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContex
t.invokeNext(StandardPipeline.java:641)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.j
ava:172)
        at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContex
t.invokeNext(StandardPipeline.java:641)
        at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.jav
a:480)
        at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)

        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineVal
ve.java:174)
        at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContex
t.invokeNext(StandardPipeline.java:643)
        at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.jav
a:480)
        at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)

        at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:22
3)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java
:601)
        at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.proce
ssConnection(Http11Protocol.java:392)
        at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java
:565)
        at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadP
ool.java:619)
        at java.lang.Thread.run(Thread.java:484)
java.sql.SQLException: Exhausted Resultset
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java)
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java)
        at oracle.jdbc.driver.OracleStatement.prepare_for_new_get(OracleStatemen
t.java)
        at oracle.jdbc.driver.OracleStatement.getStringValue(OracleStatement.jav
a)
        at oracle.jdbc.driver.OracleResultSetImpl.getString(OracleResultSetImpl.
java)
        at oracle.jdbc.driver.OracleResultSet.getString(OracleResultSet.java)
        at ElectionPollLogic.setData(ElectionPollLogic.java:120)
        at SetDataServlet.doPost(SetDataServlet.java:35)
        at SetDataServlet.doGet(SetDataServlet.java:41)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
icationFilterChain.java:247)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
ilterChain.java:193)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperV
alve.java:256)
        at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContex
t.invokeNext(StandardPipeline.java:643)
        at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.jav
a:480)
        at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)

        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextV
alve.java:191)
        at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContex
t.invokeNext(StandardPipeline.java:643)
        at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.jav
a:480)
        at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)

        at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:
2416)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.j
ava:180)
        at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContex
t.invokeNext(StandardPipeline.java:643)
        at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatche
rValve.java:171)
        at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContex
t.invokeNext(StandardPipeline.java:641)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.j
ava:172)
        at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContex
t.invokeNext(StandardPipeline.java:641)
        at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.jav
a:480)
        at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)

        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineVal
ve.java:174)
        at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContex
t.invokeNext(StandardPipeline.java:643)
        at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.jav
a:480)
        at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)

        at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:22
3)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java
:601)
        at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.proce
ssConnection(Http11Protocol.java:392)
        at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java
:565)
        at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadP
ool.java:619)
        at java.lang.Thread.run(Thread.java:484)

Friend,
       I think ResultSet is empty and you can calling the next().can be one of the reason for getting that errors.
Ameet

Hi friends,

I am trying to get a resultset of 1000 at a time, updating those 1000 records with a new set of 1000 records from a query which consists of 56,000 records. While trying to get only the 1st 1000 records, I took out my while(rsetLexus.next())loop condition and replaced it with rsetLexus.next(); Now I get the Exhausted Result set error. Any tips?

You call ResultSet out of length

It also because calling rs.next after populating cached result set also. It's from my experience

Hi, Anil
My answer may not serve you, since you posted this by sep/01, and probably you might have found the answer. Neverthless I believe my answer might help others facing similar problems.
The data may not be available in the table. Try inserting some data into the table. It worked in my case.

Hi, Anil

Might be one reason is you are opening the resultset in a loop and not closing them correctly.


Satish

We had same problme .. I was looking closely found that two issues.
1.One of the result set is not closed but the prepared stmt is closed in finally block. So in the finally block I closed result set.
Before:
 ps = null;
  try {

      } catch ( ex) {

2. In the same class different method had two updates on same prepared stmt. So first update is executed and not cloed but immidiatly second one is executed and closed. So I am not sure this is big deal or not but i did closed after executing first update

Before:
ps = connection.prepareStatement(QUERY1);
ps = connection.prepareStatement(QUERY2);
ps.close();

After:
ps = connection.prepareStatement(QUERY1);
ps.close();
ps = connection.prepareStatement(QUERY2);
ps.close();

Hi, all in my case the cause of the "Exhausted resultset" exception is in manipulating connections by connection pool (we're using Poolman). The problem occurs when a user timeout (time after which connection is returned to the pool) set for the connection pool is two small, say, smaller then the time needed by a specific query to be processed (e.g. very many rows in a result set). So in certain moment some another thread gets the connection from the pool, but that connection was not processed yet and was returned to pool because of small user timeout parameter's value... and we have the already known exception. In your case the same situation can take place, if you work in multithreaded environment and use some kind of connection pooling (or caching). I hope this note will be helpful. Good luck!

This problem occurs when underlying statement/connection is closed before accessing the result set. The stement must be open while accessing the result set, only after the result set is read, the statement can be closed.