Hi!

I have a requirement where I need to persist data by calling stored procedures fromm Session beans. Assuming that the stored procedures themselves don't do any transaction management, can UserTransaction(from EJBContext) be used to control commit/rollback of updates done by the stored procedures called as a part of one transaction?

As a side note, the stored procedure will be used only for persisting data (and for complex selects). The business logic still resides in POJO called from Session Beans.

I see this as a better performing and scalable design in comparison to using Entity beans or using Prepared Statements. Any suggestions/comments/critique?

Thanks,
Ratmeh

I don't think, there is any way to expend UserTransaction from java component to stored procedure.

But why do you need java transaction if you are using stored procedure? You can put (Or call) all the related logic in SP and you can handle all the gochaa with in SP only.

I won't recommend you to call one SP based on another's output if you are looking for rollback possibilities. It can be done but won't look good. Choice is yours :)


Hope, It will help.

Amit
"Be who you are and say how you feel. Because those who matter don't mind, and those who mind don't matter."

Thanks for your reply. You're absolutely right in saying that the TXN handling should be the responsibility of SP.
However, the scenarion is that I need to make calls to different SPs and therefore want to control Commit/Rollback from my Session Bean method. Is that possible?

Thx.
Ratmeh

George,

I really don't see why you/Amit expect any problems with the use of UserTransaction. After all, it's only a replacement for CMT. Both of them will have to send a commit/rollback to the DB. Did you actually try it? From my experience it will (and must) work. Secondary, I strongly disagree with "TXN handling should be the responsibility of SP". The opposite is true, almost always :)

Best regards,
Stefan

I haven't been able to do so unfortunately. I have DB2 which requires Visual C++ to compile procedures. Will have to install Personal Oracle I guess...

Anyways if you have used it and it worked for you, I will take your words on it :). Any thing special needs to be considered (in the SP, SessionBean, container etc) for using it? Or not.

Thanks

I haven't been able to do so unfortunately. I have DB2 which requires Visual C++ to compile procedures. Thanks

Really? Hard to believe, but I've close to no experience with DB2, so I won't comment on that. I've used UserTransaction with MS SQL Server 2000 and Oracle 8.1.7. EJB container was BEA WebLogic in both cases.

Anyways if you have used it and it worked for you, I will take your words on it :).

Don't do that, never! Try to understand yourself and always write some testing prototype :)
BTW, why do you want to use UserTransaction at all? In my case it was simply misguided design that I couldn't influence/change anymore. Could as well have used CMT with SPs ...

Any thing special needs to be considered (in the SP, SessionBean, container etc) for using it? Or not.

No, not something that I could think of the top off my head.
Think about it: you are talking to a RDBMS that *NEEDS* to be told when a tx starts and when it finishes. Nothing special about a container doing it automatically on your behalf or you doing it manually - the DB doesn't know/care about it.

Kind regards,
Stefan

After all, it's only a replacement for CMT. Both of them will have to send a commit/rollback to the DB. Did you actually try it? From my experience it will (and must) work. Secondary, I strongly disagree with "TXN handling should be the responsibility of SP". The opposite is true, almost always :)

SPs are not the replacement of CMT. Even they are not comparable. Anyways,

Ya!! You are right both have to send only commit/rollback signals but how will you handle SQL exception and will relate SQL events with User transactions.

It can be done and it sounds like a hack to me. But still, it all depends on the complexity of your use case as well.

Regards
Amit

SPs are not the replacement of CMT. Even they are not comparable.

I meant using UserTransaction is a replacement for CMT, *not* SPs. You do it manually, that's all. Also note, that I strongly advised against the use of commit/rollback inside SPs.

... but how will you handle SQL exception ...

In the Java code (caller's side), where's the problem?

... and will relate SQL events with User transactions.

Hhm, not sure what "SQL events" means here.

It can be done and it sounds like a hack to me.

Hack is a bit overstated, but I agree, it isn't "nice". I'd always use CMT wherever I can.

Best regards,
Stefan

Hello Guys, I got a small question for you. If I have two DB calls..one with SP which makes some DB updates/insert and anothe DB call can be either SP or SQL..if the second one fails would it be possible to rollback first transaction from CMT EJB..? i would appreciate you reply on this.