File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes java.sql.SQLException: Closed Statement   Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "java.sql.SQLException: Closed Statement   " Watch "java.sql.SQLException: Closed Statement   " New topic
Author

java.sql.SQLException: Closed Statement

BalaiahRaju ChamarthiRaju
Greenhorn

Joined: Sep 18, 2007
Posts: 13
I am getting this error when my java code trying to acess DB (Oracle 9i) thr' JDBC driver. It happens only in the production environment that to occationally. We can not duplicate the problem on our test servers, which has exact same configuration. Here is the trace.

java.sql.SQLException: Closed Statement
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:158)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:305)
at oracle.jdbc.driver.OracleStatement.ensureOpen(OracleStatement.java:3945)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3000)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3059)


I thought and searched for all my connections is there any closing statements before calling executeQuery() method,

Please suggest me is there any other place get chance to close connections

Thanks in advance
Bala
Herman Schelti
Ranch Hand

Joined: Jul 17, 2006
Posts: 387
h balaiahraju,

maybe you have a connection that executes 2 queries, and somehow closes the connction after the first.

Your stacktrace is probably longer: all the way down to 1 of your classes?
Maybe you can post that code?

Herman
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18987
    
    8

Errors that occur rarely and unpredictably on servers are often problems with thread safety. Is it possible that two threads are using the same connection?
Pavel Cherkashin
Ranch Hand

Joined: Mar 04, 2005
Posts: 47
Could you be so kind to describe in more details these things:
1. configuration of data source
2. usage of transaction managers
3. what container
4. the passage of code where your application tries to execute statement.

It can be concurrence problem as well as transaction management problem as well as database configuration problem.

The statement preparation initiates the application, but the database server does a piece of work - analyzes it and stores while the particular connection
exists. When the connection is about to close - all prepared statements close immediately for it. While the time life of opened connection there is a possibility to use once prepared statement many times with changed parameter values.


Pavel Cherkashin - <br />SCJP, SCWCD, SCDJWS, SCBCD, SCEA, ...<br />www.linkedin.com/in/pcherkas
Roger Chung-Wee
Ranch Hand

Joined: Sep 29, 2002
Posts: 1683
I think the most likely explanation is that your JDBC objects are referenced by instance or, even worse, class variables. So, when one thread closes a Connection or Statement, then another thread is unable to use the Statement.

It is best to reference JDBC objects by local variables. You should be creating the objects as late as possible and close them soon after usage. If this causes a problem due to the frequent opening of Connections, then you should use Connection pooling.


SCJP 1.4, SCWCD 1.3, SCBCD 1.3
Keith Bloomfield
Greenhorn

Joined: Aug 27, 2009
Posts: 1
As Roger said.

You can only execute one statement at a time. A quick fix for this problem would be to create a new Statement object, or re-point the existing object using stmt = conn.createStatement();
Tanzy Akhtar
Ranch Hand

Joined: Jul 19, 2009
Posts: 110
welcome to javaranch Keith.


Roll with punchers, there is always tomorrow.
Techie Blog -- http://jtanzy.blogspot.com/
G Kaviyarasu
Greenhorn

Joined: Mar 02, 2010
Posts: 1
We also had faced this problem when tried to connect and execute the statements using remote JDBC Connection. We changed it to RPC call and then have a local JDBC call to resolve this problem. Our application has regional servers located in each region talking to its own DB. Not sure if your configuration is also something similar in nature.
Randy Harbison
Greenhorn

Joined: May 10, 2011
Posts: 1
You may want to verify that the class that opens the connection does not have the connection variable as static.
A static variable is declared outside the scope of the instance and threads would have problems with this.

Lawrence Ee
Greenhorn

Joined: Jun 08, 2011
Posts: 3

I'm having a similar problem with my Java code trying to access an Oracle 10g database through the JDBC driver.

We generate a number of different reports, but one report consistently fails with this error.

It only happens in the production environment, which has significantly more data than the test and development environments.

One interesting (!) observation is that the error occurs after 10 minutes to the second (almost). Which leads me to think it is a timeout related problem.

And finally, the code is being run within a job sheduled using the Quartz Scheduler v1.6.5.

Any help would be appreciated.

Many Thanks

Lawrence



Here is the stack trace:



Here is the code:


Lawrence Ee
Greenhorn

Joined: Jun 08, 2011
Posts: 3

I have made some progress in understanding the problem...

Basically, I have been looking at the code from a threads perspective. There are only three objects used, log4j, oracleXML Publisher and hibernate. Discounting log4j, the Oracle object is created, opened and closed within the procedure, so I concluded that hibernate was the most likely cause of the problem (i know, theres an element of educated guesswork in this).

Anyway, I found the hibernate timeouts (all around 300 seconds) and increased them to 3000 seconds... and lo and behold the reports are generated successfully, although some of them take over 20 minutes each.

So, this is a performance problem with the SQL itself:

SELECT service
, reply
, ROUND(SUM(DECODE(op, 3, 0, count))* :uplift,0) x_events
, ROUND(SUM(DECODE(op, 3, count, 0))* :uplift,0) y_events
FROM aggregate
WHERE ref_time >= :period_start
AND ref_time < :period_end
AND feed = 13
AND agreement = :agreement
AND line = 1
GROUP BY service, reply
ORDER BY 1,2


The SQL in question is querying data for a specific month from a monthly partitioned table that contains about 11 million records a month. We currently have 99 partitions.

Executing the sql in Toad / Sql developer / sqlplus takes around 20 seconds...so why 20 minutes?

Looking in the session browser, I can see that the 20 minute query is looking through all 99 partitions where the 20 second query is only looking at 1 partition for the month in question.

I am definitely using the same query in my tests as I am cutting and pasting from the current active query.

I'm not sure if this is heading off topic for the forum thread, but the question now is...

Why is Oracle using a clearly innefficient execution plan when we are using a query with bound variables and how do we get it to use a more efficient execution plan?




Fatih Keles
Ranch Hand

Joined: Sep 01, 2005
Posts: 182
What happens if you convert date columns to char and back to date?

Lawrence Ee
Greenhorn

Joined: Jun 08, 2011
Posts: 3

Fatih Keles wrote:What happens if you convert date columns to char and back to date?



No, unfortunately the above suggestion didn't make any difference.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: java.sql.SQLException: Closed Statement