[jBoss-User] maximum open cursors exceeded

[Bill Burke]

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]