No operations allowed after statement closed.

[ying lam]

Hi,

I am using C3p0 as my JDBC connection pool with MySql.

I get the following exception after I don't hit the server for a while. While other times, it works.

Can you please tell me what may be the root cause of the problem and how can I fix it?

Thank you.

String getProc = "{ call " + Constants.DB_NAME + ".FontMetricsGet(?,?) }";
Connection     conn = this.getConnection();
CallableStatement          cstmt = conn.prepareCall(getProc);
 

  
 
2008-08-20 09:02:44,623 [Thread-21] DEBUG com.mchange.v2.c3p0.impl.NewPooledConnection - 
com.mchange.v2.c3p0.impl.NewPooledConnection@8b3bb3 handling a throwable.
com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: No operations allowed after statement closed.
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:980)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
        at com.mysql.jdbc.StatementImpl.checkClosed(StatementImpl.java:380)
        at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1250)
        at com.mysql.jdbc.DatabaseMetaData.getCallStmtParameterTypes(DatabaseMetaData.java:1581)
        at com.mysql.jdbc.DatabaseMetaData.getProcedureOrFunctionColumns(DatabaseMetaData.java:4120)
        at com.mysql.jdbc.DatabaseMetaData.getProcedureColumns(DatabaseMetaData.java:4057)
        at com.mysql.jdbc.CallableStatement.determineParameterTypes(CallableStatement.java:809)
        at com.mysql.jdbc.CallableStatement.<init>(CallableStatement.java:609)
        at com.mysql.jdbc.CallableStatement.getInstance(CallableStatement.java:502)
        at com.mysql.jdbc.ConnectionImpl.parseCallableStatement(ConnectionImpl.java:3881)
        at com.mysql.jdbc.ConnectionImpl.prepareCall(ConnectionImpl.java:3965)
        at com.mysql.jdbc.ConnectionImpl.prepareCall(ConnectionImpl.java:3939)
        at com.mchange.v2.c3p0.impl.NewProxyConnection.prepareCall(NewProxyConnection.java:643)
        at com.mycompany.myapp.FontDB.getMetrics(Unknown Source)
 
  

[ August 20, 2008: Message edited by: Scott Selikoff ]

[Scott Selikoff]

It's pretty self-explanatory; somewhere you close a Statement then try to use it again. Check the method "com.mycompany.myapp.FontDB.getMetrics" and see where you might be using a statement that you previously closed.

[ying lam]

Thanks for the reply. But I always do this.
So I don't think i can use a statement which is closed.

try{
Connection conn = this.getConnection();
CallableStatement cstmt = conn.prepareCall(insertProc);
 
cstmt.setString(1, "somestring");
cstmt.setBytes(2, data);       
                       
cstmt.execute();
} finally {
 try {
   cstmt.close();
   conn.close();
 } catch (Exception e) {
 System.err.println("Error message: " + e.getMessage());
 }
}
 

[Scott Selikoff]

Is the exception thrown in the main try block or in the finally? It's possible an error closes the exception early, and the finally is attempting to close an all ready closed exception.

[Nemo Cavianini]

Hi,

it seems like I am experiencing a similar problem. Mine however works when there is only one thread accessing the function. However, when multiple threads hit it at the same time, I constantly start receiving the error. I wonder if you could fix the problem?

My function is defined as synchronized, so it is very unlikely that threads interfere to one anothers job. any idea?

  private synchronized Event getEvent(int id, long timestamp) throws Exception {
		
		if (connection != null) connection.close();
		connection = ConnectionHandler.getInstance().getDBConnection();
		
		Event event = new Event();
		
		PreparedStatement stmt = connection.prepareStatement(stmtManager.getEventStoreStmt(EventStoreIF.GET_EVENT));
		stmt.setString(1, String.valueOf(id));
				
		ResultSet rs = stmt.executeQuery();
		while(rs.next()){
			event.addAttribute(rs.getString("name"), rs.getString("value"));
		}
		stmt.close();
		return event;
	}  

[Jeanne Boyarsky]

Nemo,
Welcome to JavaRanch!

I recommend not having connection as an instance variable. You could have a local/method variable that gets the connection at the beginning of the method and closes it at the end in a finally block.

It's logically close to what you are doing anyway, since you close the connection each time. Except yours wastes resources since the connection is kept open between queries.

[Nemo Cavianini]

Thanks a lot Jeanne for the reply and thanks for welcoming me

I wonder if it is a good practice to keep on opening and closing the connection as it is often the case the opening connection to the DB imposes extra overhead to the system as it requires a TCP handshake with the mysql server. In our case performance is an issue, so my preference would be to not even bother with opening and closing the connection to the DB, but to simply leave it open.

However, in that case, I face more disruptions when polling from the DB. Do you, or anybody in the forum, know of best practices for allowing access to the DB in multi-threaded environments? It would be a great help if someone could kindly refer me to such a resource.

cheers,
Nemo

[Jeanne Boyarsky]

Nemo,
If you use a connection pool like dbcp, the overhead is minimal since the connection isn't closed - merely returned to the pool. Even if you only have one connection in your pool, I recommend this approach for a multi-threaded application. Especially one that has a lot of connections opening and "closing."

Your performance is likely to be better with a pool than your current approach too.

[Nemo Cavianini]

Jeanne,

I am using DBCP already, but my environment is a bit tricky as I am developing everything on top of OSGi.

One problem that I am facing right now is that the connections in the pool for DBCP get timed out after a while of sitting idle. I am using a JDBC implementation of DBCP, I wonder if there is any way to validate the connections in a pool to somehow abandon the obsolete ones?

thanks,
Nemo