Where are you closing the connection?


-----Original Message-----
From: Mon Cab [mailto:futon33@(protected)]
Sent: Thursday, March 01, 2007 12:23 PM
To: Struts User Group
Subject: Connection, pool exhausted error



I am getting a connection, pool exhausted error, in the action class
below.
The error happens exactly after the hundredth connection, even though I
have closed all my statements, result sets and contexts. (I know that
one would not normally do db access in an Action class, however, I'm
implementing a quick and dirty presence detection for an external
service, and dont need to worry about elegance.)



[http-8080-Processor23] FATAL webapp.WEBCHAT_MessengerPresenceAction -
org.apache.commons.dbcp.SQLNestedException: Cannot get a connection,
pool exhausted
2007-02-28 22:07:35,146


package webapp;


import webapp.utils.*;
import javax.servlet.http.*;
import org.apache.commons.logging.*;
import org.apache.struts.action.*;
import org.apache.struts.validator.*;
import org.apache.struts.util.*;

import javax.naming.*;
import java.sql.*;
import javax.sql.*;
import java.util.*;



public class WEBCHAT_MessengerPresenceAction extends Action
{


 private static Log log =
LogFactory.getLog(WEBCHAT_MessengerPresenceAction.class);


 public ActionForward execute(  ActionMapping mapping,
 
ActionForm form,
 
HttpServletRequest req,
 
HttpServletResponse res
                )
 
throws Exception
 {

   DynaActionForm dynaForm = (DynaActionForm)form;
   MessageResources messageResources = getResources(req);

   Context initContext =null;
   Statement stmt =null;
   ResultSet rs =null;


   try
   {

     User user = null;

     String expired_sessions_sql

       =  "DELETE FROM
userplane_pending_wm WHERE "+
         "date_add(insertedAt, INTERVAL
15 MINUTE) < Now() "+
         "AND date_add(openedWindowAt,
INTERVAL 5 MINUTE) < Now();";

     log.debug("expired_sessions_sql = "+
expired_sessions_sql);

     initContext = new InitialContext();
     Context envContext =
(Context)initContext.lookup("java:/comp/env");
     DataSource ds =
(DataSource)envContext.lookup("jdbc/webdb");
     Connection con = ds.getConnection();

     stmt = con.createStatement();
     stmt.executeUpdate(expired_sessions_sql);

     boolean  bFoundPendingWMs = false;

     if (user!=null)
     {
       String presence_sql

         =  "UPDATE user SET
lastTimeOnline = NOW() "+
           "WHERE user_id = "+
user.user_id+";";

       log.debug("presence_sql = "+
presence_sql);

       stmt.executeUpdate(presence_sql);


       String pending_chats_sql

         =  "SELECT
originatingUserID FROM userplane_pending_wm "+
           "WHERE destinationUserID
= " + user.user_id + " "+
           "AND ( openedWindowAt IS
NULL "+
           "OR
date_add(openedWindowAt, INTERVAL 5 MINUTE) < Now() );";

       log.debug("pending_chats_sql = "+
pending_chats_sql);

       rs =
stmt.executeQuery(pending_chats_sql);

       if (rs.next())
       {
         rs.close();
         return
mapping.findForward("pixle1Redirect");
       }

       else
       {
         rs.close();
         return
mapping.findForward("pixle2Redirect");
       }
     }


     return null;

   }

   catch (Exception e)
   {
     log.fatal(e);
     return null;
   }

   finally
   {
     log.debug("in finally ");

      if( rs != null ) try { rs.close();
log.debug("rs closed ");} catch(
SQLException ignore ) {  log.debug("rs not closed ");}
      if( stmt != null ) try { stmt.close();
log.debug("stmt closed "); }
catch( SQLException ignore ) {  log.debug("stmt not closed ");}
      if( initContext != null ) try {
initContext.close();
log.debug("ctxt closed ");} catch( Exception ignore ) {  log.debug("ctxt
not closed ");}
   }


 }


}






________________________________________________________________________
____________
Need Mail bonding?
Go to the Yahoo! Mail Q&A for great tips from Yahoo! Answers users.
http://answers.yahoo.com/dir/?link=list&sid=396546091

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@(protected)
For additional commands, e-mail: user-help@(protected)


This message contains information that may be privileged or confidential and is the property of the Capgemini Group. It is intended only for the person to whom it is addressed. If you are not the intended recipient, you are not authorized to read, print, retain, copy, disseminate, distribute, or use this message or any part thereof. If you receive this message in error, please notify the sender immediately and delete all copies of this message.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@(protected)
For additional commands, e-mail: user-help@(protected)