Solved

ASP - How to RollBack Oracle Transaction in VBScript

Posted on 2004-11-02
1,249 Views
Last Modified: 2008-01-09
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
Question by:mmerlock
    7 Comments
     
    LVL 4

    Expert Comment

    by:mikosha
    Try to use exception handling in oracle stored procedure :

     procedure your_sp(...) is
     
     begin

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


      exception
        when others then
                rollback;
      end your_sp;
    0
     

    Author Comment

    by:mmerlock
    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
     
    LVL 4

    Expert Comment

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

    Author Comment

    by:mmerlock
    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
     
    LVL 4

    Accepted Solution

    by:
    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
     

    Author Comment

    by:mmerlock
    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
     
    LVL 4

    Expert Comment

    by:mikosha
    :)
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
    I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
    With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    933 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now