Re: Connection Pooling

    Hi Ron,

    Thread safety for the connection itself, as indicated by Dan, is handled
by ( and you are dependedent on ) the connection pool.  You are handling
thread safety for the reference by your use of a local variable.

    I expect most of your problems would go away if you ensure that the
connection is closed when you are done with it.  A defensive programmer will
also close ResultSets and Statements, although closing the connection
*should* close these as well.  You also want to make sure that happens in
all circumstances.  Otherwise you risk starving the pool, and gc will not
help you here.  BTW, "close" is overridden by connection pools to just
return the connection to the available set.  I don't see anything in your
code that explicitly closes the connection.

    If it happens that RecordLock is supposed to do the close for you, then
you may have a thread safety issue, since it appears to use a static method.
We, of course, can't see the code from here.  Even if that works, as it
stands, if you get an exception, the connection is not closed.

    Last, you can always have a valid case where the connection gets closed
for any number of reasons ( what about system shutdown, for an extreme
example, ) and you should always be prepared.  The pool can't help you while
you have the connection.


                                                         Joe Sam

Joe Sam Shirah -        http://www.conceptgo.com
conceptGO       -        Consulting/Development/Outsourcing
Java Filter Forum:       http://www.ibm.com/developerworks/java/
Just the JDBC FAQs: http://www.jguru.com/faq/JDBC
Going International?    http://www.jguru.com/faq/I18N
Que Java400?            http://www.jguru.com/faq/Java400


----- Original Message ----- 
From: <RPower@xxxxxxxxxx>
To: "Java Programming on and around the iSeries / AS400"
<java400-l@xxxxxxxxxxxx>
Sent: Thursday, April 27, 2006 2:41 PM
Subject: Connection Pooling


> Question.
> I have the following servlet:
> /*
>  * Created on Mar 18, 2005
>  *
>  * To change the template for this generated file go to
>  * Window&gt;Preferences&gt;Java&gt;Code Generation&gt;Code and Comments
>  */
> package com.csj.bulkgarbage;
>
> import java.io.IOException;
> import java.sql.*;
> import java.util.*;
> import javax.naming.InitialContext;
> import javax.servlet.*;
> import javax.servlet.http.*;
> import javax.sql.DataSource;
>
> import com.csj.bulkgarbage.util.*;
>
> /**
>  * @author RPower
>  *
>  * To change the template for this generated type comment go to
>  * Window&gt;Preferences&gt;Java&gt;Code Generation&gt;Code and Comments
>  */
> public class CancelGarbage extends HttpServlet {
>         /************************************
>         * Put in the parameters and variables
>         * for datasource.
>         *
>         * @author RPower Jun 2, 2005
>         ************************************/
>         private static DataSource ds = null;
>         private static String collectionName;
>         private static String garbschedmstTable;
>         private String dsname;
>         private DatabaseMetaData dmd;
>         private ServletContext context;
>         private String jdbc_source;
>
>         public void doGet(HttpServletRequest req, HttpServletResponse
> resp)
>                 throws ServletException, IOException {
>                 performTask(req, resp);
>         }
>
>         public void doPost(HttpServletRequest req, HttpServletResponse
> resp)
>                 throws ServletException, IOException {
>                 performTask(req, resp);
>         }
>
>         private void getDS() {
>                 try {
>                         Hashtable parms = new Hashtable();
>                         parms.put(
>                                 "java.naming.factory.initial",
>  "com.ibm.websphere.naming.WsnInitialContextFactory");
>                         InitialContext cxt = new InitialContext(parms);
>                         ds = (DataSource) cxt.lookup("jdbc/" + dsname);
>                 } catch (Exception e) {
>                         e.printStackTrace();
>                 }
>         }
>
>         public void init(ServletConfig config) throws ServletException {
>                 super.init(config);
>                 try {
>                         context = getServletContext();
>                         garbschedmstTable =
>  context.getInitParameter("bulk.garbmastertable");
>                         collectionName =
> context.getInitParameter("pubwrk.lib");
>                         dsname = context.getInitParameter("ds.name");
>                 } catch (Exception e) {
>                         System.err.println(e);
>                 }
>         }
>
>         public void performTask(HttpServletRequest req,
> HttpServletResponse resp) {
>                 HttpSession sess;
>                 Connection conn = null;
>                 Customer customer;
>                 String type;
>                 String date;
>                 String counter;
>                 String url;
>                 try {
>                         sess = req.getSession(true);
>                         //!---New code put in Jun 2, 2005
>                         if (ds == null)
>                                 getDS();
>                         conn = ds.getConnection();
>                         dmd = conn.getMetaData();
>                         // -----End New Code------->
>                         BulkGarbageSchedMaster b =
> (BulkGarbageSchedMaster) sess.getAttribute("bBulkGarbage");
>                         customer = (Customer)
> sess.getAttribute("customer");
>                         RecordLock.unlock(ds, conn, collectionName,
> garbschedmstTable, "WGSCHDID", b.getGarbageId(), "WGINUSBY", "WGINUSTM",
> req.getRemoteUser(), false);
>                         req.setAttribute("newCustomer", "no");
>                         sess.setAttribute("customer", customer);
>                         sess.setAttribute("disabled", "disabled");
>                         if (req.getParameter("counter")!=null){
>                                 counter = req.getParameter("counter");
>                                 date = req.getParameter("date");
>                                 type = req.getParameter("type");
>                                 url = "Edit_Pickupdate_Apps?type=" + type
> + "&date=" + date + "&counter=" + counter;
>                         } else {
>                                 url = "LoadCustomer?" +
> customer.getCustId();
>                         }
>  getServletConfig().getServletContext().getRequestDispatcher(
>                                 url).forward(
>                                 req,
>                                 resp);
>                 } catch (Exception e) {
>                         e.printStackTrace();
>                 }
>         }
> }
>
> When it's being run on the server, sometimes I get the error that the
> connection is closed exception.  Basically, I think that the threads are
> modifying each other as they are running.  How do I make the connection
> thread safe?
>
> Ron Power
> Programmer
> Information Services
> City Of St. John's, NL
> P.O. Box 908
> St. John's, NL
> A1C 5M2
> 709-576-8132
> rpower@xxxxxxxxxx
> http://www.stjohns.ca/
>