asked on

JDBC Error Exhausted Resultset Error (JSP)

(Sorry for crossposting, it seems the original question will not be ansewered in the former group.
https://www.experts-exchange.com/questions/20873091/What-is-the-Exhausted-Resultset-Error-JSP-JDBC-on-Oracle.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);}
%>

mehtas

coz the resultset is closed????

Nick_72

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());}
%>

grim_toaster

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>
<% }

grim_toaster

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).

grim_toaster

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.

ASKER CERTIFIED SOLUTION

grim_toaster

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

egork

ASKER

Thanks for your answers, All!

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