• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to rollback multiple prepared statements

 
marc balum
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If they are all within a single transaction the rollback will rollback all operations.
 
marc balum
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"marc",
Please check your private messages.
-DOM
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think we are going to need to see some code...
 
tapeshwar sharma
Ranch Hand
Posts: 245
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

Here is my code:


[added code tags - Dave]
[ September 27, 2008: Message edited by: David O'Meara ]
 
tapeshwar sharma
Ranch Hand
Posts: 245
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 21416
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3388
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is because PreparedStatements are safe from sql injection attacks.



Also, they perform better. I would change driver to a JDBC driver.
 
marc balum
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic