tomcat-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mike Jackson" <mjack...@cdi-hq.com>
Subject RE: RE: Oracle cursor problem with Tomcat 4.1.12 and Commons BDSF
Date Thu, 13 Feb 2003 17:14:00 GMT
I usally put the clean up in the finally block personally, something like
this:

	Connection con = null;
	PreparedStatement ps  = null;
	ResultSet rs = null;

	try {
		con = getConnection();
		PreparedStatement ps = con.prepareStatement( "select systime from dual" );
		ResultSet rs = ps.executeQuery();
		if ( rs.next() ) {
			System.out.println( "Now=" + rs.String( 1 );
		}
	}
	catch( SQLException e ) {
		// do somethign
	}
	finally {
		if ( rs != null ) {
			try { rs.close(); } catch( Exception e ) {}
		}
		if ( ps != null ) {
			try { ps.close(); } catch( Exception e ) {}
		}
		if ( con != null ) {
			try { con.close(); } catch( Exception e ) {}
		}
	}

But your results may vary, batteries not included, some assembly required...

--mikej
-=-----
mike jackson
mjackson@cdi-hq.com

> -----Original Message-----
> From: Paul French [mailto:Paul.french@thetrainline.com]
> Sent: Thursday, February 13, 2003 8:47 AM
> To: 'Tomcat Users List'
> Subject: RE: RE: Oracle cursor problem with Tomcat 4.1.12 and Commons
> BDSF
>
>
> 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:schakravarthy@doas.ga.gov]
> 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:robert.a.dana@orcmacro.com]
> Sent: Wednesday, February 05, 2003 1:16 PM
> To: tomcat-user@jakarta.apache.org
> 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" <mtam@pfc.cfs.nrcan.gc.ca>
> To: 'Tomcat Users List' <tomcat-user@jakarta.apache.org>
> 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:jakarta.andy@meadowsdesign.cc]
> 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" <mtam@pfc.cfs.nrcan.gc.ca>
> To: "'Tomcat Users List'" <tomcat-user@jakarta.apache.org>
> 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:sean@sbdconsultants.com]
> > 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: tomcat-user-unsubscribe@jakarta.apache.org
> > >For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
> >
> > Sean Dockery
> > sean@sbdconsultants.com
> > Certified Java Web Component Developer
> > Certified Delphi Programmer
> > SBD Consultants
> > http://www.sbdconsultants.com
> >
> >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
> > For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
> > For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
> >
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
>
>
> a>
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
> > For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
> >
> > --------------------------
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
>



---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org


Mime
View raw message