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)