Solved

JSP jdbc bean doesn't seem to release cursor !?

Posted on 2001-07-30
10
415 Views
Last Modified: 2013-11-21
Hello,

I am running oracle 8.1.6, and the 1.5.2 orion server.

I am using connection pooling.

I am using a JSP page with a jdbc java bean.

Everything works fine, except if I execute about 40-50 rapid executions of the JSP page, I run out of cursors.

I have rs.close, stmt.close, and connection.close() commands at the end of each SQL call, BUT I see the cursor count climb with each execution.

The cursor count does go down with time, - I imagine the cursor times out. Also I notice that once the jsp page executes, I will not see the db connection close until a few minutes later, even though I have issued conn.close() explictedly (?) !

Can anyone give me some insight into what may be going on?

Keith
0
Comment
Question by:keithedward
10 Comments
 
LVL 1

Expert Comment

by:lawrie
ID: 6336042
Hello

Just some thoughts:

Is the connection pool smaller than the cursor limit? Perhaps try reducing the pool size?

Perhaps try doing a garbage collect or runFinalization after closing the connection. Do the connections close immediately now? For performance you don't want to do this if not necessary, but it may be better than falling over?
0
 
LVL 3

Expert Comment

by:black
ID: 6336181
If you are using a connection pool the connection.close does not actually close the connection, it just returns it back to the pool.
First thing to do is to make sure you have a try and a finally and that you close all your db resources in the finally block, maybe you could put up the code in your jsp and what appserver are you using to do database pooling
try {
} catch (Exception e) {
} finally {
  // close all db resources here
}
0
 
LVL 3

Expert Comment

by:black
ID: 6336185
Sorry you already said Orion, does that provide the connection pooling or are you using another third party package?
0
 
LVL 3

Expert Comment

by:rjackman
ID: 6336913
Hi
can u post ur code
Cheers
RJ
0
 
LVL 3

Expert Comment

by:ibro
ID: 6337034
Hi keithedward,
 conn.clolse() doesn't actualy close the database connection. It marks it as "free" and sends it back to the pool. When another instance wants a connection it gives connection from the pool. There are parameters for the connection pooling, which controls the inactive timeout. This means if the connections stays idle for a specified time, it is automaticly close (physicaly).
Also check your code if it closes the connection in case of exception. As back@devx suggest, modify your code so you can have closing of all db-resource at the finaly stage.
 hope this helps
0
Superior storage. Superior surveillance.

WD Purple drives are built for 24/7, always-on, high-definition security systems. With support for up to 8 hard drives and 32 cameras, WD Purple drives are optimized for surveillance.

 

Author Comment

by:keithedward
ID: 6340295
Here is my code.

When I run the jsp page, and then look at the Oracle cursor count  with:

select * form v$sysstat where name='opened cursors current'

I see the cursor count jump, and linger long after the page has finished loading. Eventually the count drops, BUT I would like to know WHY the cursor count does not drop immediately after giving the rs.close and stmt.close ???



<%@ page language="java" import="java.sql.*" %>
<jsp:useBean id="MACHINE" class="m" scope="page"/>
<jsp:setProperty name="MACHINE" property="*"/>
<% MACHINE.fetchMachines(); %>

<html>
<body>

<TABLE>
<TR>
<TD>CODE</TD>
<TD>NAME</TD>
<TD>GROUP_ID</TD>
</TR>
<% while (MACHINE.fetchRow()){ %>
<TR>
<TD><a href="displayFormMACHINE.jsp?CODE=<%=MACHINE.getCODE()%>"> <%=MACHINE.getCODE()%></a></TD>
<TD> <%=MACHINE.getNAME()%></TD>
<TD> <%=MACHINE.getGROUP_ID()%></TD>
</TR>
<% };
 %>
</TABLE>
</BODY>
</HTML>
<@ include file="bot.html"; %>


========================================================
========================================================

import java.util.*;
import java.sql.*;

