java.sql.SQLException: Exhausted Resultset after retrieving results

Question

I was trying to get the search results, using the following method,it leaves the error like java.sql.SQLException: Exhausted Resultset in the catch block.This is showing after retrieving results. The search is seems to be very slow as well.

private static OracleConnection connection = null;
    private static OraclePreparedStatement ptmst = null;
    private static OracleResultSet rs = null;
    private static OracleCallableStatement cstmt = null;


 public static ArrayList particleSearch(BigDecimal myId,BigDecimal deId,String search_term,JspWriter out )throws Exception
  {
    StringBuffer sql = new StringBuffer();
    ArrayList particles = new ArrayList();
    try
    {

      connection = (OracleConnection) TransactionScope.getConnection();
      sql.append("select distinct(..)......... ");  
      ptmst = (OraclePreparedStatement)connection.prepareStatement(sql.toString());
      ptmst.setBigDecimal(1,deId);
      ptmst.setBigDecimal(2,myId);
      ptmst.setString(3,search_term);
      rs = (OracleResultSet)ptmst.executeQuery();               

      while (rs.next()) 
                {                  
                HashMap hashParticles = new HashMap();
                hashParticles.put("part1",rs.getString(1));
                hashParticles.put("part2",rs.getString(2)); 
                hashParticles.put("part3",rs.getBigDecimal(3));                                                 
                particles.add(hashParticles);
                }

       connection.commit();


     }
     catch (Exception e) {
        out.println("Er:"+e.toString());  ///// throws error:: Er:java.sql.SQLException: Exhausted Resultset 
        }
     finally {

            try {
                if (ptmst != null) {
                    ptmst.close();
                }
            } catch (Exception e) {
            out.println(e.toString());
            }

            try {
                if (connection != null) {
                    TransactionScope.releaseConnection(connection);
                }
            } catch (Exception e) {
            out.println(e.toString());
            }

        }
        return particles; 
  }

By looking on the internet, this error seems to be thrown when you get data from the `ResultSet` and there's no data to read. It would be better to check if your query really returns "part1", "part2" and "part3" (as you try to get). Also, make sure to **close the ResultSet** as well as the other JDBC resources (`PreparedStatement`, `Connection`) — Luiggi Mendoza, Feb 19 '13 at 05:18
Have a look at this - http://stackoverflow.com/a/1870272/2030471 — , Feb 19 '13 at 05:19
I highly suspect that this is because you've made `rs` a static field in the class rather than a variable local to your method and you're calling this static method from more than one thread. None of those fields should be `static`, for that matter, nor should your method. — Brian Roach, Feb 19 '13 at 05:32
Make the OracleResultSet object local to method and close it in finally block. — happy, Feb 19 '13 at 05:35

java.sql.SQLException: Exhausted Resultset after retrieving results

0 Answers0