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;
    }
}
rospccAsked:
Who is Participating?
 
Ajay-SinghCommented:
They also get returned to their pool, but it depends on what connection pool
library you are using.
 
But I will not suggest to close the statement/connection or even execute the
sql statements on the helper class. You should execute them on the bean or
original class.
0
 
Ajay-SinghCommented:
don't keep static connection object, you are loosing reference.
0
 
rospccAuthor Commented:
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?
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Ajay-SinghCommented:
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.
0
 
rospccAuthor Commented:
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.
0
 
Ajay-SinghCommented:
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();
    }
  }

}
0
 
Ajay-SinghCommented:
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;
        }
}
0
 
rospccAuthor Commented:
When we close the statement and the connection, will the rs still contain any data when returned?
0
 
Ajay-SinghCommented:
NO
0
 
rospccAuthor Commented:
In that case the solution that you've posted (i.e. sqlMethod) will return rs which will never have data?
0
 
Ajay-SinghCommented:
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.
0
 
rospccAuthor Commented:
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?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.