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]
