HOWTO: Performing Transactions with SQL Server 6.x

• Microsoft Visual C++, 32-bit Editions, versions 2.0, 2.1, 2.2, 4.0, 4.1, 4.2, 5.0

SUMMARY

This article discusses topics related to performing transactions with the Microsoft SQL Server 6.0 and 6.5 using the MFC ODBC classes.

Please refer to the following documentation for more information on MFC database transactions prior to this article:

   In the Microsoft Knowledge Base:
   ARTICLE-ID: Q128208
   TITLE     : How to Perform Transactions with the MFC Database Classes

   MFC Tech Note #47 - "Relaxing Database Transaction Requirements."

MORE INFORMATION

Below is a list of transaction related values the SQL Server ODBC driver returns via SQLGetInfo().

                             SQL Server ODBC
InfoType                     Driver Version     Return Value

SQL_TXN_CAPABLE                  2.50           SQL_TC_DML*
                                 2.65           SQL_TC_ALL*

SQL_CURSOR_COMMIT_BEHAVIOR       2.50           SQL_CB_CLOSE
                                 2.65           SQL_CB_CLOSE

SQL_CURSOR_ROLLBACK_BEHAVIOR     2.50           SQL_CB_CLOSE
                                 2.65           SQL_CB_CLOSE

              (DML) statements (SELECT, INSERT, UPDATE, DELETE). Data
              Definition Language (DDL) statements encountered in a
              transaction cause an error.

              in any order.

SQL_CB_CLOSE, which is the ANSI-specified default behavior, means that the cursor is closed after a commit or rollback. As a result, an MFC recordset will not have a valid cursor after CommitTrans() or Rollback() is called. No operations should be performed on a recordset prior to calling Requery().

An application may want to preserve cursors across transaction commits and rollbacks. The default behavior can be overridden for the SQL Server ODBC driver by using a driver-specific connection option, SQL_PRESERVE_CURSORS. This option can be set by calling SQLSetConnectOption(), as shown below:

  CMyDatabase db;

  db.Open( NULL, FALSE, FALSE,"ODBC;", FALSE  );  // Select  SQL Server
                                                  // 6.x data source
  // rcode will equal 0 (SQL_SUCCESS) if call succeeds
  int rcode = ::SQLSetConnectOption (db.m_hdbc,1204, 1L );

  // 1204 = SQL_PRESERVE_CURSORS, 1L = SQL_PC_ON
  // values for driver-specific options are given in the driver help file

  // if NOT using MFC 4.2, need to enable transaction support
  // look at Q128208 for more information
  // db.SetTransactions();

  BOOL bCanTrans = db.CanTransact();

  if (bCanTrans)
  {
    CMyRecordset rs(&db);
    BOOL ret = db.BeginTrans();
    rs.Open( CRecordset::dynaset );
    rs.Edit();
    // change some fields
    rs.Update();
    db.Rollback();

    // since the cursor is preserved, we do NOT have
    // to call Requery
    rs.MoveFirst();
  }

   ARTICLE-ID: Q138280
   TITLE     : INF: SQLGetInfo Returned with SQL_PRESERVE_CURSORS

REFERENCES

MFC Technote #47 - "Relaxing Database Transaction Requirements." MFC Technote #68 - "Performing Transactions with the Microsoft Access 7 ODBC Driver"

MFC Encyclopedia articles:

• "Transaction"
• "Transaction: Performing a Transaction in a Recordset"
• "Transaction: How Transactions Affect Updates"

Keywords          : MfcDatabase kbprg kbusage
Version           : WINDOWS NT: 2.0, 2.1, 2.2, 4.0, 4.1, 4.2, 5.0
Platform          : NT WINDOWS
Issue type        : kbhowto