public class m extends sqlForm {

private Statement stmt=null;
private ResultSet rs;
private String CODE="";
private String NAME="";
private String GROUP_ID="";

public void cleanup() throws Exception {
takeDown() };


public m() throws Exception {
makeConnection();
rs=null;
CODE="";
NAME="";
GROUP_ID="";
}


public String getCODE()
     {
       return CODE;
     }

public String getNAME()
     {
       return NAME;
     }

public String getGROUP_ID()
     {
       return GROUP_ID;
     }


public void setCODE( String  CODE_set)
     {
   if (CODE_set == null) CODE=""; else CODE=CODE_set.trim();
     }

public void setNAME( String  NAME_set)
     {
   if (NAME_set == null) NAME=""; else NAME=NAME_set.trim();
     }

public void setGROUP_ID( String  GROUP_ID_set)
     {
   if (GROUP_ID_set == null) GROUP_ID=""; else GROUP_ID=GROUP_ID_set.trim();
     }


public int fetchMachines()
throws Exception
    {
     stmt = myConn.createStatement();
 rs=stmt.executeQuery( "SELECT * FROM MACHINE ORDER BY CODE");
return 0;};



public boolean fetchRow()
throws Exception
    {
if (rs.next()) {
setCODE( (String) rs.getString("CODE"));
setNAME( (String) rs.getString("NAME"));
setGROUP_ID( (String) rs.getString("GROUP_ID"));
rs.close();
stmt.close();
takeDown();
}
return false;
}


};


========================================================
========================================================

import java.sql.*;
import java.io.*;
import javax.sql.DataSource;
import javax.naming.InitialContext;

public abstract class sqlForm
{
  private String myDriver = "oracle.jdbc.driver.OracleDriver";

  protected Connection myConn;
 
  public sqlForm() {}

  public void makeConnection() throws  Exception
  {
   InitialContext ctx = new InitialContext();
   DataSource ds = (DataSource) ctx.lookup("jdbc/ccfopDS");
   myConn = ds.getConnection();
   System.out.println("Connection made:-->ccfop " + myConn);
  }

  public abstract void cleanup() throws Exception;
 


 
  public void takeDown() throws Exception
  {
   System.out.println("DB connection closed");
   myConn.close();
  }
 
}
0
 
LVL 3

Accepted Solution

by:
black earned 50 total points
ID: 6342796
DO NOT DECLARE DB RESOURCES TO BE PRIVATE!!
In a JSP/Servlet environment it is multi-threaded, if the public void makeConnection method is called by two threads one after the other you'd get two connections but only hava handle to one of them because you myConn is set twice once by the first thread and then again by the second thread.
You need to make your app thread safe, you will always have issues if you run this code and if you run this for extended periods of time the oracle server will reset a connection which you do not have a handle to, this will caues a socket reset by peer exception and crash your JVM which in turn means that you're Orion appserver will stop.
It's a pretty common problem I've come across best thing to do is to have a DBManager to handle all your access to db resources, it will connect run a query get the results populate a java object and return that java object. The DBManager can implement a singleton pattern and synchronize the methods or have static methods with all db resources being variable local to the method rather than private variables.

Just remember two rules when accessing the database
1. Never have the variables with scope other than local unless you're entire class is thread safe
2. ALWAYS have a finally block and close all your db resources in there

Here's a bit of sample code that illustrates the above points:
//start code
public void getData() {
  Connection conn = null;
  Statement stmt = null;
  ResultSet rs = null;
  try {
    InitialContext ctx = new InitialContext();
    DataSource ds = (DataSource) ctx.lookup("jdbc/ccfopDS");
    conn = ds.getConnection();
    stmt = conn.createStatement();
    rs=stmt.executeQuery( "SELECT * FROM MACHINE ORDER BY CODE");
    while(rs != null && rs.next()) {
      // do what you want with the data here!!
    }
  } catch (SQLException e) {
  } finally {
    try {
      if(rs != null) rs.close();
      if(stmt != null) stmt.close();
      if(conn != null) conn.close();
    } catch (SQLException e) {
    }
  }
}
// end code
The code might look lengthly especially all the try and catch blocks but it's vital that you always do this! It might be especially messy if you have it in every bean that's why I suggest you have a DBManager or maybe some kind of factory to create an object populate it with data from the database and return that object.
0
 

Author Comment

by:keithedward
ID: 6346445
I don't understand what setting private does to multi-threading... maybe "static" would have some effect.... but "private"???
0
 
LVL 3

Expert Comment

by:black
ID: 6347133
The same object is shared by different threads, therefore your variable which has class scope because it's declared private will keep changing. Have it as only local scope and make the method thread safe by synchronizing it and you'll be fine.
Static is even worse because every instance of that class will be sharing it, you're problem will become significantly worse if you make the variable static.
With private variable an instance of each private variable exist for each instance of a class, with static variables only once instance of the variable exists for all instances of the class.
0
 

Author Comment

by:keithedward
ID: 6348343
I guess the part I don't understand is when you say "The same object is shared by different threads".... I thought each thread got it's own instance of the object (class)?
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
basic hardware to learn oop advanced design patterns 3 74
JDeveloper 12c for 32 bit 4 35
WEB Farm 6 25
mockito example issue 8 35
For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
The viewer will learn how to implement Singleton Design Pattern in Java.

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now