| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| JDBC unit-of-work/commit question |
« View previous topic :: View next topic » |
| Author |
Message
|
| PaulMraz |
 Posted: Thu Nov 29, 2001 8:40 am Post subject: |
 |
|
Novice
Joined: 27 Aug 2001
Posts: 12
|
Looks like I need to use JDBC to explicitly commit my JDBC inserts/updates to our Oracle database tables, for my Java/MQI application (on Windows/NT server, base Java/MQI, no threads, nothing fancy).
I noticed that on a fatal exception, I'm getting the message rolled back to the queue (SYNCPOINT option on and using MQI commit and backout), but any database tables updated through JDBC are NOT being rolled back. I'm just now reading how auto-commit is the default for JDBC, but can be turned off and explicit commit/rollback can be issued... IMPORTANT QUESTION: Does the commit have to be issued in the SAME JDBC database connection, or can the unit of work SPAN SEVERAL JDBC connections?
Reason being, the application was written in multiple classes (one for each major table insert/update), each with its own connection, prepared statements, executeupdate, and close connection. What we NEED to do is have several of these span a unit of work, finally doing a commit (or rollback) when ALL of the inserting/updating is complete.
Anyone know if this is possible, that an explicit commit (after auto-commit has been disabled) can follow several connect/execute update/close sessions?
Thanks,
Paul |
|
| Back to top |
|
 |
| PaulMraz |
| Posted: Thu Nov 29, 2001 1:36 pm Post subject: |
|
|
Novice
Joined: 27 Aug 2001
Posts: 12
|
Addendum:
Is it possible for the above scenario to work, if I do not explicitly close the JDBC database connection between the inserts/updates to the different tables?
In other words, will the connection stay open if I do not close it between invocations of each class that updates a separate table? I notice that each class which updates a separate Oracle table performs a JDBC connection, but does not perform a close to that connection...
Could one connection be performed, then disable the auto-commit, then update one table; then have subsequent update classes not close the connection nor perform an open before/after those JDBC updates; then do an explicit JDBC commit (or rollback), and all updates will have remained in the same unit of work?
It seems this could work; I'm just concerned about the JDBC connection being IMPLICITLY closed unwantedly, upon or after the prepared update statement is closed in any given update class, or upon exit from the class that contained that code.
Could anyone shed any light on this?
Thanks,
Paul
[ This Message was edited by: PaulMraz on 2001-11-29 13:41 ]
[ This Message was edited by: PaulMraz on 2001-11-29 13:44 ] |
|
| Back to top |
|
|
| middlewareonline |
| Posted: Fri Nov 30, 2001 7:21 am Post subject: |
|
|
Acolyte
Joined: 09 Jul 2001
Posts: 73
|
One UOW can NOT span multiple connections/sessions.
MiddlewareOnline.COM
_________________
---------------------------------------------
IBM & SUN (J2EE) Certified Consultants,
http://www.MiddlewareOnline.com
A "SARVAM" Online Portal
http://www.SARVAM.com
--------------------------------------------- |
|
| Back to top |
|
|
| PaulMraz |
| Posted: Mon Dec 03, 2001 1:31 am Post subject: |
|
|
Novice
Joined: 27 Aug 2001
Posts: 12
|
Hmmm... if a UOW cannot span several connection sessions, would it likely work if I were to put the JDBC connection command into the main module, but have all the called (static) methods (which are in separate classes) perform the actual prepared statement commands (i.e., INSERT, UPDATE, etc.)?
I guess here I'm asking if the connection will span calls to static methods in other classes, and retain the unit of work, if the session connection command were put in the main calling method?
(all the examples I've seen show the connect command, disable of autocommit, the prepared statement execute, the commit, and rollback within the same method; it's not intuitively obvious to me if the session connect and commit/rollback can be in the calling method, while putting the prepared statement execute logic in methods which are in other classes)
Thanks,
Paul |
|
| Back to top |
|
|
| middlewareonline |
| Posted: Mon Dec 03, 2001 10:52 am Post subject: |
|
|
Acolyte
Joined: 09 Jul 2001
Posts: 73
|
As long as you pass along the same connection handle (session) you can implement prepare in as many functions/classes as you like and do the UOW.
Rajesh
MiddlewareOnline.COM
_________________
---------------------------------------------
IBM & SUN (J2EE) Certified Consultants,
http://www.MiddlewareOnline.com
A "SARVAM" Online Portal
http://www.SARVAM.com
--------------------------------------------- |
|
| Back to top |
|
|
| PaulMraz |
| Posted: Tue Dec 04, 2001 11:39 am Post subject: |
|
|
Novice
Joined: 27 Aug 2001
Posts: 12
|
OK... I've put the connect, commit/rollback, and connection close into the main calling module, and have taken the connect and close out of the individual called class methods.
(and set AutoCommit to false after connecting, in the calling module).
The changes all compiled fine, but... now it's telling me "Logical handle no longer valid" when I get to the called modules to attempt to executeUpdate of the JDBC prepared statements. Not sure why I'm getting this error; I declared the connection as public static in the calling module, and referenced that connection object by the calling class's name in the called method.
Anyone that could help out with advice here?
Thanks,
Paul |
|
| Back to top |
|
|
| PaulMraz |
| Posted: Tue Dec 04, 2001 4:04 pm Post subject: |
|
|
Novice
Joined: 27 Aug 2001
Posts: 12
|
After further review... I found the problem to the "Logical handle no longer valid".
Although I was careful to not open or close the connection other than in the main calling module, several of the called modules had a getNextKey call in them. They did not reference the existing connection, but apparently were the culprit making the existing connection handle invalid. I now do all the required getNextKeys first, before commencing the unit of work connection. (This may result in some keys being reserved then going unused, but I'm not seeing any alternative...)
Now, on to testing that the JDBC rollback actually will work!
- Paul |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
