• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

JDBC Statement Documentation Ambiguity

 
Greenhorn
Posts: 14
C++ Notepad Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 14
C++ Notepad Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 14
C++ Notepad Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Alright, thanks again for the help, Martin. I like your idea of defensive statement re-opening.
 
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
What are you doing? You are supposed to be reading this tiny ad!
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic