The pool manager won't close a connection being used. I use oracle jdbc and I have found if I do the following I do not get any problems
During the doGet or doPost { try { Get a connection from the pool Prepare a statement Execute and get a result set Close the result set when finished (I read somewhere Oracle can't guarantee to clean up all cursors if you simply close the prepared statement although most of the time it does) Close the prepared statement } catch (SQLExceptions e) { Check all result sets and prepared statements are closed (need to put try blocks around each in case of further sql exceptions - simply ignore the exception) } finally { // return the connection to the pool if (conn!= null) { try { conn.close(); } catch (SQLException ignore){} } } Like your self I would be interested in any documentation on the Commons database pool manager. -----Original Message----- From: Chakravarthy, Sundar [mailto:[EMAIL PROTECTED]] Sent: 13 February 2003 16:03 To: Tomcat Users List Subject: RE: RE: Oracle cursor problem with Tomcat 4.1.12 and Commons BDSF Could you tell me how to force Tomcat to close connections sooner ? Also where can I find the documentation for all the parameters I could set in the xml file ? Another question: Say I have the following statements, 1 System.out.println("Creating connection."); 2 conn = dataSource.getConnection(); 3 System.out.println("Creating statement."); 4 stmt = conn.createStatement(); What prevents the Pool Manager from closing the connection in line 2 before line 4 is reached ? Thanks, Sundar -----Original Message----- From: Robert Dana [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 05, 2003 1:16 PM To: [EMAIL PROTECTED] Subject: Re: RE: Oracle cursor problem with Tomcat 4.1.12 and Commons BDSF Not sure if this is any help, but I do have some related information. I believe the problems you are experiencing relate directly to a known bug in the Oracle JDBC drivers. In my case, I found that using a PreparedStatement object in a servlet resulted in 2 or 3 (depending on the complexity of the statement) "overhead" cursors being opened by Oracle. These cursors did not close, even when the PreparedStatement itself was closed in my code. The orphan cursors only seemed to close if the connection itself was closed - a major problem if one is trying to use any kind of efficient connection pooling. This problem has been acknowledged by Oracle, but they have not, to my knowledge, fixed it. For me, the best solution was to "de-tune" my connection pool to force connections to be closed sooner than I normally would, in combination with setting a very high value for MAXCURSORS in the init.ora file. After some experimentation, I found a combination of those 2 factors that resulted in no more "maximum open cursors" errors, with only a modest degradation in performance. A compromise solution to be sure, but one that worked out OK for me. I hope that is useful information. Robert Dana -----Original Message----- From: "Tam, Michael" <[EMAIL PROTECTED]> To: 'Tomcat Users List' <[EMAIL PROTECTED]> Date: Tue, 4 Feb 2003 18:48:16 -0500 Subject: RE: Oracle cursor problem with Tomcat 4.1.12 and Commons BDSF Maybe you can post a segment of the code or example to illustrate your problem. Michael -----Original Message----- From: Andy Meadows [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 04, 2003 2:25 PM To: Tomcat Users List Subject: Re: Oracle cursor problem with Tomcat 4.1.12 and Commons BDSF Doing that. Actually, further testing reveals that the problem occurs with the statement. If an exception occurs while the statement is being prepared, then an exception is thrown. However, it would appear that this exception is thrown after a cursor is opened (internally) and that cursor is never closed. A call to close on the statement in turn throws a NPE because a value was never assigned to it. Thus, I'm left with an open cursor on an object that I can't reach. Can anyone else validate this? Andy ----- Original Message ----- From: "Tam, Michael" <[EMAIL PROTECTED]> To: "'Tomcat Users List'" <[EMAIL PROTECTED]> Sent: Tuesday, February 04, 2003 4:22 PM Subject: RE: Oracle cursor problem with Tomcat 4.1.12 and Commons BDSF > Have seen this problem before. > It is the JDBC code. The best solution is to explicitly close RESULTSET, > STATEMENT (of any kind), and CONNECTION as soon as you finished using the > object ( or else close them in the FINALLY block) > > Regards, > Michael > > -----Original Message----- > From: Sean Dockery [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, February 04, 2003 1:04 PM > To: Tomcat Users List > Subject: Re: Oracle cursor problem with Tomcat 4.1.12 and Commons BDSF > > > Try explicitly closing your ResultSet variables as well. See if the > problem persists. > > At 13:58 2003-02-04, you wrote: > >Configuring Tomcat to provide a JNDI connection pool was no problem. Now, > >however, we are getting error ORA-01000: maximum cursors opened. Logging > >shows that any statement and connection that is opened is again closed > >which, according to everything I read, release the cursors. This is > >obviously not the case. > > > >Has anyone else experienced this problem and, if so, what was the > >resolution -- other than increasing opened cursor counts. > > > >Andy Meadows > > > > > >--------------------------------------------------------------------- > >To unsubscribe, e-mail: [EMAIL PROTECTED] > >For additional commands, e-mail: [EMAIL PROTECTED] > > Sean Dockery > [EMAIL PROTECTED] > Certified Java Web Component Developer > Certified Delphi Programmer > SBD Consultants > http://www.sbdconsultants.com > > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] a> > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > -------------------------- --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]