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

[sriharish koduri]

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

[Bauke Scholtz]

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.