File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes How to rollback multiple prepared statements Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to rollback multiple prepared statements" Watch "How to rollback multiple prepared statements" New topic
Author

How to rollback multiple prepared statements

marc balum
Greenhorn

Joined: Sep 25, 2008
Posts: 7
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
Rancher

Joined: Mar 06, 2001
Posts: 13459

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

Joined: Sep 25, 2008
Posts: 7
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
Rancher

Joined: Mar 06, 2001
Posts: 13459

"marc",
Please check your private messages.
-DOM
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

I think we are going to need to see some code...
tapeshwar sharma
Ranch Hand

Joined: Mar 10, 2006
Posts: 245
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
Greenhorn

Joined: Sep 25, 2008
Posts: 7
Hi,

Here is my code:


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

Joined: Mar 10, 2006
Posts: 245
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
Greenhorn

Joined: Sep 25, 2008
Posts: 7
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
Bartender

Joined: Oct 14, 2005
Posts: 18992
    
    8

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
Author
Ranch Hand

Joined: May 31, 2007
Posts: 3275
This is because PreparedStatements are safe from sql injection attacks.



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


500+ Java Interview Questions and Answers | Java job hunting know how & Java resumes
marc balum
Greenhorn

Joined: Sep 25, 2008
Posts: 7
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?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to rollback multiple prepared statements