Problems with ManagedConnection - Leaking Memory

[cariaco99]

Hello there.
I'm an ASP developer fairly new to Java, and I'm having a problem with the application I'm writing. Every time I run the app, it seems that I'm not closing something or I'm using the resources out of synch, and after 10 or 15 refreshes, I get a dreaded:
No ManagedConnections available within configured blocking timeout ( 30000 [ms] ) ....
I've looked everywhere and found some obscures comments but no solutions.
I tried using Try/Finally blocks but I'm not sure if I'm using it right...
PLEASE PLEASE HELP!!!!

This is a piece of the code:
<%@ page import = "javax.naming.*"%>
<%@ page import = "java.io.*"%>
<%@ page import = "java.lang.*"%>
<%@ page import = "java.sql.*"%>
<%@ page import = "javax.sql.*"%>

<%
String sMode = "";
String sSiteID = "";
String sSurveyID = "";
String sRoomTemplateType = "";
String sRoomCode = "";
String sAreaID = "";
String sRoomID = "";
String sUniCode = "";
String sRoomName = "";
String sJSP_Action = "";

if (request.getParameter("Mode") != null) {
sMode = request.getParameter("Mode").toString();
}else {
sMode = "0";
}

if (request.getParameter("fld_SiteID") != null) {
sSiteID = request.getParameter("fld_SiteID").toString();
}else {
sSiteID = "0";
}

if (request.getParameter("fld_AreaID") != null) {
sAreaID = request.getParameter("fld_AreaID").toString();
}else {
sAreaID = "0";
}

if (request.getParameter("RoomCode") != null) {
sRoomCode = request.getParameter("RoomCode").toString();
}else {
sRoomCode = "0";
}
if (request.getParameter("UniCode") != null) {
sUniCode = request.getParameter("UniCode").toString();
}else {
sUniCode = "0";
}

if (request.getParameter("fld_RoomID") != null) {
sRoomID = request.getParameter("fld_RoomID").toString();
}else {
sRoomID = "0";
}

if (request.getParameter("txt_RoomName") != null) {
sRoomName = request.getParameter("txt_RoomName").toString();
}else {
sRoomName = "";
}

if (request.getParameter("JSP_ACTION") != null) {
sJSP_Action = request.getParameter("JSP_ACTION").toString();
}else {
sJSP_Action = "";
}

InitialContext ctxt = new InitialContext();
DataSource ds = (DataSource) ctxt.lookup("javaEMO_CARACAS");
Connection con = ds.getConnection();
Connection con1 = ds.getConnection();
Statement stmt = null;
ResultSet rs = null;
String sSQL = "";
int rows = 0;

try {

if (sJSP_Action.equals("Aceptar") || sJSP_Action.equals("Click_Accept")){
if (sMode.equals("ADDROOM")){
sSQL = "INSERT INTO tbl_Rooms (SiteID, AreaID, RoomCode, UniCode, RoomName)";
sSQL = sSQL + " VALUES(" + sSiteID + "," + sAreaID + ",'" + sRoomCode + "','" + sUniCode + "','" + sRoomName + "')";
}else{
sSQL = "UPDATE tbl_Rooms ";
sSQL = sSQL + " SET SiteID=" + sSiteID + ",";
sSQL = sSQL + " AreaID=" + sAreaID + ",";
sSQL = sSQL + " RoomCode='" + sRoomCode + "',";
sSQL = sSQL + " RoomName='" + sRoomName + "'";
sSQL = sSQL + " WHERE ID=" + sRoomID;
}
out.print(sSQL);
stmt = con.createStatement();
rows = stmt.executeUpdate (sSQL);
if(rs !=null) rs.close();
if(stmt !=null) stmt.close();
if(con !=null) con.close();

}else if (sJSP_Action.equals("Borrar") || sJSP_Action.equals("Click_Delete")){
sSQL = "DELETE FROM tbl_Rooms WHERE ID=" + sRoomID;
stmt = con.createStatement();
rows = stmt.executeUpdate (sSQL);
if(rs !=null) rs.close();
if(stmt !=null) stmt.close();
if(con !=null) con.close();

sSQL = "DELETE FROM tbl_Elements WHERE RoomID=" + sRoomID;
stmt = con1.createStatement();
rows = stmt.executeUpdate (sSQL);
if(rs !=null) rs.close();
if(stmt !=null) stmt.close();
if(con1 !=null) con1.close();
}

} finally {
con1.close();
con.close();
}
response.sendRedirect("survey_rooms_list.jsp?SiteID=" + sSiteID + "&AreaID=" + sAreaID);

%>

 

[sedj]

Please don't take this the wrong way ... but that code is badly written and inefficient.
Here is a *slightly better* version.

