asked on

ASP - How to RollBack Oracle Transaction in VBScript

I have a FOR loop on an ASP page that iterates over a list of files submitted and inserts some of the file detail (file name, etc.) using a stored produced into an Oracle table and then copies the file over to a file server.

If an error occurs during the oracle transaction or duing the file copy/delete, I forward to an error page but most importantly I want to rollback all of those Oracle table inserts. I'm not too familar with ASP but can I use the RollbackTrans and CommitTrans method when dealing with an Oracle table that are normally used with SQL databases?

I have a code snippet below and any help would be apprecited.

Thanks!

For Each File in Upload.Files
      Set Fdcmd = Server.CreateObject("ADODB.Command")
      Set Fdparm = server.CreateObject("adodb.Parameter")
      Set Fdrs = Server.CreateObject("ADODB.Recordset")
      Fdcmd.ActiveConnection = Fddb
      Fdcmd.CommandText = "fd_sql_lib.p_add_activity"
      Fdcmd.CommandTimeout = 120
      Set Fdparm = Fdcmd.CreateParameter("userid", adVarChar, adParamInput, 100)
      Fdparm.Value = Upload.Form("userid")
      Fdcmd.Parameters.Append Fdparm
.......................................
......................................
      Fdcmd.CommandType = adCmdStoredProc
      Set Fdrs = Fdcmd.Execute()
      IF Err.number <> 0 then
'ROLLBACK TRANSACTIONS - HELP!
            sessionLogWarning Err.description & sessionErrorUserData()
            if Fdrs.State = adStateOpen then
                  Fdrs.Close
            end if
            Set Fdrs = Nothing
            Set Fdparm = nothing
            Set Fdcmd = Nothing
            if Fddb.State = adStateOpen then
                  Fddb.Close
            end if
            Set Fddb = Nothing
            sessionErrorRedirect "An unexpected error has occurred.", "", ""
END IF
Next

'AFTER FOR LOOP COMMIT ALL ORACLE TRANSACTIONS AS NO ERROR OCCURED - HELP!

mikosha

Try to use exception handling in oracle stored procedure :

procedure your_sp(...) is

begin

.............
.............
.............

commit;

exception
when others then
rollback;
end your_sp;

mmerlock

ASKER

The insert stored procedure is excuted each interation of the FOR loop and when an error occurs all of the inserts need to be rolled back, not just the very last. So I was thinking I could commit the transactions outside of the FOR loop once I knew there were no errors. But if there was an error in the FOR loop, I'd roll back all of the executed transactions. Is this possible?

mikosha

Sorry , my mistake...
But may be this will help:
http://support.microsoft.com/kb/187289/EN-US/

mmerlock

ASKER

Thanks for the link, Mikosha. I checked out that page and included the SAVEPOINT code on my page but it seems like my transactions get auto commited. When I rollback to my save point I get this error: [Oracle][ODBC][Ora]ORA-01086: savepoint 'BeginTrans' never established" and my the record is inserted into to the DB.

Thanks for your help!

ASKER CERTIFIED SOLUTION

mikosha

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mmerlock

ASKER

Hi Mikosha, I looked at the procedure and finally found a commit in another procedure that it calls so I'm sure you're right that's what's causing the rollback to fail.

Thanks for all your help - appreciate it. :)

mikosha