|
midrange.com
Hi Ron,
I don't have the free time for a full debug ( my paying clients and
stomach would be most unhappy, ) but from a quick scan, it doesn't appear
that the connection is *ever* closed. I see some ResultSet and Statement
closes ( and I assume all the xxxLock objects are some sort of Statement. )
The only line to do with connection close was this:
//March 24, 2006 Removed code for create new connection - Ron Power
//conn.close();
So I don't see ( from the available code ) how *any* connection *ever* gets
closed. And that was only in your whoLocked() method.
> So I guess the best thing for me to do then is to use the try catch
> finally and put the close connection in the finally in case of any errors?
Yep. Known in some circles as TCFTC. There are examples in the source
code for my JDBC tutorial at
http://java.sun.com/developer/onlineTraining/Database/JDBC20Intro/
You can write a wrapper to help ease the pain, similar to what I did with
SQLException reporting, but closing is essential, and particularly so in a
pooling situation.
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: Friday, April 28, 2006 7:39 AM
Subject: Re: Connection Pooling
> So I guess the best thing for me to do then is to use the try catch
> finally and put the close connection in the finally in case of any errors?
> Here's the RecordLock code to just for verification:
>
> /*
> * Created on Mar 18, 2005
> *
> * To change the template for this generated file go to
> * Window>Preferences>Java>Code Generation>Code and Comments
> */
> package com.csj.bulkgarbage.util;
>
> import java.sql.Connection;
> import java.sql.PreparedStatement;
> import java.sql.ResultSet;
> import java.sql.SQLException;
> import java.util.*;
> import java.text.*;
>
> import javax.sql.DataSource;
>
> /**
> * @author RPower
> *
> * To change the template for this generated type comment go to
> * Window>Preferences>Java>Code Generation>Code and Comments
> */
> public class RecordLock {
>
> /**
> * @param ds
> * @param conn
> * @param table
> * @param fieldname
> * @param lockfield
> * @param recordKey
> * @param lib
> * @param userid
> * @return
> */
> //check if record is locked, if not, lock it for current user.
> public synchronized static boolean isLocked(
> DataSource ds,
> Connection conn,
> String lib,
> String table,
> String fieldname,
> String recordKey,
> String lockfield,
> String timefield,
> String userid) {
> boolean result = false;
> PreparedStatement checkLock = null;
> ResultSet rs = null;
> try {
> java.sql.DatabaseMetaData dmd =
> conn.getMetaData();
> String query =
> "SELECT "
> + lockfield
> + " FROM "
> + lib
> + dmd.getCatalogSeparator()
> + table
> + " WHERE "
> + fieldname
> + "='"
> + recordKey.trim()
> + "' FOR UPDATE OF "
> + lockfield.trim()
> + ", "
> + timefield.trim();
>
> checkLock = conn.prepareStatement(query);
> rs = checkLock.executeQuery();
> rs.next();
> String cursorName = rs.getCursorName();
> String checkField = rs.getString(lockfield);
> //field is not blank, so it is in use and not by
> the current user
> if (!checkField.trim().equals("")
> && !checkField.trim().equals(userid)) {
> result = true;
> } else {
> result = false;
> query =
> "UPDATE "
> + lib
> +
> dmd.getCatalogSeparator()
> + table
> + " SET "
> + lockfield.trim()
> + " = ?,"
> + timefield.trim()
> + " = ? WHERE CURRENT OF "
> + cursorName;
> PreparedStatement getLock =
> conn.prepareStatement(query);
> getLock.setString(1, userid);
> Date today = new Date();
> SimpleDateFormat formatter = new
> SimpleDateFormat("yyyyMMdd");
> int callRecDate =
> Integer.parseInt(formatter.format(today));
> formatter = new
> SimpleDateFormat("yyyy/MM/dd");
> String callRecDateString =
> formatter.format(today);
> formatter = new SimpleDateFormat("h:mm
> a");
> String callRecTime = (String)
> formatter.format(today);
> getLock.setString(
> 2,
> callRecDateString.trim() + " " +
> callRecTime.trim());
> getLock.executeUpdate();
> if (getLock != null){
> getLock.close();
> getLock = null;
> }
> }
> conn.commit();
> if (checkLock != null){
> checkLock.close();
> checkLock = null;
> }
> if (rs != null){
> rs.close();
> rs = null;
> }
> } catch (SQLException e) {
> result = true;
> e.printStackTrace();
> } finally {
> if (checkLock != null){
> try { checkLock.close(); } catch
> (SQLException e) { ; }
> checkLock = null;
> }
> if (rs != null){
> try { rs.close(); } catch (SQLException e)
> { ; }
> rs = null;
> }
> }
> return result;
> }
>
> /**
> * @param ds
> * @param conn
> * @param table
> * @param fieldname
> * @param lockfield
> * @param recordKey
> * @param lib
> * @param userid
> * @return
> */
> //unlock the record that was in use by the user.
> public synchronized static boolean unlock(
> DataSource ds,
> Connection conn,
> String lib,
> String table,
> String fieldname,
> String recordKey,
> String lockfield,
> String timefield,
> String userid,
> boolean forceUnlock) {
> boolean result = false;
> PreparedStatement checkLock = null;
> ResultSet rs = null;
> try {
> java.sql.DatabaseMetaData dmd =
> conn.getMetaData();
> String query =
> "SELECT "
> + lockfield
> + " FROM "
> + lib
> + dmd.getCatalogSeparator()
> + table
> + " WHERE "
> + fieldname
> + "='"
> + recordKey.trim()
> + "' FOR UPDATE OF "
> + lockfield.trim()
> + ", "
> + timefield.trim();
>
> checkLock = conn.prepareStatement(query);
> rs = checkLock.executeQuery();
> if (rs.next()) {
> String cursorName = rs.getCursorName();
> String checkField =
> rs.getString(lockfield);
> if (!checkField.trim().equals(userid) &&
> !forceUnlock) {
> result = false;
> } else {
> //set field to blanks to unlock
> record
> result = true;
> query =
> "UPDATE "
> + lib
> +
> dmd.getCatalogSeparator()
> + table
> + " SET "
> + lockfield.trim()
> + " = ?,"
> + timefield.trim()
> + " = ? WHERE
> CURRENT OF "
> + cursorName;
> PreparedStatement resetLock =
> conn.prepareStatement(query);
> resetLock.setString(1, "");
> resetLock.setString(2, "");
> resetLock.executeUpdate();
> if (resetLock != null){
> resetLock.close();
> resetLock = null;
> }
> }
> }
> conn.commit();
> if (checkLock != null){
> checkLock.close();
> checkLock = null;
> }
> if (rs != null){
> rs.close();
> rs = null;
> }
> } catch (SQLException e) {
> result = false;
> System.out.println("Error with " + lockfield);
> e.printStackTrace();
> } finally {
> if (checkLock != null){
> try { checkLock.close(); } catch
> (SQLException e) { ; }
> checkLock = null;
> }
> if (rs != null){
> try { rs.close(); } catch (SQLException e)
> { ; }
> rs = null;
> }
> }
> return result;
> }
>
> /**
> * @param ds
> * @param conn
> * @param table
> * @param fieldname
> * @param lockfield
> * @param recordKey
> * @param lib
> * @param userid
> * @return
> */
> //returns user name of user who locked the record
> public synchronized static String whoLocked(
> DataSource ds,
> Connection conn,
> String lib,
> String table,
> String fieldname,
> String recordKey,
> String lockfield,
> String timefield,
> String userid) {
> String result = "";
> PreparedStatement checkLock = null;
> ResultSet rs = null;
> try {
> //March 24, 2006 Removed code for create new
> connection - Ron Power
> //Connection conn = ds.getConnection();
> java.sql.DatabaseMetaData dmd =
> conn.getMetaData();
> String query =
> "SELECT "
> + lockfield
> + ","
> + timefield
> + " FROM "
> + lib
> + dmd.getCatalogSeparator()
> + table
> + " WHERE "
> + fieldname
> + "='"
> + recordKey.trim()
> + "'";
>
> checkLock = conn.prepareStatement(query);
> rs = checkLock.executeQuery();
> rs.next();
> result = rs.getString(lockfield) + " " +
> rs.getString(timefield);
> if (checkLock != null){
> checkLock.close();
> checkLock = null;
> }
> if (rs != null){
> rs.close();
> rs = null;
> }
> //March 24, 2006 Removed code for create new
> connection - Ron Power
> //conn.close();
> } catch (SQLException e) {
> result = "";
> e.printStackTrace();
> } finally {
> if (checkLock != null){
> try { checkLock.close(); } catch
> (SQLException e) { ; }
> checkLock = null;
> }
> if (rs != null){
> try { rs.close(); } catch (SQLException e)
> { ; }
> rs = null;
> }
> }
> return result;
> }
> }
>
> 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/
>
___________________________________________________________________________
> Success is going from failure to failure without a loss of enthusiasm. -
> Sir Winston Churchill
>
>
>
>
> "Joe Sam Shirah" <jshirah@xxxxxxxxxxxxx>
> Sent by: java400-l-bounces@xxxxxxxxxxxx
> 2006/04/28 02:00 AM
> Please respond to
> Java Programming on and around the iSeries / AS400
> <java400-l@xxxxxxxxxxxx>
>
>
> To
> "Java Programming on and around the iSeries / AS400"
> <java400-l@xxxxxxxxxxxx>
> cc
>
> Subject
> 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>Preferences>Java>Code Generation>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>Preferences>Java>Code Generation>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/
> >
>
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
