GeeCON Prague 2014*
The moose likes JDBC and the fly likes JDBC Statement Documentation Ambiguity Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "JDBC Statement Documentation Ambiguity" Watch "JDBC Statement Documentation Ambiguity" New topic
Author

JDBC Statement Documentation Ambiguity

Luc Lieber
Greenhorn

Joined: May 17, 2010
Posts: 14

http://docs.oracle.com/javase/6/docs/api/java/sql/Statement.html#isClosed%28%29

A Statement is closed if the method close has been called on it, or if it is automatically closed.


This has to be one of the most ambiguous documentation cases that I have ever seen. What on earth do the authors mean by 'automatically closed'? Perhaps through the AutoCloseable interface? Implicitly when a fatal error occurs? Is this something that I should check before using a Statement across method calls due to a possible "fatal error"?

Can someone shed some light? Thanks
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

I'd say you should simply close every statement when you're done with it, regardless what the docs say in this case.

The automatic close might refer to the statement being closed when the underlying DB connection was closed. It certainly doesn't refer to the AutoCloseable interface, as that is a Java 7 thing. It probably doesn't refer to the Object's finalize method either, as that happens when the object is out of scope and you generally cannot call its methods.

I'm not sure a statement might get closed due to "fatal error". The production logs of our application show lots of various database/network failures, but no traces of statements being unexpectedly closed. So I wouldn't be very concerned about this automatic close. Even if it did happen after an error, you'll still get an appropriate exception, and calling close on already closed statement is a no-op.
Luc Lieber
Greenhorn

Joined: May 17, 2010
Posts: 14

Martin Vajsar wrote:I'd say you should simply close every statement when you're done with it, regardless what the docs say in this case.


The (prepared) statement that I'm using is live during the entire lifespan of my application. Closing it isn't a problem (or preferable until the very end of the application scope). Finding it suddenly not working because of something like the database software being restarted is a problem. I was hoping for a comprehensive list of "fatal error conditions" that will cause a statement to abruptly stop working.

* More info: It is important that I can distinguish between a 'temporary condition' like a network error and a 'permanent' condition' like a malformed query. Transactions need to be re-queued if the database simply isn't reachable, but aborted if there's something wrong with the queries themselves.

Thanks for the reply.

**
What I really want is a link to some authoritative documentation that makes guarantees about what causes 'automatic closing'. Perhaps it's driver specific, but I haven't even found any driver documentation that gives the required information.

***
I suppose that I could just use the exception's error code to distinguish between cases in the worst possible scenario, but the exception codes are vendor specific and thus not portable.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Luc Lieber wrote:What I really want is a link to some authoritative documentation that makes guarantees about what causes 'automatic closing'. Perhaps it's driver specific, but I haven't even found any driver documentation that gives the required information.

Yeah, now I understand your problem. Unfortunately, I don't know of any such authoritative source. I know some authorities (eg. Tom Kyte of Oracle) mentioned that applications can keep prepared statements open for an indeterminate amount of time, though that probably wasn't specifically about Java.

As you say, this could be JDBC driver specific. Perhaps your best option would be to read the documentation for your driver, even if you don't find a definitive answer, you might find some important hints. For example, Oracle's JDBC driver caches prepared statements and reuses them. This means that closing and opening a prepared statement has probably just a small overhead and, if I wanted to be on the safe side, I'd close and re-open the statements for each transaction.

In any case, a good defensive strategy might be to close and reopen everything after any SQLException is encountered. Errors should be uncommon, so the additional overhead should be small.

* More info: It is important that I can distinguish between a 'temporary condition' like a network error and a 'permanent' condition' like a malformed query. Transactions need to be re-queued if the database simply isn't reachable, but aborted if there's something wrong with the queries themselves.

In my opinion, this is unreasonably difficult even for database-specific code. I don't know other databases in detail, but in Oracle there are thousands of error codes. This might be entirely unfeasible in a database-agnostic way.

Perhaps you might retry transactions a few times and then put them into a list of failed jobs and have some human review them and decide which ones to run again and which ones to ditch. There shouldn't be many malformed queries or permanent errors, if you tested the application properly. And even if there are (hopefully just a few) transactions failed due to bugs, they'll be retried, but they'll fail again.
Luc Lieber
Greenhorn

Joined: May 17, 2010
Posts: 14

Alright, thanks again for the help, Martin. I like your idea of defensive statement re-opening.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18570
    
    8

Luc Lieber wrote:I was hoping for a comprehensive list of "fatal error conditions" that will cause a statement to abruptly stop working.


Well, it isn't possible to enumerate all the possible things which might go wrong, especially in a network environment. That phrase is more or less a catch-all which acknowledges that things might go wrong, that's all.
 
GeeCON Prague 2014
 
subject: JDBC Statement Documentation Ambiguity