VistaDB 6
VistaDB / Developer's Guide / How To Perform Common Tasks / Handle Transactions in SQL / Number of open transactions
In This Topic
    Number of open transactions
    In This Topic

    VistaDB only supports a single open transaction (no nested transactions are supported). It is therefore very useful to be able to determine how many open transactions are present prior to creating one, or in an error handling loop.

    Open Transactions in DDA

    Given a VistaDB Database like this( IVistaDBDatabase db ) you can query the NestedTransactionLevel property to determine how many transactions are open.

    Open Transaction Count in DDA
    Copy Code
            
       if( db.NestedTransactionLevel > 0 )
       {
          db.RollbackTransaction();
       } 
    

    Open Transactions in SQL

    In a SQL Proc you may want to check for transactions using the @@TRANCOUNT system variable.

    The query will return 0 for none, or a positive number (only 1 is valid at this time). If no database is open or if the database is in an invalid state -1 will be returned to represent an error condition.

    Open Transaction Count in SQL
    Copy Code
    SELECT @@TRANCOUNT; 
    

    A stored procedure capable of handling an exception in a TSQL Proc that contains transactions should also check the TRANCOUNT and rollback if it is found to be greater than 0.

    SQL Stored Procedure Example
    Copy Code
    BEGIN TRY
         { sql_statement | statement_block }
    END TRY
    BEGIN CATCH
    -- Do we have a transaction to rollback?
    IF @@TRANCOUNT > 0
    BEGIN
              ROLLBACK TRANSACTION;
    END
    END CATCH 
    
    See Also