Rollback/Commit

[opiate]

I’m coding a VB app that will transfer data from one of our legacy system into Oracle8i.

The data will basically have 2 levels.
ACCOUNTS
CONTACTS
The account records being the top level will be selected based on certain criteria anything that is related to this account will be considered a contact.

I coded a loop that selects all the accounts and inserts them. If anything is related to it, it gets inserted in a nested loop.

Example:

Do Until rsa.EOF
Insert account
Insert account address1
Insert account address2
Insert account cross-referenceids


For i =1 to rsc.RecordCount 'the recordset rsc selects all the records that are related to the accountid
Insert contact
Insert contact address1
Insert contact address1
Insert contact referenceid's

rsc.MoveNext

Next

rsa.MoveNext

Loop

My question:
Say an error occurs, I'm left with only half of the contact for that account. My program will not know where it left off when the error occured. What I'm wanting to do is use the Transaction, Rollback, Commit functions/events. If an error occurs, rollback all the records that were just inserted else if successful commit.

Hope this seems logical enough...

Thanks for any help

[antonye]

Rollback and Commit are used to bundle all your database operations up into "transactions" to help avoid this exact error you describe.

The idea is that you flag when your transaction begins, ie you start inserting your customer details, and commit the transaction to the database when everything is finished, or you call roll-back all the changes if there was an error.

Depending on how you want to code your routines, there are a couple of ways of setting up your transaction.

The ADO Connection object has .BeginTrans, .CommitTrans and .RollbackTrans methods, which you can use like this:

adoConn.BeginTrans
adoConn.Execute "INSERT ..."
adoConn.Execute "INSERT ..."
adoConn.Execute "UPDATE ..."
adoConn.CommitTrans

If you get an error, you simply need to execute:

adoConn.RollbackTrans

which will then "undo" everything upto your .BeginTrans point.

The other way to do it is explicitly through the SQL. The ADO methods do nothing more than execute the correct SQL syntax, but obviously it depends on how you want to code it. The SQL way would be:

adoConn.Execute "BEGIN TRANSACTION;"
adoConn.Execute "INSERT ..."
adoConn.Execute "INSERT ..."
adoConn.Execute "UPDATE ..."
adoConn.Execute "COMMIT TRANSACTION;"

Similarly, you can execute the SQL "ROLLBACK TRANSACTION" if you want to undo your changes.

As you can see, there's not much difference between the two except in programming style.

For more info, see the ADO Begin/Commit/Rollback Methods (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthbegintrans.asp) article on MSDN.

HTH.

[opiate]

Thanks bro! One problem, I get a provider error. The reason, you can only have one instance of .BeginTrans. Reading MSDN, .BeginTrans returns a long value.

The reason why I get the error is because I used it in the loop.

Can I use .BeginTrans before the loop? The loop is where all the records are inserted. When "Do Until rs.EOF" is true the loop ends. Once the loop ends, can I put the something like...

This is my code sample...

Sub Sample()
On Error Goto ErrTrans

adoConn.Execute.BeginTrans

Do Until rs.EOF

........adoConn.Execute "insert statements".......

rs.MoveNext
Loop

ErrTrans:

If Err.Number <> 0 then
adoConn.Execute.RollbackTrans
MsgBox "Errors encountered!"
Exit Sub
End
Resume 'is this where i put Resume?

adoConn.Execute.CommitTrans

rs.Close
set rs = nothing

adoConn.Close
set adoConn = Nothing

End Sub


Would this work or do I need to put the error code in the loop?


Nice Impreza! I checked out your site. I almost bought one, a black one. Is yours the WRX model? I found the car not very comfortable. I loved everything else. The way it handles, the 4cyl turbo charged beast of a engine and the AWD.

I setteled for a 1999 Audi A4 Quattro 1.8 T with the sport package. And it's comfortable too. I like it but dosent have much power as the Impreza. Get this, I even got a speeding ticket while test driving the Impreza, $285 bucks for the ticket and an attourney. I've had the Audi it for about a year. I'm ready for a 2001 Audi S4, V6 Twin Turbo, AWD w/sport package

Thanks again for any help...


P.S. Is your girlfriend still alive after wrecking your ride? <IMG SRC="smilies/biggrin.gif" border="0">

[antonye]

Originally posted by opiate:
The reason why I get the error is because I used it in the loop.
...
Can I use .BeginTrans before the loop?


Yes, that's the way you want to do it.

The way of looking at it is to think of at which point it is ok for your database to be updated and commit the transaction then.

Don't forget to always pair up a Begin and Commit, otherwise you get the error. You can do nested transactions (it's actually just a counter on the SQL server) but you have to do some additional stuff concerning "implicit transactions" to get this to work without returning errors.

Nice Impreza! I checked out your site. I almost bought one, a black one. Is yours the WRX model?

No - we didn't get the WRX model in the UK until the new shape cars came out last year as it wasn't an officially imported model through Subaru UK. Many people have managed to get imported WRX models, so there are some about here. The nice thing is that the Japanese have right-hand drive models (like in the UK) so it's easy to import a car without worrying about the steering being on the wrong side <IMG SRC="smilies/smile.gif" border="0">

All the new shape models are WRX ones, but we're also getting a special edition around 300bhp model too.

I bought the wife a Citroen Saxo VTS which I don't think you have in the states. It's a real cool little hatch-back with a 1600cc 16v engine kicking out 120bhp and does 0-60 in just over 7 seconds. It's a little rocket!

I'm more of a motorcycle man, and I'm just trying to find myself a nice second-hand Ducati 748. I would buy a new one but can't get insurance on it due to the price. I could go out and buy two second-hand ones and insure them though, which would cost more to replace than one new one. Work that one out!

[opiate]

Cool, I got it to work...

Thanks again anytonye <IMG SRC="smilies/biggrin.gif" border="0">