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
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
RE: RE: Oracle cursor problem with Tomcat 4.1.12 and Commons BDSF
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
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]