Please think about the compiler and ultimately the machine code you will execute when writing higher level languages.

BTW, replace all the 'if' statements with the construct 'if/else if/else' ....

<%@ page import = "javax.naming.*"%>
<%@ page import = "java.io.*"%>
<%@ page import = "java.lang.*"%>
<%@ page import = "java.sql.*"%>
<%@ page import = "javax.sql.*"%>

<%
    String sMode                 = "0";
    String sSiteID                 = "0";
    String sSurveyID             = "0";
    String sRoomTemplateType     = "0";
    String sRoomCode             = "0";
    String sAreaID                 = "0";
    String sRoomID                 = "0";
    String sUniCode             = "0";
    String sRoomName             = "0";
    String sJSP_Action            = "0";
    
    if (request.getParameter("Mode") != null) {
        sMode    = request.getParameter("Mode").toString();
    }
  
    if (request.getParameter("fld_SiteID") != null) {
        sSiteID    = request.getParameter("fld_SiteID").toString();
    }
  
    if (request.getParameter("fld_AreaID") != null) {
        sAreaID    = request.getParameter("fld_AreaID").toString();
    }
  
    if (request.getParameter("RoomCode") != null) {
        sRoomCode    = request.getParameter("RoomCode").toString();
    }    
    if (request.getParameter("UniCode") != null) {
        sUniCode    = request.getParameter("UniCode").toString();
   }
    
    if (request.getParameter("fld_RoomID") != null) {
        sRoomID    = request.getParameter("fld_RoomID").toString();
    }    

    if (request.getParameter("txt_RoomName") != null) {
        sRoomName    = request.getParameter("txt_RoomName").toString();
    }    

    if (request.getParameter("JSP_ACTION") != null) {
        sJSP_Action    = request.getParameter("JSP_ACTION").toString();
    }

    InitialContext ctxt = new InitialContext();
    DataSource     ds         = (DataSource) ctxt.lookup("java:DEMO_CARACAS");
    Connection     con     = ds.getConnection();
    Statement     stmt     = null; 
    ResultSet     rs         = null; 
    String         sSQL     = "";
    int         rows    = 0;
    
     try {
     
	if (sJSP_Action.equals("Aceptar") || sJSP_Action.equals("Click_Accept")){
		if (sMode.equals("ADDROOM")){
			sSQL = "INSERT INTO tbl_Rooms (SiteID, AreaID, RoomCode, UniCode, RoomName)";
			sSQL = sSQL + " VALUES(" + sSiteID + "," + sAreaID + ",'" + sRoomCode + "','" + sUniCode + "','" + sRoomName + "')";
		} else {
			sSQL = "UPDATE tbl_Rooms ";
			sSQL = sSQL + " SET SiteID="     + sSiteID         + ",";
			sSQL = sSQL + " AreaID="         + sAreaID         + ",";
			sSQL = sSQL + " RoomCode='"        + sRoomCode        + "',";
			sSQL = sSQL + " RoomName='"     + sRoomName        + "'";
			sSQL = sSQL + " WHERE ID="        + sRoomID;
		}
		out.print(sSQL);
		stmt    = con.createStatement(); 
		rows    = stmt.executeUpdate (sSQL); 
		
	} else if (sJSP_Action.equals("Borrar") || sJSP_Action.equals("Click_Delete")){
		sSQL = "DELETE FROM  tbl_Rooms WHERE ID=" + sRoomID;
		stmt    = con.createStatement(); 
		rows    = stmt.executeUpdate (sSQL); 
		if(con    !=null)     con.close();
	
		sSQL = "DELETE FROM  tbl_Elements WHERE RoomID=" + sRoomID;
		stmt    = con1.createStatement(); 
  rows    = stmt.executeUpdate (sSQL); 
 }
     } catch (SQLexception sql) {
        // handle error here !!!
        . sqle.printStackTrace(System.err);
 
    } finally {
       if(rs    !=null)     rs.close(); 
       if(stmt    !=null)     stmt.close(); 
       con.close();
    }         
    response.sendRedirect("survey_rooms_list.jsp?SiteID=" + sSiteID + "&AreaID=" + sAreaID);    
    
%>

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[cariaco99]

Thanks for your help, sedj.

I don't feel offended at all! I'm totally new at JAVA and I bascially translated code from ASP.

3 things:

1) You say is bad (and I know it!!!) but please, can you tell me why and how can I make it better?

2) Other than the catch, I don't see any difference. What is causing the memory leak? will your code as you wrote it solve it? where?

3) When I have to open 2,3 different tables, should I reuse the con,stmt,rset? What about when I have a loop inside a recordset and I need to open another table inside the loop?

I haven't found anywhere samples other than one EXACTLY the way I did it.

Thanks, man.