?
Solved

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

Posted on 2007-10-16
12
Medium Priority
?
697 Views
Last Modified: 2013-11-24
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;
    }
}
0
Comment
Question by:rospcc
  • 7
  • 5
12 Comments
 
LVL 23

Expert Comment

by:Ajay-Singh
ID: 20084557
don't keep static connection object, you are loosing reference.
0
 

Author Comment

by:rospcc
ID: 20084587
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
 
LVL 23

Expert Comment

by:Ajay-Singh
ID: 20084615
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:rospcc
ID: 20084649
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
 
LVL 23

Expert Comment

by:Ajay-Singh
ID: 20084666
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
 
LVL 23

Expert Comment

by:Ajay-Singh
ID: 20084673
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
 

Author Comment

by:rospcc
ID: 20091506
When we close the statement and the connection, will the rs still contain any data when returned?
0
 
LVL 23

Expert Comment

by:Ajay-Singh
ID: 20099222
NO
0
 

Author Comment

by:rospcc
ID: 20099245
In that case the solution that you've posted (i.e. sqlMethod) will return rs which will never have data?
0
 
LVL 23

Expert Comment

by:Ajay-Singh
ID: 20099327
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
 

Author Comment

by:rospcc
ID: 20099379
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
 
LVL 23

Accepted Solution

by:
Ajay-Singh earned 2000 total points
ID: 20099441
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The viewer will learn how to implement Singleton Design Pattern in Java.
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
Suggested Courses

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question