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 inserting data of two forms into two tables  with same transaction id Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "inserting data of two forms into two tables  with same transaction id" Watch "inserting data of two forms into two tables  with same transaction id" New topic
Author

inserting data of two forms into two tables with same transaction id

rammie singh
Ranch Hand

Joined: Mar 26, 2009
Posts: 116
i have two forms and i am fetching the data from those two forms. i want to store the data of these two forms in two different
tables.

the problem is i want to maintain a common column taht is tranaaction_id , in both tables. also if i want this transaction_id to be generated dynamically i.e. my databse should generate it.


also both form data need to be inserted together. that is either both are inserted or none are inserted.

i am using MYSQL database.

can any one please suggest me the solution.

Thanks and regards.
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38363
    
  23
How about a 3rd table for transaction_id with an auto_increment (in MySQL try "serial") column in as a primary key, then the other two tables can refer to this table as a foreign key??
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Sounds like you need a transaction log table that stores these ids and has a relationship with both tables. Then if you wrap your insert into a transaction (making sure you are using one of the database engines that support transactions) you should get the behaviour you need.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Swastik Dey
Rancher

Joined: Jan 08, 2009
Posts: 1454
    
    6

This might be a solution, let me know if I am going wrong



Swastik
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38363
    
  23
Would you add setAutocommit(true) to the catch??
Swastik Dey
Rancher

Joined: Jan 08, 2009
Posts: 1454
    
    6

Probably yes, may be for the other transactions.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: inserting data of two forms into two tables with same transaction id