RE: [JBoss-dev] jbosscx rfe 677512
thank you timothy for the tip.
i've gone through my code and i'm now closing all resultset's. so far this
afternoon, i haven't seen any errors come up. there's one thing that really
bothers me though, reading the jdk 1.4.1 javadocs, it states "All execution
methods in the Statement interface implicitly close a statment's current
ResultSet object if an open one exists."
so, is this another example of oracle's thin jdbc driver of not following
the spec? personally, i think that dealing with all of oracle's
"exceptions" to the rule is ridiculous, but what are you going to do? =)
long live open source.
Ryan
-Original Message-
From: Timothy Barreto [mailto:[EMAIL PROTECTED]]
Sent: Monday, February 17, 2003 5:22 PM
To: [EMAIL PROTECTED]
Subject: Re: [JBoss-dev] jbosscx rfe 677512
Ryan,
I had the exact same configuration (Oracle 8i/JBoss 3) and problem with the
cursors. I found an article on the Oracle site that explained the whole
deal. Turned out I wasn't closing the result sets.
Tim
ORA-01000: maximum open cursors exceeded
"I suspect that statement.close() leaves DB Cursors open. ... After doing a
lot of database fetches in JDBC, I eventually get the following error:"
ORA-01000: maximum open cursors exceeded
* The error ORA-01000: maximum open cursors exceeded happens when not
all
of the statement and result set objects are closed. In almost all cases when
this error occurs, the programmer discovers that he or she has inadvertently
missed the close() methods. Please make sure that all your statement, result
set, and connection objects are explicitly closed after you have finished
processing to avoid this error. The following is a snippet for such code:
try { ...
} finally {
try { rs.close(); rs=null;
stmt.close(); stmt=null;
} catch (Exception e){}
}
* Note that if -for other reasons- you need to increase the maximum
number
of cursors, you can do so in the initxxx.ora by specifying (assuming the
default is 50 or so)
open_cursors=200
* You should consider using SQLJ from the start. SQLJ will automatically
manage statement objects (though you still must take care to close your
result sets and connection contexts). Then you will not have to look at code
such as the following from the Oracle 8.1.5 Application Developer's Guide -
Large Objects (LOBs):
OracleCallableStatement cstmt = (OracleCallableStatement)
conn.prepareCall
("BEGIN DBMS_LOB.OPEN( ?, DBMS_LOB.LOB_READWRITE); END;");
cstmt.setCLOB(1, lob_loc);
cstmt.execute();
...
cstmt = (OracleCallableStatement) conn.prepareCall
("BEGIN DBMS_LOB.CLOSE(?); END;");
cstmt.setCLOB(1, lob_loc);
cstmt.execute();
cstmt.close();
The above code opens two cursors with the cstmt but ends up closing only
one.
In SQLJ you would have written much more correctly and concisely:
#sql { BEGIN DBMS_LOB.OPEN(:lob_loc, DBMS_LOB.LOB_READWRITE); END; };
...
#sql { BEGIN DBMS_LOB.CLOSE(:lob_loc); END; };
Or, consider the following subtly buggy code:
switch (mode)
{
case READ_ONLY :
dbStatement =
conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
case READ_WRITE :
dbStatement =
conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
}
Notice the missing break statement which causes the leaking of cursors.
* You also need to make sure to close all of the SQLJ connection context
objects, rather than waiting for them to be garbage-collected. SQLJ 8.1.6
and
later performs automatic caching of the last five SQLJ statements that have
been executed. Whenever you close the SQLJ connection context you ensure
that
the statements cached on it are being closed as well. You can use the method
close(sqlj.runtime.ConnectionContext.KEEP_CONNECTION) if you just want to
clean up the SQLJ statement cache but not close the underlying JDBC
connection.
Cleaning up the SQLJ connection context is particularly important in
server-side code, since closing the server-side default connection is
performed as a no-op and will not result in any statement (or cursor)
cleanup. Alternatively, you may just want to use a single static SQLJ
connection context, such as the default context, in your server-side code.
* A final tip: In order to find the bad cursors, debug/break in the
exception catcher, and then look at all opened cursors in TOP SESSIONS. In
every case of "max cursors exceeded", the same cursor was present hundreds
of
times, and easily found.
On Monday 17 February 2003 03:41 pm, Sonnek, Ryan wrote:
> 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 conti
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
Re: [JBoss-dev] jbosscx rfe 677512
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 --- 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
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
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
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of Igor > Fedorenko > Sent: Monday, February 17, 2003 1: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. > 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. We need to implement this in the 3.x series. 2.4 already had this feature and is all around more tolerant of buggy application code. I have this on my todo list, but if anybody is willing to implement right away, let us know. 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 --- 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
> unfortunately this is a MAJOR problem for us right now, since > we can't get our application up with 24/7 reliability. it code it yourself, wait for someone to do it on their own free time when they want or pay us to do it on your timeline, but frankly don't come here with FREE DEMANDS. You don't ASK FOR FREE WORK. You pay for work or give your work. Free software doesn't mean free development why is that so complex to grasp? > 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? It is a good idea by the way. It would be integrated. marcf --- 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: 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
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
[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? 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
