Re: [JBoss-dev] jbosscx rfe 677512
I'd rather figure out what is causing the problem. Does cursor == open statement? Open resultset? Are you using cmp? If bmp are you closing everything in finally blocks? A known problem is that the jca-jdbc wrappers don't track open statements for you and close them when you close the connection handle. This is not hard to fix, but I haven't done it yet. You might be able to make a solution to this problem in any cvs version of jboss if you can come up with some sql that will fail if too many cursors are open on a connection. All cvs versions now have CheckValidConnectionSQL for you to set: if you set it, this statement is executed when a connection is taken out of the pool and before it is handed out to your application. If you can determine from some Oracle info which connection it is and how many cursors or how many times the statement has been called, you could throw some kind of exception that would force the connection to be destroyed. thanks david jencks On 2003.02.17 11:50 Sonnek, Ryan wrote: i posted this request about a week ago, and would like to know any developers thoughts on this. to summerize, i'd like to be able to force a datasource to timeout any connections in the pool after a period of time. we're running jboss 3.0.6 with oracle 8i and right now and we're constantly running into an error Maximum number of cursors exceeded. being able to timeout connections that have been used heavily to restore those cursors is the only idea i had to get around this error. unfortunately this is a MAJOR problem for us right now, since we can't get our application up with 24/7 reliability. it only takes a matter of minutes of heavy activity to exceed 300 cursors on oracle. if there is some other way to get around this error, i would appreciate any feedback. or, if this is a planned enhancement, when would it be integrated? thanks in advance. Ryan --- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf ___ Jboss-development mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-development --- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf ___ Jboss-development mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-development
Re: [JBoss-dev] jbosscx rfe 677512
Bill Burke wrote: I am not sure I understand your problem. In RFE you are saying cached prepared statements stay alive and cause this error. What caches prepared statements? Did you explicitly enabled statement caching on oracle connections? I remember seeing this error message but it turned to be a problem in one of my SSBs which did not explicitly close java.sql.[Prepared]Statement. Well, strictly speaking it is a known JBoss bug/limitation -- Connection.close() does not close all opened [Prepared]Statement as it is supposed to -- but is has a reasonable workaround. Igor is correct. You are probably not closing your statements and/or ResultSets. You can get what sql statements are being leaked from Oracle. Backtrack in your code to find out where you are not closing your statements. You should also be able to work out where the problem is by checking with Oracle - there's a system table called v$open_cursors or something like that and it is sometimes possible to work out which SQL call is causing the problem from there and tidy up your closing code. -- Luke Taylor. Monkey Machine Ltd. PGP Key ID: 0x57E9523Chttp://www.monkeymachine.ltd.uk --- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf ___ Jboss-development mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-development
RE: [JBoss-dev] jbosscx rfe 677512
thank you all for your reply, i'll try and clear some things up for all those that responded. mark: sorry if i sounded demanding. i am more than willing to contribute my knowledge (however limited) to help the jboss project continue to thrive. i've been an avid user of jboss for the past 2 years now, and just recently i've begun to dig into the actual code. that being said, i still consider myself a relative rookie, and my post was meant to ask advice, not demand service. again, sorry if my netiquete came off rude. david, igor: i'm using BMP beans and all of my database connections and prepared statements are being closed in finally blocks. my resulset's are not being closed at all, but isn't it stated somewhere that closing the statement, closes all resultsets? i will test if closing resultset's makes any difference. bill, luke: thank you for your ideas, i will be checking the oracle tables you mentioned to see if i can get some more information. thank you all again for your replies! Ryan -Original Message- From: Igor Fedorenko [mailto:[EMAIL PROTECTED]] Sent: Monday, February 17, 2003 12:56 PM To: [EMAIL PROTECTED] Subject: RE: [JBoss-dev] jbosscx rfe 677512 -Original Message- From: Sonnek, Ryan [mailto:[EMAIL PROTECTED]] Sent: Monday, February 17, 2003 11:50 AM To: '[EMAIL PROTECTED]' Subject: [JBoss-dev] jbosscx rfe 677512 i posted this request about a week ago, and would like to know any developers thoughts on this. to summerize, i'd like to be able to force a datasource to timeout any connections in the pool after a period of time. we're running jboss 3.0.6 with oracle 8i and right now and we're constantly running into an error Maximum number of cursors exceeded. being able to timeout connections that have been used heavily to restore those cursors is the only idea i had to get around this error. unfortunately this is a MAJOR problem for us right now, since we can't get our application up with 24/7 reliability. it only takes a matter of minutes of heavy activity to exceed 300 cursors on oracle. if there is some other way to get around this error, i would appreciate any feedback. or, if this is a planned enhancement, when would it be integrated? I am not sure I understand your problem. In RFE you are saying cached prepared statements stay alive and cause this error. What caches prepared statements? Did you explicitly enabled statement caching on oracle connections? I remember seeing this error message but it turned to be a problem in one of my SSBs which did not explicitly close java.sql.[Prepared]Statement. Well, strictly speaking it is a known JBoss bug/limitation -- Connection.close() does not close all opened [Prepared]Statement as it is supposed to -- but is has a reasonable workaround. --- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf ___ Jboss-development mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-development --- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf ___ Jboss-development mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-development
RE: [JBoss-dev] jbosscx rfe 677512
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Sonnek, Ryan Sent: Monday, February 17, 2003 4:42 PM To: '[EMAIL PROTECTED]' Subject: RE: [JBoss-dev] jbosscx rfe 677512 thank you all for your reply, i'll try and clear some things up for all those that responded. mark: sorry if i sounded demanding. i am more than willing to contribute my knowledge (however limited) to help the jboss project continue to thrive. i've been an avid user of jboss for the past 2 years now, and just recently i've begun to dig into the actual code. that being said, i still consider myself a relative rookie, and my post was meant to ask advice, not demand service. again, sorry if my netiquete came off rude. david, igor: i'm using BMP beans and all of my database connections and prepared statements are being closed in finally blocks. my resulset's are not being closed at all, but isn't it stated somewhere that closing the statement, closes all resultsets? i will test if closing resultset's makes any difference. I thought this was the case too(but I usually close ResultSets anyways). Let us know what you find. Bill --- This sf.net email is sponsored by:ThinkGeek Welcome to geek heaven. http://thinkgeek.com/sf ___ Jboss-development mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-development
Re: [JBoss-dev] jbosscx rfe 677512
On Monday, February 17, 2003, at 05:21 PM, Timothy Barreto wrote:
try { ...
} finally {
try { rs.close(); rs=null;
stmt.close(); stmt=null;
} catch (Exception e){}
}
You need to put rs.close() and stmt.close() in different try blocks.
If rs.close() throws an exception stmt.close() may never get called.
In the CMP engine I have a utility class that contains a bunch of
helper methods for closing db resources. Here is a snippet.
public final class JDBCUtil
{
private static Logger log =
Logger.getLogger(JDBCUtil.class.getName());
public static void safeClose(Connection con)
{
if(con != null)
{
try
{
con.close();
} catch(SQLException e)
{
log.error(SQL error, e);
}
}
}
...
}
I have a safeClose method for each resource type. This makes the
cleanup code very easy to write as you don't have to check for nulls.
For example:
try{
// whatever
} finally {
JDBCUtil.safeClose(rs);
JDBCUtil.safeClose(stmt);
}
-dain
---
This sf.net email is sponsored by:ThinkGeek
Welcome to geek heaven.
http://thinkgeek.com/sf
___
Jboss-development mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/jboss-development
