• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

com.ibm.websphere.ce.cm.ObjectClosedException: DSRA9110E: Statement is closed.

 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi

I get the below exception when I perform any kind of database operations.

"com.ibm.websphere.ce.cm.ObjectClosedException: DSRA9110E: Statement is closed."

This is happening very randomly and currently it is happening in our QA env when we are doing some performance tests. We are using Websphere Process Server as the runtime container via which the db operations are perofmed. We have a Utility class for all the DB generic operations. That code goes as below:

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.ResourceBundle;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import com.ibm.ws.rsadapter.jdbc.WSJdbcConnection;
import com.ibm.ws.rsadapter.jdbc.WSJdbcUtil;

/**
* @author Administrator
*
* TODO To change the template for this generated type comment go to
* Window - Preferences - Java - Code Style - Code Templates
*/
public class DBUtil {

private Connection objConn = null;
private static String dataSourceName = getJNDI();
private Connection conn = null;
private CallableStatement stmt = null;
private PreparedStatement pstmt = null;

private static String getJNDI(){
ResourceBundle rbQuery = ResourceBundle.getBundle("MDA_Common");
String dataSourceName = rbQuery.getString("JDBC_JNDI");

return dataSourceName;
}

private Connection getConnection(){

try{
Context ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup(dataSourceName);
conn = ds.getConnection();
}catch(Exception ex)
{
ex.printStackTrace();
}
return conn;
}


public CallableStatement getCallableStatement(String Procedure)
{

try{
String sProcedure = "BEGIN "+Procedure+"; END;";
conn = getConnection();
stmt = conn.prepareCall(sProcedure);

}catch(Exception ex)
{
ex.printStackTrace();
closeCallableStatement();

}
return stmt;
}

public PreparedStatement getPreparedStatement(String Query)
{

try{
conn = getConnection();
pstmt = conn.prepareStatement(Query);

}catch(Exception ex)
{
ex.printStackTrace();
closePreparedStatement();
}
return pstmt;
}

public void closeCallableStatement()
{
try{
this.stmt.close();
stmt = null;
this.conn.close();
conn = null;
}catch(Exception e)
{
e.printStackTrace();
}
}

public void closePreparedStatement()
{
try{
this.pstmt.close();
pstmt = null;
this.conn.close();
conn = null;
}catch(Exception e)
{
e.printStackTrace();
}
}
}

Below is an extract of a business class where we are using the above db util class:

import java.sql.CallableStatement;
import java.sql.Types;
import java.util.ResourceBundle;
import commonj.sdo.DataObject;
public class fetchNotificationDataImpl {
/**
* Default constructor.
*/

DBUtil db = new DBUtil();
public fetchNotificationDataImpl() {
super();
}

public DataObject populateNotificationData(String actionNumber,
String transactionNumber, String stepNumber, String scenarioType)
{

String sProcedure = "";
boolean done = false;

DataObject output=null;
BOFactory boFactory = (BOFactory) new ServiceManager().locateService("com/ibm/websphere/bo/BOFactory");

CallableStatement stmt = null;
try
{

ResourceBundle rbQuery = ResourceBundle.getBundle("Sample_Queries");

sProcedure = rbQuery.getString("fetchNotificationData");
stmt = db.getCallableStatement(sProcedure);
stmt.setString(1, actionNumber);
stmt.setString(2, transactionNumber);
stmt.setString(3, stepNumber);
stmt.setString(4, scenarioType);
stmt.registerOutParameter(5, Types.VARCHAR);

stmt.execute();
LogUtil.logger("----Successfuly executed fetchNotificationData SP---");

output.setString("jobTitle",stmt.getString(5));
}
catch(Exception ex)
{
ex.printStackTrace();
output=null;
}
finally{
db.closeCallableStatement();
try{
stmt.close();
stmt=null;
}catch(Exception e){
e.printStackTrace();
}
}
return output;
}
}

Are we missing anything here in the above usage. We have many classes that use the above DB Utility class for all the generic DB operations.

Any help is very thankful.

Regards,
-Harish


 
Ranch Hand
Posts: 2458
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Two things:

1) You need to acquire and close the connection/statement/resultset in the shortest possible scope (inside same method block) to avoid resource/memory leaks.
2) You need to declare the connection/statement/resultset in the method scope as threadlocal variable and certainly not in class scope as instance variable to avoid thread-unsafety.
 
So you made a portal in time and started grabbing people. This tiny ad thinks that's rude:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic