I changed the code so that it does direct JDBC calls instead of using
the connection pool. Still after awhile, I get maximum open cursors
exceeded originating from JAWS. The funny thing is, is that direct JDBC
still works, but any JAWS calls do not. I am the only user of JBoss and
the Database. Any hints? I'm running JBoss 2.0-Final on Linux with
Oracle thin JDBC drivers.
I've read that JBoss caches Statements and PreparedStatements. How does
it know the MAX_OPEN_CURSORS of Oracle?
Bill
Scot Bellamy wrote:
> Your code looks perfectly fine. You are properly closing the
> statement, which is where the opening of the Oracle cursor is
> initiated. Do you have many threads accessing different bean
> instances simultaneously? If so, you might be exceeding the maximum
> open cursors because of the number of concurrent threads. If this is
> the case, the only way to fix this is to either reduce the number of
> concurrent threads, or increase the MAX_OPEN_CURSORS parameter of your
> Oracle database. If you are seeing this as a result of running a
> single thread over and over, it might be a bug in the JDBC driver you
> are using. Make sure you are using the latest version of the thin
> driver available on the Oracle web site.
>
> Scot.
>
> Bill Burke wrote:
>
>> I'm not using PreparedStatement though, I'm using just a regular
>> Statement. Does JBoss pool Statements? Why does it do this if it does?
>>
>> Bill
>>
>> Ari Suutari wrote:
>>
>>> Hi,
>>>
>>> If you are sure that you close resultset and statement this should not
>>> happen, maybe Minerva is pooling prepared statements, I remember
>>> some discussions about having limit for such cached statetements.
>>>
>>> Ari S.
>>>
>>> ----- Original Message ----- From: "Bill Burke"
>>> <[EMAIL PROTECTED]>
>>> To: "JBoss-User" <[EMAIL PROTECTED]>
>>> Sent: Tuesday, March 06, 2001 5:52 AM
>>> Subject: Re: [jBoss-User] maximum open cursors exceeded
>>>
>>>
>>>> I tried closing the ResultSet before the Statement and still, after
>>>> awhile, I get maximum open cursors exceeded. Anybody else run into
>>>> this problem with regular EJBs?
>>>>
>>>> Thanks,
>>>> Bill
>>>>
>>>> Bill Burke wrote:
>>>>
>>>>> I don't know if this is wrong to do, but I'm using an Oracle
>>>>> connection pool that I created for my EJB's to execute SQL queries
>>>>> and updates. The problem is, after awhile I get a "maximum open
>>>>> cursors exceeded" error from Oracle. I'm running on RedHat linux
>>>>> 6.2 with an Oracle DB running on NT. I'm using the
>>>>> jdbc:oracle:thin drivers.
>>>>>
>>>>> jboss.jcml:
>>>>> <mbean name="DefaultDomain:service=XADataSource,name=oraclePool">
>>>>> ....
>>>>> </mbean>
>>>>>
>>>>> Java code is below.
>>>>> Some things to note, executeSQLUpdate and executeSQLQuery. I
>>>>> can't seem to find out where I'm not cleaning up connections. Do
>>>>> you have to close the ResultSet? The JDBC docs say that if you
>>>>> close the Statement, then your result sets will be closed as well.
>>>>>
>>>>> Thanks in advance,
>>>>> Bill
>>>>>
>>>>> Java Code:
>>>>>
>>>>>
>>>>> import java.io.*;
>>>>> import java.rmi.*;
>>>>> import javax.ejb.*;
>>>>> import java.util.*;
>>>>> import javax.naming.*;
>>>>> import com.ctone.services.utils.*;
>>>>> import com.ctone.services.constants.*;
>>>>> import com.ctone.services.framework.*;
>>>>> import javax.sql.*;
>>>>> import java.sql.*;
>>>>>
>>>>> public class CTSQLServiceBean extends CTServiceBean
>>>>> {
>>>>> public CTSQLServiceBean()
>>>>> {
>>>>> super();
>>>>> }
>>>>>
>>>>> private Connection getConnection() throws Exception
>>>>> {
>>>>> javax.sql.DataSource source =
>>>>> (javax.sql.DataSource)getInitialContext().lookup("java:/oraclePool");
>>>>> return source.getConnection();
>>>>> }
>>>>>
>>>>> public int executeSQLUpdate(String sqlUpdate) throws RemoteException
>>>>> {
>>>>> Connection connect = null;
>>>>> Statement statement = null;
>>>>> int rowCount = 0;
>>>>>
>>>>> try
>>>>> {
>>>>> connect = getConnection();
>>>>> if (connect == null)
>>>>> {
>>>>> ServicesLog.logAndThrow( CTExceptionType.RECOVERABLE,
>>>>> "Unable to connect to database." );
>>>>> }
>>>>> ServicesLog.log( 5, "Got connection, creating statement..." );
>>>>>
>>>>> statement = connect.createStatement();
>>>>> if (statement == null)
>>>>> {
>>>>> ServicesLog.logAndThrow( CTExceptionType.RECOVERABLE,
>>>>> "Unable to create SQL statement for: '" +
>>>>> sqlUpdate + "'" );
>>>>> }
>>>>> ServicesLog.log( 5, "Created statement, executing query..." );
>>>>>
>>>>> rowCount = statement.executeUpdate( sqlUpdate );
>>>>> if (rowCount == 0)
>>>>> {
>>>>> ServicesLog.log( 5, "No rows affected by SQL statement '" +
>>>>> sqlUpdate + "'" );
>>>>> }
>>>>> else
>>>>> {
>>>>> ServicesLog.log( 5, String.valueOf( rowCount ) + " rows
>>>>> affected by SQL statement..." );
>>>>> }
>>>>> }
>>>>> catch (SQLException sqe)
>>>>> {
>>>>> ServicesLog.logAndThrow( CTExceptionType.RECOVERABLE,
>>>>> "Unable to execute SQL query: '" + sqlUpdate + "'",
>>>>> sqe );
>>>>> }
>>>>> catch (Exception ex)
>>>>> {
>>>>> ServicesLog.logAndThrow( CTExceptionType.RECOVERABLE,
>>>>> "Unable to get connection pool Driver.",
>>>>> ex );
>>>>> }
>>>>> finally
>>>>> {
>>>>> if (statement != null)
>>>>> {
>>>>> try
>>>>> {
>>>>> statement.close();
>>>>> }
>>>>> catch (SQLException sqe)
>>>>> {
>>>>> }
>>>>> }
>>>>>
>>>>> if (connect != null)
>>>>> {
>>>>> try
>>>>> {
>>>>> connect.close();
>>>>> }
>>>>> catch (SQLException sqe)
>>>>> {
>>>>> }
>>>>> }
>>>>> }
>>>>>
>>>>> return rowCount;
>>>>> }
>>>>>
>>>>> public CTResultSet executeSQLQuery(String sqlQuery) throws
>>>>> RemoteException
>>>>> {
>>>>> Connection connect = null;
>>>>> Statement statement = null;
>>>>> CTResultSet results = null;
>>>>>
>>>>> try
>>>>> {
>>>>> connect = getConnection();
>>>>> if (connect == null)
>>>>> {
>>>>> ServicesLog.logAndThrow( CTExceptionType.RECOVERABLE,
>>>>> "Unable to connect to database." );
>>>>> }
>>>>> ServicesLog.log( 5, "Got connection, creating statement..." );
>>>>>
>>>>> statement = connect.createStatement();
>>>>> if (statement == null)
>>>>> {
>>>>> ServicesLog.logAndThrow( CTExceptionType.RECOVERABLE,
>>>>> "Unable to create SQL statement for query: '" +
>>>>> sqlQuery + "'" );
>>>>> }
>>>>> ServicesLog.log( 5, "Created statement, executing query..." );
>>>>>
>>>>> ResultSet resultSet = statement.executeQuery( sqlQuery );
>>>>> if (resultSet == null)
>>>>> {
>>>>> ServicesLog.log( 5, "No results returned for query '" +
>>>>> sqlQuery + "'" );
>>>>> }
>>>>> else
>>>>> {
>>>>> ServicesLog.log( 5, "Got results from query..." );
>>>>> results = new CTResultSet( resultSet );
>>>>> }
>>>>> }
>>>>> catch (SQLException sqe)
>>>>> {
>>>>> ServicesLog.logAndThrow( CTExceptionType.RECOVERABLE,
>>>>> "Unable to execute SQL query: '" + sqlQuery + "'",
>>>>> sqe );
>>>>> }
>>>>> catch (Exception ex)
>>>>> {
>>>>> ServicesLog.logAndThrow( CTExceptionType.RECOVERABLE,
>>>>> "Unable to get connection pool Driver.",
>>>>> ex );
>>>>> }
>>>>> finally
>>>>> {
>>>>> if (statement != null)
>>>>> {
>>>>> try
>>>>> {
>>>>> statement.close();
>>>>> }
>>>>> catch (SQLException sqe)
>>>>> {
>>>>> }
>>>>> }
>>>>>
>>>>> if (connect != null)
>>>>> {
>>>>> try
>>>>> {
>>>>> connect.close();
>>>>> }
>>>>> catch (SQLException sqe)
>>>>> {
>>>>> }
>>>>> }
>>>>> }
>>>>>
>>>>> return results;
>>>>> }
>>>>> }
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> --------------------------------------------------------------
>>>>> To subscribe: [EMAIL PROTECTED]
>>>>> To unsubscribe: [EMAIL PROTECTED]
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> --------------------------------------------------------------
>>>> To subscribe: [EMAIL PROTECTED]
>>>> To unsubscribe: [EMAIL PROTECTED]
>>>>
>>>
>>>
>>>
>>> --
>>> --------------------------------------------------------------
>>> To subscribe: [EMAIL PROTECTED]
>>> To unsubscribe: [EMAIL PROTECTED]
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>> --
>> --------------------------------------------------------------
>> To subscribe: [EMAIL PROTECTED]
>> To unsubscribe: [EMAIL PROTECTED]
>>
>>
>
>
>
> --
> ----------------------------------------------
--
--------------------------------------------------------------
To subscribe: [EMAIL PROTECTED]
To unsubscribe: [EMAIL PROTECTED]
