How To Implement Nested Transactions with Oracle

This article was previously published under Q187289
SUMMARY
ADO and ODBC do not support nested transactions. However, native Oracle SQLsupports the SAVEPOINT keyword that can be used to simulate nestedtransactions.
MORE INFORMATION
The Microsoft Knowledge Base article 177138, entitled "INFO: NestedTransactions Not Available in ODBC/OLE DB/ADO" says this about nestedtransactions:
"Neither Open Database Connectivity (ODBC, nor any released Microsoft OLE DB Provider supports Nested Transactions. ActiveX Data Objects (ADO) supports the feature, but only if the underlying provider exposes it. Currently none of Microsoft's OLE DB providers support Nested Transactions."

This is true for the Microsoft ODBC for Oracle driver. However, by usingthe SAVEPOINT keyword, you can simulate Nested Transactions. For moreinformation about native ODBC or ADO support for Nested Transactions,please see the article mentioned above.

The SAVEPOINT keyword basically sets a bookmark for uncommitted statementsin an Oracle session. You can rollback these statements by using the TOoption with the ROLLBACK statement. This all has to be done through Executestatements (such as in the form of <connection>.Execute) because the ODBCparser cannot parse the SAVEPOINT keyword properly.

The following code shows how this all works:
    Conn = "UID=****;PWD=****;DRIVER={Microsoft ODBC for Oracle};" _            & "SERVER=SamOracle;"    Set Cn = New ADODB.Connection    With Cn        .ConnectionString = Conn        .CursorLocation = adUseClient        .Open    End With    Cn.BeginTrans    Cn.Execute "SAVEPOINT ALPHA"    Cn.Execute "INSERT INTO trantest VALUES(1,10)"    Cn.Execute "INSERT INTO trantest VALUES(2,10)"    Cn.Execute "SAVEPOINT BETA"    Cn.Execute "INSERT INTO trantest VALUES(3,10)"    Cn.Execute "INSERT INTO trantest VALUES(4,10)"    Cn.Execute "ROLLBACK TO SAVEPOINT BETA"    Cn.Execute "COMMIT"    Cn.RollbackTrans				
NOTE: This assumes a table "Trantest" exists on the Oracle server.

This code will commit the first two INSERT statements and rollback thesecond two. You will notice that the whole set of statements is surroundedwith a CONNECTION level BeginTrans and CommitTrans. This is necessary sothat, at the ODBC API level, the SQLSetConnectOption SQL_AUTOCOMMIT is setto SQL_AUTOCOMMIT_OFF. After you have finished your transaction it is agood idea to re-set SQL_AUTOCOMMIT to SQL_AUTOCOMMIT_ON (the default) byexecuting either a CommitTrans or a RollbackTrans. Because you have eithercommitted or rolled backed your transactions with your Execute statements,it doesn't matter whether you call CommitTrans or RollbackTrans; eitherway, they have nothing to commit or rollback. You are just calling thesefunctions to reset SQL_AUTOCOMMIT to SQL_AUTOCOMMIT_ON, which they both do.
REFERENCES
For information on how to acquire the Microsoft Data Access Components(MDAC) stack (which includes the Microsoft ODBC for Oracle driver), pleasesee the following Microsoft Knowledge Base article:
175018 How To Acquire and Install the Microsoft Oracle ODBC Driver
For more information about nested transactions with ODBC and see thefollowing Microsoft Knowledge Base article:
177138 INFO: Nested Transactions Not Available in ODBC/OLE DB/ADO
For information on using ADO with Oracle, please see the followingMicrosoft Knowledge Base article:
176936 INFO: Visual Basic 5.0 Accessing an Oracle Database Using ADO
Properties

Article ID: 187289 - Last Review: 07/01/2004 17:44:25 - Revision: 1.2

Microsoft Visual Basic 5.0 Enterprise Edition, Microsoft Visual Basic Enterprise Edition for Windows 6.0, Microsoft Open Database Connectivity 2.0, Microsoft Open Database Connectivity 2.0, Microsoft Open Database Connectivity 2.0, Microsoft Open Database Connectivity 2.5, Microsoft Open Database Connectivity 2.5, Microsoft Open Database Connectivity 2.5, Microsoft Open Database Connectivity 2.0, Microsoft ActiveX Data Objects 1.5, Microsoft ActiveX Data Objects 2.0, Microsoft ActiveX Data Objects 2.1, Microsoft ActiveX Data Objects 2.5, Microsoft ActiveX Data Objects 2.6, Microsoft ActiveX Data Objects 2.7

  • kbdatabase kbdriver kbhowto kboracle KB187289
Feedback