wood burning stoves 2.0*
The moose likes JDBC and the fly likes com.ibm.websphere.ce.cm.ObjectClosedException: DSRA9110E: Statement is closed. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "com.ibm.websphere.ce.cm.ObjectClosedException: DSRA9110E: Statement is closed." Watch "com.ibm.websphere.ce.cm.ObjectClosedException: DSRA9110E: Statement is closed." New topic
Author

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

sriharish koduri
Greenhorn

Joined: Jan 18, 2009
Posts: 1
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
Ranch Hand

Joined: Oct 08, 2006
Posts: 2458
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.
 
jQuery in Action, 2nd edition
 
subject: com.ibm.websphere.ce.cm.ObjectClosedException: DSRA9110E: Statement is closed.