We help IT Professionals succeed at work.

JDBC Error Exhausted Resultset Error (JSP)

egork
egork asked
on
3,945 Views
Last Modified: 2010-08-05
(Sorry for crossposting, it seems the original question will not be ansewered in the former group.
https://www.experts-exchange.com/Databases/Oracle/Q_20873091.html)

Hi, I have a JSP that is supposed to read a certain table and present it to the user. It basically copies a table one to one, only there is an additional row with the date, wich I extract from first record in the result set, and this is why I have two HTML blocks there.

the JSP works, but if left alone for a couple of days, next time I call it there comes an error:

exception
org.apache.jasper.JasperException: Exhausted Resultset

What is wrong here and why does it work right after the resart? Could it be that some times a connection to the database expires and I have to put some code, to reopen it?

Thanks,
Egor

HTTP Status 500 -

type Exception report

message

description The server encountered an internal error () that prevented it from fulfilling this request.

org.apache.jasper.JasperException: Exhausted Resultset
     at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:254)
     at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:295)
     at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
     at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
     at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
     at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
     at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256)
     at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
     at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
     at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
     at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)

<%@ page import="javax.servlet.*" %>
<%@ page import="javax.servlet.http.*" %>
<%@ page import="java.sql.*" %>
<%@ page import="java.util.*" %>
<%@ page import="java.io.*" %>

<%-- these two lines do the trick with the excel --%>
<%@ page contentType = "application/vnd.ms-excel" %>
<% response.setContentType("application/vnd.ms-excel"); %>

<%!private String replace (String source, String part, String replacement)
    //replaces all occurances of part inside source
    {
    if (source !=null && source.indexOf(part)!=-1)
        {
        int pos = source.indexOf(part);
        source = source.substring(0, pos) + replacement + source.substring(pos+1, source.le
ngth());
        //System.out.println(pos+" - "+source);
        if (source.indexOf(part) !=-1) source = replace(source, part, replacement);
        }
    return source;
    }//replace
%>

<%
String JDBCurlLong = "jdbc:oracle:thin:_connectionString_";
ResultSet rs = null;
String SQL = "Select * From pricepool p where p.loaded=-1 order by p.NAME";
Connection conne = null;
Statement stmt = null;
try {
 DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
 conne = DriverManager.getConnection(JDBCurlLong);
 stmt = conne.createStatement();
 rs = stmt.executeQuery(SQL);
} catch (Exception e) {System.out.println(e);}
%>

<table border="1">
<%  if (rs.next()) %>
<tr><td colspan=5>Angebote Retention ab <%=rs.getString("validFrom")%></td></tr>
<tr><td></td></tr>
<tr><th>Artikelnummer</th><th>Modell</th><th>Farbe</th><th>Kategorie A-C</th><th>Kategorie
D</th><th>Kategorie E </th><th>Aenderungen</th></tr>
<tr><td><%=rs.getString("code")%></td>
     <td><%=rs.getString("Name")%></td>
     <td><%=rs.getString("color")%></td>
     <td><%=rs.getString("abcprice").replace('.',',')%>EUR</td>
     <td><%=rs.getString("dprice").replace('.',',')%>EUR</td>
     <td><%=rs.getString("eprice").replace('.',',')%>EUR</td>
     <td><%=rs.getString("description")%></td>
</tr>

<% ;
while (rs.next()) {
%>
 <tr><td><%=rs.getString("code")%></td>
     <td><%=rs.getString("Name")%></td>
     <td><%=rs.getString("color")%></td>
     <td><%=rs.getString("abcprice").replace('.',',')%>EUR</td>
     <td><%=rs.getString("dprice").replace('.',',')%>EUR</td>
     <td><%=rs.getString("eprice").replace('.',',')%>EUR</td>
     <td><%=rs.getString("description")%></td>
 </tr>
<%
 }
%>
</table>
<%
try {
rs.close();
conne.close();
} catch (Exception e) {System.out.println(e);}
%>
Comment
Watch Question

Commented:
coz the resultset is closed????

Commented:
I think you should close the statement object too, maybe that causes the problem:

<%
try {
  if (rs != null)
      rs.close();
  if (stmt != null)
      stmt.close();
  if (conne != null)
      conne.close();
} catch (SQLException sqle) {System.out.println(sqle.toString());}
%>
You have a problem with this code:
<%  if (rs.next()) %>
<tr><td colspan=5>Angebote Retention ab <%=rs.getString("validFrom")%></td></tr>
<tr><td></td></tr>
<tr><th>Artikelnummer</th><th>Modell</th><th>Farbe</th><th>Kategorie A-C</th><th>Kategorie
D</th><th>Kategorie E </th><th>Aenderungen</th></tr>
<tr><td><%=rs.getString("code")%></td>
     <td><%=rs.getString("Name")%></td>
     <td><%=rs.getString("color")%></td>
     <td><%=rs.getString("abcprice").replace('.',',')%>EUR</td>
     <td><%=rs.getString("dprice").replace('.',',')%>EUR</td>
     <td><%=rs.getString("eprice").replace('.',',')%>EUR</td>
     <td><%=rs.getString("description")%></td>
</tr>
<% ;

if the resultset.next() call returns false, you will still execute most of the code (the generated servlet generally puts each line output into its own out.print(..), change to (note the included braces):

<%  if (rs.next()) { %>
<tr><td colspan=5>Angebote Retention ab <%=rs.getString("validFrom")%></td></tr>
<tr><td></td></tr>
<tr><th>Artikelnummer</th><th>Modell</th><th>Farbe</th><th>Kategorie A-C</th><th>Kategorie
D</th><th>Kategorie E </th><th>Aenderungen</th></tr>
<tr><td><%=rs.getString("code")%></td>
     <td><%=rs.getString("Name")%></td>
     <td><%=rs.getString("color")%></td>
     <td><%=rs.getString("abcprice").replace('.',',')%>EUR</td>
     <td><%=rs.getString("dprice").replace('.',',')%>EUR</td>
     <td><%=rs.getString("eprice").replace('.',',')%>EUR</td>
     <td><%=rs.getString("description")%></td>
</tr>
<% }

Also, the following block of code could cause you problems:

try {
 DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
 conne = DriverManager.getConnection(JDBCurlLong);
 stmt = conne.createStatement();
 rs = stmt.executeQuery(SQL);
} catch (Exception e) {System.out.println(e);}

If any exception is thrown whilst acquiring your connection, creating the statement, etc, then you will log it, but then allow the page to continue processing as normal (which could allow your result set to be null), although I doubt this is your problem here (you would have got a NullPointerException), it could cause you problems in the future (for example maximum number of connections to db, etc.).

Also, registering of the driver is an expensive operation, and should only be done once, and all of your database connections, etc, should ideally be handled by a helper class of some sort, without hardcoding your database connection into the JSP (as it is possible that a JSP can be accessed directly (effectively viewing the source), and other people could get all that they need to access your database (i.e. usernames/passwords).
Just expanding upon my comment:
--> (for example maximum number of connections to db, etc.).
(for example maximum number of connections to db, etc. would throw an exception whilst opening the connection, would be handled by your exception handler (System.out.println(..)) and then you would continue processing the rest of the JSP with a null ResultSet.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks for your answers, All!

 I'll evaluate all what I have learned and close the question soon.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.