aspose file tools*
The moose likes JDBC and the fly likes No operations allowed after statement closed. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "No operations allowed after statement closed." Watch "No operations allowed after statement closed." New topic
Author

No operations allowed after statement closed.

ying lam
Ranch Hand

Joined: May 17, 2004
Posts: 85
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.




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

Joined: Oct 23, 2005
Posts: 3704
    
    5

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.


My Blog: Down Home Country Coding with Scott Selikoff
ying lam
Ranch Hand

Joined: May 17, 2004
Posts: 85
Thanks for the reply. But I always do this.
So I don't think i can use a statement which is closed.

Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

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
Greenhorn

Joined: Sep 17, 2009
Posts: 6
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?

Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30512
    
150

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Nemo Cavianini
Greenhorn

Joined: Sep 17, 2009
Posts: 6
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30512
    
150

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
Greenhorn

Joined: Sep 17, 2009
Posts: 6
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
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: No operations allowed after statement closed.