I am trying to download attachment from oracle 10g database when hyperlink is clicked in struts 1.3. which leads to following exception as
java.sql.SQLException: Closed Connection at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:137) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:174) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:239) at oracle.sql.BLOB.getDBAccess(BLOB.java:875) at oracle.sql.BLOB.length(BLOB.java:142) at _jsp._CLA__DownLoadSelected._jspService(_CLA__DownLoadSelected.java:73) at com.orionserver[Oracle Application Server Containers for J2EE 10g (10.1.2.3.0)].http.OrionHttpJspPage.service(OrionHttpJspPage.java:56) at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:356) at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:498) at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:402) at javax.servlet.http.HttpServlet.service(HttpServlet.java:853) at com.evermind[Oracle Application Server Containers for J2EE 10g (10.1.2.3.0)].server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:835) at com.evermind[Oracle Application Server Containers for J2EE 10g (10.1.2.3.0)].server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:341) at com.evermind[Oracle Application Server Containers for J2EE 10g (10.1.2.3.0)].server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:816) at com.evermind[Oracle Application Server Containers for J2EE 10g (10.1.2.3.0)].server.http.AJPRequestHandler.run(AJPRequestHandler.java:231) at com.evermind[Oracle Application Server Containers for J2EE 10g (10.1.2.3.0)].server.http.AJPRequestHandler.run(AJPRequestHandler.java:136) at com.evermind[Oracle Application Server Containers for J2EE 10g (10.1.2.3.0)].util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:186) at java.lang.Thread.run(Thread.java:534)
1. hyper link code look like :
<a href="./jsp/DownLoadSelected.jsp?TrnID=3333"></a>
2. dowloadSelected.jsp looks like:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<%@ page contentType="text/html;charset=windows-1252" %>
<%@ page import = "java.util.*" %>
<%@ page import ="java.sql.*" %>
<%@ page import="java.sql.Blob.*" %>
<%@ page import="org.apache.commons.fileupload.*"%>
<%@ page import ="javax.naming.InitialContext" %>
<%@ page import = "java.io.*"%>
<%@ page import ="java.sql.PreparedStatement.*" %>
<%@ page import="oracle.sql.BLOB"%>
<%@ page import="java.sql.Blob"%>
<%@ page import="org.iibf.claim.dto.*"%>
<html>
<head>
<title>DownLoad File Form</title>
<form name="upload1" method=get ENCTYPE='multipart/form-data'>
<link href="../stylesheet/style.css" rel="stylesheet" type="text/css" />
<script>
alert("Download pdf");
</script>
</head>
<body>
<form name="RT_DownloadAttach">
<%
String PurchaseId=request.getParameter("TrnID");
System.out.println(PurchaseId+ "<- trnid ");
LM_DownLoadAttachDto objRT_DownLoadAttachDTO =org.iibf.claim.util.CLA_Conveyance_recommenHelper.getSelectedDownload(Purchas eId);
//old line
Blob blob= objRT_DownLoadAttachDTO.getblob();
System.out.println("with sql blob : "+blob.length());
long l =blob.length();
oracle.sql.BLOB bbb = objRT_DownLoadAttachDTO.getblob();
l =bbb.length();
byte[] ba =blob.getBytes(1,(int)bbb.length());
//java.io.InputStream fis = rs.getBlob(2).getBinaryStream();
String fileName = objRT_DownLoadAttachDTO.getName();
OutputStream os = response.getOutputStream();
response.setContentType("text/ascii");
response.setHeader("Content-Disposition","attachment;filename=\""+fileName+"\"");
response.setHeader("cache-control", "no-cache");
os.write(ba);
os.close(); %>
<div id="body11">
</body>
<html>
3. helper class method looks like
public static DownLoadAttachDto getSelectedDownload(String ReqID) throws SQLException {
String FunctionName="getSelectedDownload";
boolean update = false;
String strSQL="";
int updatecount=0;
Collection collAllDownLoad = null;
PreparedStatement prepStmt= null;
Connection objConnection = null;
ResultSet rs = null;
Vector vecDownload = new Vector();
LM_DownLoadAttachDto objRT_DownLoadAttachDTO=null;
strSQL = " SELECT UD.* FROM CT_UPLOADATTACHMENT_DTLS UD " +
" WHERE UD.TRN_ID = ? ";
try {
objConnection = CLA_DALHelper.getConnection();
prepStmt = objConnection.prepareStatement(strSQL);
prepStmt.setString(1,ReqID);
rs = prepStmt.executeQuery();
while(rs.next()) {
objRT_DownLoadAttachDTO = new LM_DownLoadAttachDto();
objRT_DownLoadAttachDTO.setName(rs.getString("ATTACH_NAME"));
BLOB b = (oracle.sql.BLOB)rs.getBlob("ATTACH_FILE");
System.out.println("with in recom b helper blob : "+b.length());
Blob bb =rs.getBlob("ATTACH_FILE");
System.out.println("with recom bb blob : "+bb.length());
long l = bb.length(); objRT_DownLoadAttachDTO.setblob((oracle.sql.BLOB)rs.getBlob("ATTACH_FILE"));
}
}
catch(SQLException ex) {
throw new SQLException("Unable to get File " + ex.getMessage() );
}
finally{
if(prepStmt!=null)
prepStmt.close();
if(rs!=null)
rs.close();
objConnection.close();
} return objRT_DownLoadAttachDTO; }
I have Debugged by Code with LIVE database but this Exception cannot be reproduced. This Exception only seen LIVE application of its Deployed war file.
Thanks to @RoySix, i been able solve this problem. like he told in comment i setted Byte of BLOB in DTO object Setter before closing connection. then got that Byte by getter method call on DTO which is obtain from Helper Class.
Following is how JSP look like
<%@ page contentType="text/html;charset=windows-1252" %>
<%@ page import = "java.util.*" %>
<%@ page import ="java.sql.*" %>
<%@ page import="java.sql.Blob.*" %>
<%@ page import="org.apache.commons.fileupload.*"%>
<%@ page import ="javax.naming.InitialContext" %>
<%@ page import = "java.io.*"%>
<%@ page import ="java.sql.PreparedStatement.*" %>
<%@ page import="oracle.sql.BLOB"%>
<%@ page import="java.sql.Blob"%>
<%@ page import="org.iibf.claim.dto.*"%>
<html>
<head>
<title>DownLoad File Form</title>
<form name="upload1" method=get ENCTYPE='multipart/form-data'>
<link href="../stylesheet/style.css" rel="stylesheet" type="text/css" />
<script>
alert("Download pdf");
</script>
</head>
<body>
<form name="RT_DownloadAttach">
<%
String PurchaseId=request.getParameter("TrnID");
System.out.println(PurchaseId+ "<- trnid ");
LM_DownLoadAttachDto objRT_DownLoadAttachDTO =org.iibf.claim.util.CLA_Conveyance_recommenHelper.getSelectedDownload(Purchas eId);
byte[] ba =objRT_DownLoadAttachDTO.getAttachmentByte();
//java.io.InputStream fis = rs.getBlob(2).getBinaryStream();
String fileName = objRT_DownLoadAttachDTO.getName();
OutputStream os = response.getOutputStream();
response.setContentType("text/ascii");
response.setHeader("Content-Disposition","attachment;filename=\""+fileName+"\"");
response.setHeader("cache-control", "no-cache");
os.write(ba);
os.close(); %>
<div id="body11">
</body>
<html>
helper class method looks like
......................
while(rs.next()) {
objRT_DownLoadAttachDTO = new LM_DownLoadAttachDto();
objRT_DownLoadAttachDTO.setName(rs.getString("ATTACH_NAME"));
BLOB b = (oracle.sql.BLOB)rs.getBlob("ATTACH_FILE");
Blob bb =rs.getBlob("ATTACH_FILE");
objRT_DownLoadAttachDTO.setblob((oracle.sql.BLOB) rs.getBlob("ATTACH_FILE"));
oracle.sql.BLOB bbb = (oracle.sql.BLOB)rs.getBlob("ATTACH_FILE");
l =bbb.length();
byte[] ba =blob.getBytes(1,(int)bbb.length());
}
.................