• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1350
  • Last Modified:

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!
0
mmerlock
Asked:
mmerlock
  • 4
  • 3
1 Solution
 
mikoshaCommented:
Try to use exception handling in oracle stored procedure :

 procedure your_sp(...) is
 
 begin

   .............
    .............
     .............
   
 
   commit;


  exception
    when others then
            rollback;
  end your_sp;
0
 
mmerlockAuthor Commented:
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?
0
 
mikoshaCommented:
Sorry , my mistake...
But may be this will help:
http://support.microsoft.com/kb/187289/EN-US/
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
mmerlockAuthor Commented:
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!
0
 
mikoshaCommented:
Ok, lets try to figure it out :)

Have you tried to exclude commit statement from your package procedure (fd_sql_lib.p_add_activity) ?
Because SAVEPOINT works with uncommitted statements in an Oracle session.

If this will not be helpful ,could you , please, post your current code where SAVEPONT is used?  

0
 
mmerlockAuthor Commented:
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. :)
0
 
mikoshaCommented:
:)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now