Solved

How to work with client side transactions in VB.net for DB2 DSN

Posted on 2007-11-15
3
685 Views
Last Modified: 2008-02-07
I have an application in VB.net that does inserts into three different tables. Insert into table 1 is done by DB2 stored procedure1. Insert into table 2 DEPENDS on the success of the insert into table 1 and is achieved by a DB stored procedure 2. Insert into table 3 depends on success of inserts into table2 [hence success of insert into table1] and is done with a DB2 stored procedure 3.

Now, when i am at the insert into table3 [assuming that inserts 1 and 2 have completed successfully] and the insert FAILS, I want to ROLLBACK the inserts into table 2 and inserts into table 1. Now, I cannot change the stored procedure because this is a 3 tier development architecture where the database objects are shielded from the "view" layer [this is not a WEB APP]. I have three methods that do these inserts.

I have heard of this "transaction scope" object in .net but I do not see a "rollback" option in there. So, my question is how would I go about coding the "rollback" transaction option to this scenario? More than the commit, I need the rollback.

App: Vb.net, database:DB2 on Z/os[mainframe]
0
Comment
Question by:VBSolutionSeeker
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 45

Accepted Solution

by:
Kent Olsen earned 200 total points
ID: 20289866
Hi VBSolutionSeeker,

A transaction is a logical unit of work.  (Silly words right out of the 'manual'.)  Support for running in a transaction environment varies with the different clients and applications.

But I know of no way to maintain a transaction across multiple connections.  If your application connects, runs procedure1 and disconnects, then reconnects to run procedure2, the updates performed by procedure1 are already committed.  You can not roll them back.

Also, if the procedure does an explicit commit, you're in the same situation.  DB2 doesn't care where the commit is initiated.  A commit is a commit.

All that said, what you probably want is middleware that will maintain a connection to DB2 and allow your VB code to interact with it.  It's not necessarily cheap, and it will require a significant amount of recoding on your end.


Good Luck,
Kent
0
 
LVL 5

Assisted Solution

by:ocgstyles
ocgstyles earned 200 total points
ID: 20292259
Hi VBSolutionSeeker,

I agree with Kdo.

But if there's the slight chance you can combine the 3 stored procedures into 1, then you could just make it an atomic transaction.  When atomic, its all or none.  If SP2 failed, so does 1, if SP3 fails, so does 1 and 2.

But like you said, that's not an option...

Keith
0
 
LVL 27

Assisted Solution

by:tliotta
tliotta earned 100 total points
ID: 20295605
Minor question... does "cannot change the stored procedure" also mean that a new stored proc _cannot_ be created?

Tom
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question