Does Websphere have a mechanism for validating connections, much like
Tomcat's DataSource? We simply supply a validation query that either
gets executed *every* time a DataSource is provided to a client -OR-
after a certain timeout (idle time).
This helped us when our iSeries was bounced regularly Sunday mornings.
Brice
On Mon, 14 Feb 2005 14:16:16 -0600, [EMAIL PROTECTED]
<[EMAIL PROTECTED]> 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
> [EMAIL PROTECTED]
>
>
