Microsoft KB Archive/168708: Difference between revisions
m (Text replacement - """ to """) |
m (Text replacement - "&" to "&") |
||
Line 116: | Line 116: | ||
if (bCanTrans) | if (bCanTrans) | ||
{ | { | ||
CMyRecordset rs(& | CMyRecordset rs(&db); | ||
BOOL ret = db.BeginTrans(); | BOOL ret = db.BeginTrans(); | ||
rs.Open( CRecordset::dynaset ); | rs.Open( CRecordset::dynaset ); |
Latest revision as of 12:29, 21 July 2020
Article ID: 168708
Article Last Modified on 7/1/2004
APPLIES TO
- Microsoft Visual C++ 2.0 Professional Edition
- Microsoft Visual C++ 2.1
- Microsoft Visual C++ 2.2
- Microsoft Visual C++ 4.0 Standard Edition
- Microsoft Visual C++ 4.1 Subscription
- Microsoft Visual C++ 4.2 Enterprise Edition
- Microsoft Visual C++ 5.0 Enterprise Edition
- Microsoft Visual C++ 6.0 Enterprise Edition
- Microsoft Visual C++ 4.2 Professional Edition
- Microsoft Visual C++ 5.0 Professional Edition
- Microsoft Visual C++ 6.0 Professional Edition
- Microsoft Visual C++ 6.0 Standard Edition
This article was previously published under Q168708
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:
128208 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 *SQL_TC_DML = Transactions can only contain Data Manipulation Language (DML) statements (SELECT, INSERT, UPDATE, DELETE). Data Definition Language (DDL) statements encountered in a transaction cause an error. *SQL_TC_ALL = Transactions can contain DDL statements and DML statements 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(); }
NOTE: SQL_PRESERVE_CURSORS works only for server-side cursors. As a result, even after setting this connection option, the ODBC driver will return SQL_CB_CLOSE when queried for SQL_CURSOR_COMMIT_BEHAVIOR. For additional information, please see the following article in the Microsoft Knowledge Base:
138280 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"
Additional query words: kbVC200 kbVC210 kbVC220 kbVC410 kbVC420 kbVC500 kbVC600
Keywords: kbhowto kbdatabase kbprogramming kbusage KB168708