How to rollback multiple prepared statements

[marc balum]

Hi Everyone,

I have to insert records to multiple tables at one go or in one batch. For example, my tables are: A, B, C

I have to use the prepared statement for executing an insert statement which in this case, there will be one prepared statement for each table.

The requirement is if one table fails to insert a record, the rest of the tables should also NOT insert records.

1) How should I accomplish the above requirement using prepared statements?
2) How can I rollback multiple prepared statements?

Help me asap please..

Thank you.

[David O'Meara]

If they are all within a single transaction the rollback will rollback all operations.

[marc balum]

I tried to put some invalid values to the fields of table C to make it fail to insert a record, but still, the records for tables A & B were inserted.

[David O'Meara]

"marc",
Please check your private messages.
-DOM

[David O'Meara]

I think we are going to need to see some code...

[tapeshwar sharma]

Have you taken care that Auto-commit is set to false? Sometimes we tend to forget the little things you know.
Even after we set it to false, there are scenarios that complete rollback can fail in.
For example, let's say there's a exception and we don't rollback the transaction in the exception handler.

[marc balum]

Hi,

Here is my code:

private void saveRecords(ArrayList sqls, ArrayList sqlParams) throws SQLException, Exception {
Connection con = (Connection)getDBConnection();

try {
con.setAutoCommit(false);
if (sqls != null && sqlParams != null && sqls.size() > 0 && sqls.size() == sqlParams.size()) {
for (int i=0; i<sqls.size(); i++) {
PreparedStatement stmt = con.prepareStatement(sqls.get(i).toString());
ArrayList recordsParams = (ArrayList) sqlParams.get(i);
if (recordsParams != null && recordsParams.size() > 0) {
for (int j=0; j<recordsParams.size(); j++) {
List recordParams = (ArrayList) recordsParams.get(j);
if (recordParams != null && recordParams.size() > 0) {
for (int k=0; k<recordParams.size(); k++) {
stmt.setString(k + 1, String.valueOf(recordParams.get(k)));
}
stmt.addBatch();
}
}
updateCounts = stmt.executeBatch();
}
}
}
con.commit();
}
catch (SQLException sqle) {
sqle.printStackTrace();
con.rollback();
}
catch (Exception e) {
e.printStackTrace();
con.rollback();
}
}

[added code tags - Dave]
[ September 27, 2008: Message edited by: David O'Meara ]

[tapeshwar sharma]

Hi,
my 2 cents:
1) Kindly check the DB and the Driver that you are using. It could be that the driver does not support Batch updates.
2) You may want to change the exception handling to include BatchUpdateException.

[marc balum]

Hi,

I'm using MySQL ODBC 3.51 Driver.

I tried using createStatement() instead of PreparedStatement, and it was working properly. The sql statements are executed as one transaction when I used the createStatement. But the requirement is I need to use the PreparedStatement to execute a group of sql statements as one transaction. This is because PreparedStatements are safe from sql injection attacks.

Please let me know how to go around with this.

Your help is very much appreciated.

[Paul Clapham]

Originally posted by marc balum:
I'm using MySQL ODBC 3.51 Driver.

ODBC??? Why ODBC, when MySQL comes with a perfectly good JDBC driver? I would try switching to the JDBC driver.

[arulk pillai]

This is because PreparedStatements are safe from sql injection attacks.

Also, they perform better. I would change driver to a JDBC driver.

[marc balum]

I am already using MySQL JDBC driver now. It's the mysql-connector-java-5.1.6 I'm testing now for the preparedStatements above. But, it is still not working as it should be. The preparedStatements are still not committed as a single transaction. They are committed sequentially wherein when one sql statement fails, the sql statements before it are not rolled back. Are there specific database properties to be set for the driver to be able to treat a group of sql statements as a single transaction?