Mailing-List: contact ibatis-user-java-help@incubator.apache.org; run by ezmlm
Received-SPF: pass (hermes.apache.org: domain of jerry.jalenak@gmail.com
 designates 64.233.170.204 as permitted sender)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
        s=beta; d=gmail.com;
        h=received:message-id:date:from:reply-to:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:references;
        b=LFk9IhEbw+y3G+UFcS1zxAcQ7xJcC3+bdw/3biRfvohIbkcJHhGyxwnL5tZZLDclrRBAlJQhBZnisKym7npWN/ow/oSZVaY6Ajiw6mxfBsoGNpbmXkQlFobqHKtGco/RkBZbQgoND46+vKhh4Jli20PyydfTpuUau3qDR7nY+nc=
Message-ID: <544feb4905021412352ffc2aa1@mail.gmail.com>
Date: Mon, 14 Feb 2005 14:35:08 -0600
From: Jerry Jalenak <jerry.jalenak@gmail.com>
Reply-To: Jerry Jalenak <jerry.jalenak@gmail.com>
To: ibatis-user-java@incubator.apache.org, cbegin@ibatis.com
Subject: Re: StaleConnectionException
In-Reply-To: <16178eb1050214122956aca489@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
References: 
 <OFECC9DCF9.D70C4F53-ON86256FA8.005CE0BD-86256FA8.006F5A86@courts.mo.gov>
	 <16178eb1050214122956aca489@mail.gmail.com>

Clinton,

How do you *really* feel?

8-)

Jerry


On Mon, 14 Feb 2005 13:29:38 -0700, Clinton Begin
<clinton.begin@gmail.com> wrote:
> This is not a criticism of you, but of WebSphere and that "example"....
> 
> That is the single most ridiculous thing I have ever seend.  That code
> and approach is absolutely stupid.  The entire point behind the
> DataSource interface is to hide code like that and deal with
> infrastructure issues in a single location, so you don't have to
> everywhere.
> 
> That DataSource is flawed.  I suggest you call IBM and tell them this
> exactly.  Tell them to fix their crappy datasource and their crappy
> app server.
> 
> If they refuse (which they probably will if you've already paid them
> their annual fees), then you could always wrap the datasource with
> your own implementation that will deal with this.
> 
> ....phew.  Glad to get that off my chest.  ;-)
> 
> Cheers,
> Clinton
> 
> 
> On Mon, 14 Feb 2005 14:16:16 -0600, Cory.Bestgen@courts.mo.gov
> <Cory.Bestgen@courts.mo.gov> wrote:
> >
> >
> > Hello,
> > I am using websphere 5.1.2, oracle 9i and ibatis 2.0.8. I was using the
> > following
> > example from the jpetstore demo as a template for setting up my ibatis
> > transactions
> > but crazy things happen when the database has gone down and come back up.
> >
> > public Order getOrder(int orderId) {
> >   Order order = null;
> >   try {
> >     daoManager.startTransaction();
> >     order = orderDao.getOrder(orderId);
> >     for (int i = 0; i < order.getLineItems().size(); i++) {
> >       LineItem lineItem = (LineItem) order.getLineItems().get(i);
> >       lineItem.setItem(itemDao.getItem(lineItem.getItemId()));
> >     }
> >     daoManager.commitTransaction();
> >   } finally {
> >     daoManager.endTransaction();
> >   }
> >   return order;
> > }
> >
> > Websphere likes to throw a StaleConnectionException when a connection in
> > the pool has gone
> > bad. At the websphere site ibm shows how to use a construct something
> > similar
> > to the code below that retries if the StaleConnectionException is thrown.
> >
> > // Set retryCount to the number of times you would like to retry after a
> > StaleConnectionException
> > int retryCount = 5;
> > // If the Database code processes successfully, we will set error = false
> > boolean error = true;
> > do {
> >   try {
> >     conn = ds.getConnection();
> >     stmt = conn.createStatement();
> >     String query = "Select FirstNme, MidInit, LastName from Employee ORDER
> > BY LastName";
> >     rs = stmt.executeQuery(query);
> >     while (rs.next()) { employeeList.addElement(rs.getString(3) + ", " +
> > rs.getString(1) + " " + rs.getString(2)); }
> >     //Set error to false to indicate successful completion of the database
> > work
> >     error=false;
> >   } catch (com.ibm.ejs.cm.pool.ConnectionWaitTimeoutException cw) {
> >     // This exception is thrown if a connection can not be obtained from
> > the
> >     // pool within a configurable amount of time.  Frequent occurrences of
> >     // this exception indicate an incorrectly tuned connection pool
> >     System.out.println("Connection Wait Timeout Exception during get
> > connection or process SQL: " + c.getMessage());
> >     //In general, we do not want to retry after this exception, so set
> > retry count to 0
> >     retryCount = 0;
> >   } catch (com.ibm.websphere.ce.cm.StaleConnectionException sc) {
> >     // This exception indicates that the connection to the database is no
> > longer valid.
> >     // Retry several times to attempt to obtain a valid
> >     //connection, display an error message if the connection still can not
> > be obtained.
> >     System.out.println("Stale Connection Exception during get connection or
> > process SQL: " + sc.getMessage());
> >     if (--retryCount == 0) {
> >       System.out.println("Five stale connection exceptions, displaying
> > error page.");
> >     }
> >   }
> > } while ( error==true && retryCount > 0 );
> >
> > And now my question, is there a way to have ibatis automatically retry when
> > it gets a StaleConnectionException?
> > Or should each method implement something like below?
> >
> > public Order getOrder(int orderId) {
> >   Order order = null;
> >   int retryCount = 5;
> >   boolean error = true;
> >   do {
> >     try {
> >       daoManager.startTransaction();
> >       order = orderDao.getOrder(orderId);
> >       for (int i = 0; i < order.getLineItems().size(); i++) {
> >         LineItem lineItem = (LineItem) order.getLineItems().get(i);
> >         lineItem.setItem(itemDao.getItem(lineItem.getItemId()));
> >       }
> >       daoManager.commitTransaction();
> >       error = false;
> >     } catch (com.ibm.ejs.cm.pool.ConnectionWaitTimeoutException cw) {
> >       System.out.println("Connection Wait Timeout Exception during get
> > connection or process SQL: " + c.getMessage());
> >       retryCount = 0;
> >     } catch (com.ibm.websphere.ce.cm.StaleConnectionException sc) {
> >       System.out.println("Stale Connection Exception during get connection
> > or process SQL: " + sc.getMessage());
> >       if (--retryCount == 0) {
> >         System.out.println("Five stale connection exceptions, displaying
> > error page.");
> >       }
> >     } finally {
> >       daoManager.endTransaction();
> >     }
> >   } while ( error==true && retryCount > 0);
> >   return order;
> > }
> >
> > I am new to websphere and haven't seen a topic like this covered on the
> > list yet.
> >
> > thanks
> >
> > Cory Bestgen
> > Computer Information Technologist II
> > Information Technology Division
> > Office of State Courts Administrator
> > (573) 522 - 5455
> > cory.bestgen@courts.mo.gov
> >
> >
>