Hi,
I working on j2ee project. I have a requirement like this.
I need to insert a data into one master and one detail table. There will one header record and multiple detail records. I should insert successfully in both the table. If any insert fails I should rollback both the inserts. How can I achieve it. Should I use user transaction or entity bean or stored procedure. IF it is a stored procedure, I can have only one database call and can manage rollback and commit in procedure itself but the drawback is I can not insert variable number of detail records, I will have to use limited number of detail records ( i.e. 20 etc. as I have oracle 7.3.4 database).
Another option is user transaction, but I will have to make one database call per insert i.e. one for header and one each for detail line. I really want minimum number of database calls as our application server is 700 miles away from database server.
Third suggestion is entity bean, I don’t know much about it but ready to explore.

P lease suggest me best possible solution.
Thanks
Pramod

First off, forget the Entity bean. Given the complexity of your problem, it will only make things worse.

Second, do some quick performance tests. Write a "dummy" store procedure that does 10 inserts to the detail table, and 1 insert to the header table, and write some JDBC code that does the same thing remotely in a transaction. Run both 1000 times, and determine the difference in performance.

If the difference is large, go with the stored proc, and give up some flexiblity. If the difference is small, go with the JBDC inserts.

My guess is that if there is more than 2 or 3 network hops to get from your app server to your database, a stored proc will be the best way to go.

I can't see any problem necessarily with any of the strategies you have suggested.

Ultimately I feel it boils down to how comfortable you feel about each technology and go with your instincts, ultimately you're the one that's going to have to maintain this, yes ?

Ian Purton
Tune up your web site stats with IndexTools.com

Try to use JDBC batch updates. This allows you to make several updates in one network call.

I appriciate everyone's input.