JasperException - I got this error

[ks kumar]

Hi,
I am using JDTS JDBC driver to establish database connection.
The connection establishment and fetching the data was sucessfull from database.
But, some times i got the below error :

HTTP Status 500 -
 
type Exception report
 
message
 
description The server encountered an internal error () that prevented it from fulfilling this request.
 
exception
 
org.apache.jasper.JasperException: Invalid state, the Connection object is closed.
org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:476)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:371)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:315)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
 
root cause
 
javax.servlet.ServletException: Invalid state, the Connection object is closed.
org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:846)
org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:779)
org.apache.jsp.topic_jsp._jspService(topic_jsp.java:718)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:98)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:328)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:315)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
 
root cause
 
java.sql.SQLException: Invalid state, the Connection object is closed.
net.sourceforge.jtds.jdbc.TdsCore.checkOpen(TdsCore.java:451)
net.sourceforge.jtds.jdbc.TdsCore.getNextRow(TdsCore.java:763)
net.sourceforge.jtds.jdbc.JtdsResultSet.next(JtdsResultSet.java:593)
com.forum.database.QueryStatement.selectAllTopic(QueryStatement.java:437)
org.apache.jsp.topic_jsp._jspService(topic_jsp.java:111)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:98)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:328)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:315)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
 
note The full stack trace of the root cause is available in the Apache Tomcat/5.5.23 logs.
Apache Tomcat/5.5.23

i can't clear this error when i try to fix this problem.
please help me, if any one know the answer, let me know.

thanks and regards,
K.S.Kumar

[Remko Strating]

It sounds like that the database connection is closed and you're trying to use this connection. You can check in your code if a connection is closed with the statement isClosed().


java.sql.Connection


The connection could be closed for a lot of reasons.

[ks kumar]

Thanks for your reply.

i have used a separate class file for database connection establishment.
here is the connectionpool file.

package com.forum.database;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
 

 public class ConnectionPool extends javax.servlet.http.HttpServlet implements javax.servlet.Servlet {
    

 
public ConnectionPool() {
super();
} 

public static Connection getConnection()
{
try
{
Class.forName("net.sourceforge.jtds.jdbc.Driver");
ConnectionPool.con = DriverManager.getConnection("jdbc:jtds:sqlserver://localhost:1433/mb", "sa", "sa");

if (ConnectionPool.con != null) {
System.out.println("Connection Pool Database Connection Success");
}
}catch(final ClassNotFoundException cfe)
{
cfe.printStackTrace();
}catch(final SQLException se)
{
se.printStackTrace();
System.gc();
}

return ConnectionPool.con;
}

private static Connection con=null;
}

here is the query execute method

ackage com.forum.database;
 
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
 
public class SQLConnectionManager extends javax.servlet.http.HttpServlet
implements javax.servlet.Servlet {
 
public Connection con =null;

private Statement st = null;
private CallableStatement cst = null;
private ResultSet rs = null;
 
public SQLConnectionManager() {
super();
try
{
this.con =ConnectionPool.getConnection();
}catch(final Exception e)
{
System.gc();
this.con=null;
}

}

public ResultSet getResultSet(final String strQuery) throws Exception
{
try
{
this.st=this.con.createStatement();
this.rs=this.st.executeQuery(strQuery);
}catch(final Exception e)
{
System.out.println("Query Executing Exception : " + e);
}
return this.rs;
}

public int getResultSetInsertUpdateDelete(final String strQuery) throws Exception
{
int i=0;
try
{
this.st=this.con.createStatement();
i=this.st.executeUpdate(strQuery);
}catch(final Exception e)
{
System.out.println("Update Query Executing Exception : " + e);
}
return i;
}

public ResultSet getResultSetPrepareCall(final String strQuery) throws Exception
{
try
{
this.cst=this.con.prepareCall(strQuery);
this.rs=this.cst.executeQuery();
}catch(final Exception e)
{
System.out.println("SP Query Executing Exception : " + e);
}
return this.rs;
}

public void setStateClose() throws Exception
{
if (this.con!=null)
{
this.con.close();
}
if (this.st!=null)
{
this.st.close();
}
if (this.cst!=null)
{
this.cst.close();
}
if (this.rs!=null)
{
this.rs.close();
}

}
}
 

here is the query calling method

package com.forum.database;
 
import java.io.File;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
 
public class QueryStatement {

static SQLConnectionManager sqlConnManager = new SQLConnectionManager();
sendMail _sendMail=new sendMail();
 
private static ResultSet rs = null, rs1=null, rs2=null;
private static CallableStatement cst=null;
private static Vector vector=null;
private static PreparedStatement pt=null;
private static File file=null;
 
public QueryStatement() {

}
        
        public static Vector selectTopic(final int catID, final int forumID, final int strPage) throws Exception {
QueryStatement.vector=new Vector();
int pageTop=20*strPage;
final String str=Messages.getString("QueryStatement.43")+" where ForumID="+forumID; 
final String str1="select * from ( select TOP 20 * from ( select TOP "+pageTop+" * from Thread where ForumID="+forumID+" and CatID="+catID+" order by ThreadID DESC ) as foo order by ThreadID ASC ) as bar ORDER BY ThreadID DESC";
QueryStatement.rs1=QueryStatement.sqlConnManager.getResultSet(str);
if(QueryStatement.rs1.next())
{
final int countValue=QueryStatement.rs1.getInt(1);
if (countValue!=0)
{
QueryStatement.rs = QueryStatement.sqlConnManager.getResultSet(str1);
QueryStatement.vector.addElement(QueryStatement.rs);
}else if (countValue==0)
{
QueryStatement.vector.addElement(null);
}
}
return QueryStatement.vector;
}
}

i got this error

java.sql.SQLException: Invalid state, the Connection object is closed.

please tell me what i did error coding in the above coding.

please help if any one know the answer.


Thanks and regads,
K.S.Kumar