asked on

Connection not closed properly? Therefore causing Oracle ORA-01000: maximum open cursors?

HI All,

My application is running on SunAppServer 8.2 accessing Oracle 9i. I have stripped my functions to a very simple module for easier debugging. I simulated 10 concurrent access to 1 simple JSP page (without any other codes, only 1 code to access the Java method getID below. I believe that the resultset and connection had been closed properly. However, I still noticed that the open_cursor kept on going up.
It doesn't happen when I simulated only 1 user access. I can see that the open_cursor maintained at 1. Any idea what happened?
I will be meeting my client tomorrow, and I will need to have some good explanation to this..=(

JSP
---------
<%@ page import="java.io.*,
java.lang.String"%>
<%@ page pageEncoding="UTF-8"%>
<jsp:useBean id="TBID" class="bean.TableID" scope="page"/>
<%=TBID.getID(20) + "<br>"%>

JAVA
-----------

public class ConnectionBean
{
private static Connection con = null;

public static Connection getConnection()
{
try
{
       InitialContext context = new InitialContext();
       //Look up our data source
       DataSource ds = (DataSource) context.lookup("jdbc/connpool");
       //Allocate and use a connection from the pool
       con = ds.getConnection();
}
catch (Exception e)
{
System.out.println("getConnection" + e);
}
return con;
}

public synchronized static void close() throws Exception
{
if (con != null)
{
      con.close();
con = null;
}
}

}

public class sqlMethod {

      private static ResultSet rs=null;
      private static Connection con=null;
      private static Statement st=null;

      public static ResultSet rsQuery(String sql)
      {
            try
            {
                  con=ConnectionBean.getConnection();
                  st=con.createStatement();
                  rs=st.executeQuery(sql);


            }
            catch(Exception e)
            {
                  System.out.println("sqlMethod rsQuery is abnormal"+e);
            }
            finally
            {
                  close();
            }
            return rs;
      }

     public synchronized static void close()
      {
            try
            {
                  if (con != null) {
                        ConnectionBean.close();
                  }
            }
            catch(Exception er)
            {
                  System.out.println("con='" + con + "' close is abnormal "+er);
            }

      }

      public synchronized static void closeStmt()
      {
            try
            {
                  if (st != null) {
                        st.close();
                        st = null;
                  }
            }
            catch(Exception er)
            {
                  System.out.println("st='" + st + "' close is abnormal "+er);
            }

      }
}

public class TableID {
public int getID(int iCandID) {
            int iID = 0
            ResultSet rs = null;
            try
            {
                  String query = "SELECT * FROM ID WHERE (FKID= " + iCandID + ")";
                  rs=sqlMethod.rsQuery(query);

if(rs.next()) {
      iID = rs.getInt("PKID");
}
            }
            catch(Exception E)
{
System.err.println(E);
}
finally
{
      if(rs != null)
      {
            try{
            rs.close();
rs = null;
            } catch(Exception E)
{
                  E.printStackTrace();
}

      }
      sqlMethod.closeStmt();
      sqlMethod.close();
}

      return iID;
}
}

Ajay-Singh

don't keep static connection object, you are loosing reference.

rospcc

ASKER

May I know what is the proper or better way to declare the connection? It will be quite troublesome to pass the connection created via every function right?

Ajay-Singh

I agree. Instead of having static method that holds the connection, you can
have a helper class (or same class) that will just return the connection and
caller will close the connection once its done.

rospcc

ASKER

Thought that's what we're doing right now. Perhaps I missed out something. Could you give me an example using the codes that I have above? Thanks.

Ajay-Singh

public class ConnectionBean
{
public static Connection getConnection() throws SQLException
{
Connectin con = null;
InitialContext context = new InitialContext();
//Look up our data source
DataSource ds = (DataSource) context.lookup("jdbc/connpool");
//Allocate and use a connection from the pool
con = ds.getConnection();
return con;
}

public synchronized static void close( Connection con ) throws Exception
{
if (con != null)
{
con.close();
}
}

}

Ajay-Singh

and use that class as

public class sqlMethod {

public static ResultSet rsQuery(String sql)
{
ResultSet rs=null;
Connection con=null;
Statement st=null;
try
{
con=ConnectionBean.getConnection();
st=con.createStatement();
rs=st.executeQuery(sql);

}
catch(Exception e)
{
System.out.println("sqlMethod rsQuery is abnormal"+e);
}
finally
{
if(st != null) {
st.close();
}

if(con != null) {
con.close();
}
}
return rs;
}
}

rospcc

ASKER

When we close the statement and the connection, will the rs still contain any data when returned?

Ajay-Singh

rospcc

ASKER

In that case the solution that you've posted (i.e. sqlMethod) will return rs which will never have data?

Ajay-Singh

I assumed that connection is using connection pool. So, in that case, the connection will not be physically be closed, rather put back to the pool.

rospcc

ASKER

Sorry, what about the statement (st)? Don't think statement can be returned to pool right? Does it mean that when we close the statement, we can still return rs with data?

ASKER CERTIFIED SOLUTION

Ajay-Singh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial