• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

How to rollback multiple prepared statements

 
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I think we are going to need to see some code...
 
Ranch Hand
Posts: 245
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Marshal
Posts: 28177
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Author
Posts: 3473
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
reply
    Bookmark Topic Watch Topic
  • New Topic