Oh, I didn't realize that you were doing CMP through JAWS. From what
you are saying it appears to be a JAWS issue and not a JDBC driver
issue. Not seeing your stack trace I assumed the exception was
originating from the JDBC driver, not JAWS. So this exception is
originating in JAWS? Hmmm. I haven't used JAWS so I don't think I can
help you with this one. Can someone else?
Scot.
Bill Burke wrote:
> 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]
>
>
--
--------------------------------------------------------------
To subscribe: [EMAIL PROTECTED]
To unsubscribe: [EMAIL PROTECTED]
