aspose file tools*
The moose likes JDBC and the fly likes record insertion Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "record insertion" Watch "record insertion" New topic
Author

record insertion

rudresh kumar
Ranch Hand

Joined: Jan 04, 2006
Posts: 83
HI All,

I am trying to insert records of one application in different tables, like Main details, address, payment, product...in each respective tables.

i am doing like this in my code
if(main details success)
insert address
if(address success)
insert payment
....like this

problem is if any of the table is not inserted then i am not able to roll back,

can some one help me in solving this, an example or reference would be good

thanks
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 39436
    
  28
Sounds as if you need a stored procedure, and a transaction. You will need to read any standard databasees book,

Other members may be better able to help than me, but it goes something like this:-

See whether that helps. To call the procedure, you say "CALL multiple_insert('Rudresh Kumar', '123 High St', 123.45);"

You need to check carefully depending which database you are using; the syntax seems to vary slightly; you might say "execute" instead of "call," or "begin transaction" instead of "start."

References? The Java Tutorial includes stored procedures and transactions.
I haven't found any other good stored procedure tutorials; this is probably the least bad.
Philip Greenspun explains why you need ACID transactions here, and MySqL have a tutorial here.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2503
    
    8

I would say: transaction: yes, stored procedure: no.

The transaction mechanism will take care that all inserts are applied, or none.

Stored procedures will not help you with that. They are a mechanism to run code inside the database, on the database server.
I would not advise to commit inside a stored procedure. It might surprise java developers who are trying to rollback. They think that they rolled back to were they started the transaction, but they are only rolling back to after the stored procedure (Applying the Rule of Least Surprise).

Regards, Jan
[ May 12, 2007: Message edited by: Jan Cumps ]

OCUP UML fundamental and ITIL foundation
youtube channel
Herman Schelti
Ranch Hand

Joined: Jul 17, 2006
Posts: 387
hi Rudresh,

If you use only one Connection object you can do a rollback on all tables by:
1. conn.setAutoCommit(false); //default-value = "true"
2. conn.rollback() after failure, conn.commit() after succes.

Herman
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 39436
    
  28
Thank you, Jan Cumps. I shall remember that.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: record insertion