Berin Loritsch wrote:
> Annette Doyle wrote:
>
>> After running the excalibur 4.1 pooling for some time with tomcat,
>> Oracle started giving the "ORA-01000: maximum open cursors exceeded"
>> error. I had not changed anything with Oracle or my web application,
>> except for replacing my modified excalibur 4.0 with new excalibur
>> 4.1. After looking at the 4.1 source code, I found that isClosed()
>> method in AbstractJdbcConnection class did not close the result set
>> after executing the test statement. I had fixed this in the my
>> modified version of excalibur 4.0 JdbcConnection class. ( I had fixed
>> this to track down another bug I sent in a while back for class
>> JdbcConnection in excalibur 4.0).
>
> Thank you for bringing it to our attention, it is now fixed in CVS.
I did this, but according Sun's javadocs this should not have been a
problem:
From the java.sql.ResultSet.close() javadocs:
---
Releases this ResultSet object's database and JDBC resources immediately
instead of waiting for this to happen when it is automatically closed.
Note: A ResultSet object is automatically closed by the Statement object
that generated it when that Statement object is closed, re-executed, or
is used to retrieve the next result from a sequence of multiple results.
A ResultSet object is also automatically closed when it is garbage
collected.
---
In the AbstractJdbcConnection a single statement is being opened and
kept around for the Keep Alive feature. Each time the database is
pinged, the same statement is reused to execute query. According to the
Javadocs, this should be closing any previous ResultSets. So this
shouldn't have been a problem. Is this a known place where Oracle does
not conform to the JDBC standard? We only use MySQL, PostgreSQL and
HSQL and they all seem fine.
Likewise, Statements are supposed to be automatically closed along with
their last ResultSet when they are garbage collected.
For this reason, to keep the code clean, we never close ResultSets or
Statements unless there is some immediate need to save memory or something.
Code that looks like this:
---
try
{
Connection conn = getConnection();
try
{
Statement stmt = conn.createStatement();
try
{
ResultSet rs = stmt.executeQuery(query);
try
{
// Do something with the ResultSet
}
finally
{
rs.close();
}
}
finally
{
stmt.close();
}
}
finally
{
conn.close();
}
}
catch (SQLException e)
{
m_logger.error("Error", e);
}
---
Can be reduced to much simpler code that looks like this:
---
try
{
Connection conn = getConnection();
try
{
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query);
// Do something with the ResultSet
}
finally
{
conn.close();
}
}
catch (SQLException e)
{
m_logger.error("Error", e);
}
---
What standards do you usually follow when dealing with ResultSets and
Statements? PreparedStatements will cause errors in MySQL drivers if
they are closed. The driver keeps them around to be reused. So we
never close them either.
Thanks,
Leif
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
