[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

ASP - How to RollBack Oracle Transaction in VBScript

Posted on 2004-11-02
7
Medium Priority
?
1,322 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
Comment
Question by:mmerlock
[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
  • 4
  • 3
7 Comments
 
LVL 4

Expert Comment

by:mikosha
ID: 12477796
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
ID: 12477904
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
ID: 12478034
Sorry , my mistake...
But may be this will help:
http://support.microsoft.com/kb/187289/EN-US/
0
Technology Partners: 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!

 

Author Comment

by:mmerlock
ID: 12487407
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:
mikosha earned 500 total points
ID: 12497325
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
ID: 12498792
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
ID: 12499199
:)
0

Featured Post

Technology Partners: 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

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…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

656 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