HOWTO: Implement Nested Transactions with Oracle

ID: Q187289

The information in this article applies to:

Microsoft Visual Basic Enterprise Edition for Windows, versions 5.0, 6.0
Microsoft ODBC for Oracle (Build 2.73.7283.03), version 2.0
Microsoft ODBC for Oracle (Build 2.573.2927), version 2.5
ActiveX Data Objects (ADO), versions 1.5, 2.0, 2.1 SP2
Microsoft ODBC for Oracle (Build 2.573.4202), version 2.5

SUMMARY

ADO and ODBC do not support nested transactions. However, native Oracle SQL supports the SAVEPOINT keyword that can be used to simulate nested transactions.

MORE INFORMATION

The Microsoft Knowledge Base article Q177138, entitled "INFO: Nested Transactions Not Available in ODBC/OLE DB/ADO" says this about nested transactions:

"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 using the SAVEPOINT keyword, you can simulate Nested Transactions. For more information about native ODBC or ADO support for Nested Transactions, please see the article mentioned above.

The SAVEPOINT keyword basically sets a bookmark for uncommitted statements in an Oracle session. You can rollback these statements by using the TO option with the ROLLBACK statement. This all has to be done through Execute statements (such as in the form of <connection>.Execute) because the ODBC parser 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 the second two. You will notice that the whole set of statements is surrounded with a CONNECTION level BeginTrans and CommitTrans. This is necessary so that, at the ODBC API level, the SQLSetConnectOption SQL_AUTOCOMMIT is set to SQL_AUTOCOMMIT_OFF. After you have finished your transaction it is a good idea to re-set SQL_AUTOCOMMIT to SQL_AUTOCOMMIT_ON (the default) by executing either a CommitTrans or a RollbackTrans. Because you have either committed or rolled backed your transactions with your Execute statements, it doesn't matter whether you call CommitTrans or RollbackTrans; either way, they have nothing to commit or rollback. You are just calling these functions 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), please see the following Microsoft Knowledge Base article:

Q175018 : HOWTO: Acquire and Install the Microsoft Oracle ODBC Driver

For more information about nested transactions with ODBC and see the following Microsoft Knowledge Base article:

Q177138 : INFO: Nested Transactions Not Available in ODBC/OLE DB/ADO

For information on using ADO with Oracle, please see the following Microsoft Knowledge Base article:

Q176936 : INFO: Visual Basic 5.0 Accessing an Oracle Database Using ADO

Additional query words: kbADO150 kbOracle200 kbDSupport kbdse kbVBp500 kbVBp600 kbOracle kbODBC kbDriver kbDatabase kbADO

Keywords : kbDatabase kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2 kbMDAC210SP2
Version : WINDOWS:1.5,2.0,2.1 SP2,2.5,5.0,6.0
Platform : WINDOWS
Issue type : kbhowto