I'm forwarding this on to the list as it was very helpful with regard to my question, just incase anyone else has the same questions. Thanks, Avi.
Jeff. Begin forwarded message: Date: Tue, 29 Jan 2002 08:50:25 +0200 From: Avi Abrami <[EMAIL PROTECTED]> To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> Subject: What is a Dirty Connection (using -Djdbc.debug=true) Hi Jeff, I'm not subscribed to the Orion-Interest mailing list, but I monitor it through the OrionServer website. I hope you don't mind that I am answering you via a direct eMail. This is what I know about "dirty connections". (Note that you may already know some of what I am about to tell you, I hope that's OK with you.) Orion "wraps" the JDBC driver classes. It seems to me, from debug messages displayed by the server, (but I haven't verified this yet) that Orion uses different wrapper classes depending on whether the server is running in debug mode or regular mode. I have had my application crash when running in debug mode, but work properly when working in regular (as in non-debug) mode. As far as I can ascertain, a "dirty connection" is one that has an associated "open" Statement or ResultSet or PreparedStatement, etc. object. I have found that the only way to properly "close" a Connection is to first close the ResultSet object, then the Statement object and finally the Connection object. Note that I am only working with the Oracle JDBC driver that comes with OC4J (and my version is 1.0.2.2 -- on SUN Solaris 2.7) Further, the following methods from the JDBC API do not work correctly (when using Orion's JDBC wrapper classes): ResultSet.getStatement() Statement.getConnection() These methods execute normally -- they don't throw exceptions at runtime and they compile without an problems. However, it seems like they do nothing! Basically a "dirty connection" means you have database resources (like Statement objects or ResultSet objects) still lying around -- which need to be disposed of (or "closed"). This is sort of like a memory leak, because eventually the Oracle database will exhaust all its resources and throw SQLExceptions in your application. The only way I found to properly "close" these database resource associated objects (Connection, Statement, ResultSet, etc) was to hold on to the references after I created the objects and only use those references to "close" those objects. So if, for example, you open a Connection in one method and that method returns a ResultSet to a calling method, the calling method will not be able to close the Connection unless you also pass a reference to the Connection to the calling method. Also note, that the Oracle JDBC driver also does not correctly implement the Connection.close() method. According to the API documentation, closing a Connection is supposed to also close any associated Statements, ResultSets, etc. However, the Oracle JDBC driver does not. You need to explicitly close the ResultSet, then the Statement, then the Connection. So in summary, I will answer your questions individually now with a short answer, below: Q:What is a Dirty Connection (when using -Djdbc.debug=true)? A:A connection with unclosed resources lying around. Q:What causes it? A:Not explicitly closing the resources. Q:What repercussions does it have? A:It can crash the database server. Q:Is a Dirty Connection a bad thing? A:Yes. Q:What can we do to avoid it (if anything)? A:Explicitly close all resources -- ResultSets, Statements, etc. Q:Are there any settings that control when and if they are cleaned up? A:If you are using pooled connections, perhaps the "inactivity-timeout" attribute in the "data-sources.xml" file can help -- I don't know for sure. Q:Are they cleaned up at all? A:As far as I can tell, no. A very long post -- I hope you don't mind, and I hope it can help you. Good Luck, Avi. -- Jeff Hubbach Internet Developer Sun Certified Web Component Developer New Media Division ITQ Lata, L.L.C. 303-745-4763 x3114
