Proper exception handling of JDBC code? Need to call connection.rollback()?

[Bomb Diggy <te...@yahoo.com>]

One of the many points I raised earlier was how to
properly close a database connection, and how that
might change depending on whether you were using
connection pooling and/or a DataSource.  The example
JDBC code cited was that in the Struts 1.0.2 Javadoc:

http://jakarta.apache.org/struts/doc-1.0.2/api/org/apache/struts/util/package-summary.html#doc.JDBC

The part that looked a little funny to me and others
was the call to [myConnection].rollback() in the
catch{} block.  I think I finally figured out why this
was put in - defensive programming.

It seems to me that if *any* part of a transaction is
successful, then [connection].rollback() needs to be
called, else the next piece of application code that
got its hands on that connection object and completed
a transaction successfully would also complete the
previously-uncommitted transaction.  

For example, let's say your updateDB() method has two
separate method calls made within it that each update
a table.  If only the first conn.executeUpdate()
method completes successfully, and the second
conn.executeUpdate() throws some kind of SQLException,
that transaction will *not* automatically be rolled
back.  The driver won't do it.  The database would do
it only when the connection is finally closed.  

But what if that connection is part of a connection
pool, and before the application server (and thus the
pool) goes down for the night, that updateDB() method
is used again, this time with success.  Now, the
leftover transaction from the first failed call to
updateDB() has been committed along w/ any changes
from follow-on calls to updateDB().  All of this
happens, if your DataSource/Connection Pooling
Mechanism/Wrapped Connection Object isn't smart enough
to call [connection].rollback() for you.  The
GenericConnection object that ships with Struts is
smart enough - and that's why I probably hadn't been
burned in the past while using Struts, and not calling
conn.rollback() myself.

The GenericConnection object's first task in its
close() method is to call connection.rollback().  So,
as long as I call connection.close() before leaving my
updateDB() method, I'm good to go.

/*  Code snippet from Struts source */
...
// Clean up any outstanding transaction as best we can
try {
  conn.rollback();
} catch (SQLException e) {}
...

Hope all this is correct...don't want to mislead
people...


__________________________________________________
Do you Yahoo!?
New DSL Internet Access from SBC & Yahoo!
http://sbc.yahoo.com

--
To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>