Then just replace the "catch (SQLException e)" with "catch (Exception e)". However, the rollback shouldn't even be needed because you would never commit if there was an exception; it would be rolled back automatically when the Connection was closed.
David --- James Blashill <[EMAIL PROTECTED]> wrote: > The reason I did not do things the way you describe is because I wanted > to make sure I rollback in the case of run time exceptions as well. > Other then that, I believe the two examples would behave the same. > > -----Original Message----- > From: David Graham [mailto:[EMAIL PROTECTED] > Sent: Wednesday, June 23, 2004 3:35 PM > To: Jakarta Commons Users List > Subject: Re: [DbUtils] MySQL rollback problem.. > > First, your code would be simpler if you did this: > > Connection conn = dataSource.getConnection(); conn.setAutoCommit(false); > QueryRunner run = new QueryRunner(); try { > run.update(conn, sqlStatement1); > run.update(conn, sqlStatement2); > run.update(conn, sqlStatement3); > conn.commit(); > } catch (SQLException e) { > DBUtils.rollback(conn); > } finally { > DBUtils.close(conn); > } > > If you look at the code for QueryRunner.update() you will see it doesn't > call commit() or rollback() so it's completely up to your code and the > driver. I don't use MySQL so I can't comment on any tricks for that > database. > > David > > > --- James Blashill <[EMAIL PROTECTED]> wrote: > > Hi, > > > > I noticed a problem recently where autoCommit set to false did not > > work as I intended. Here is my code snippet: > > > > Connection connection = dataSource.getConnection(); > > connection.setAutoCommit(false); > > boolean success = false; > > > > QueryRunner queryRunner = new QueryRunner(); > > try > > { > > queryRunner.update(connection, sqlStatement1); > > queryRunner.update(connection, sqlStatement2); > > queryRunner.update(connection, sqlStatement3); > > success = true; > > } > > finally > > { > > if (connection != null) > > { > > if (success) > > { > > connection.commit(); > > } > > else > > { > > connection.rollback(); > > } > > connection.close(); > > } > > } > > > > What I expected from this code was that all three sql statements would > > > be performed as a transaction - that is, if one fails then the other > > will no go through either. However, I have observed that even then > > connection.rollback() get called in the event of an error. The updates > > > that may have already succeeded do NOT get undone. In fact, they > > appeared in the database regardless of whether connection.commit() was > > > called. I have done similar things using Postgres 7.4, but now it does > > > not seem to work with mySQL. Has anyone else experienced this? I am > > using MySQL 4. > > > > Thanks for any input you may have, > > > > James > > > > FYI, I am using DBCP to do my connection pooling as well. > > > > > > __________________________________ > Do you Yahoo!? > Yahoo! Mail - 50x more storage than other providers! > http://promotions.yahoo.com/new_mail > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > __________________________________ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
