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
[EMAIL PROTECTED]
> -----Original Message-----
> From: Paul French [mailto:[EMAIL PROTECTED]]
> 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:[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]
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
