Transaction-processing (TP) applications are classified in this book as database applications that update data contained in tables. An update to a table occurs when the application changes the value of data in existing records, adds new records, or deletes records from the database.
You update data with Visual C++ applications that are easily developed using AppWizard. Updating is done with SQL statements that Microsoft calls action queries. Semantically, the term query isn't an appropriate description of an SQL statement that doesn't return rows. For consistency with the Microsoft documentation for Access, this book uses the term action query to include SQL update, append, and make-table queries. You also can use the CDatabase::BeginTrans() and CDatabase::CommitTrans() member functions to make changes to recordset objects.
Online transaction processing (OLTP) is a category of TP in which the updates occur on a real-time basis. The alternative to OLTP is batch processing, in which updates to database tables are accumulated as rows of temporary tables. A separate database application processes the data in the temporary tables. The second application deletes the temporary tables when the batch update process is complete. Batch processing often is used in accounting applications. This chapter deals primarily with OLTP, but most of the techniques you will learn here are also applicable to batch processing methods.
All database applications fall into either the decision-support or transaction-processing class. Decision-support applications need only (and always should be restricted to) read-only access to the database tables. By definition, transaction-processing applications require read-write access to the tables being updated. This section defines some of the terms used in this book, as well as by the industry, to describe transaction-processing applications.
The majority of transaction-processing applications fall into one of these three categories or subclasses:
Another category of transaction-processing application that is becoming more widely used in conjunction with downsizing projects involves distributed databases. Transaction-processing applications that make almost-simultaneous updates to tables in more than one database are called distributed OLTP (DOLTP) applications. Transaction processing in a distributed database environment, where the databases involved are located on different servers that may be geographically dispersed, is one of the subjects of Chapter 19.
Transaction monitors (TMs or OLTMs) are a class of transaction-processing applications that were originally designed to manage very large numbers of simultaneous transactions against mainframe database management systems. TMs are more robust and handle more simultaneous transactions than conventional client-server RDBMSs. IBM's Customer Information Control System (CICS) transaction-monitor application is undoubtedly the most widely used mainframe TM in North America. IBM has released client-server version 2.0.1 of CICS for OS/2. CICS For OS/2 version 2.0.1 lets you use the CICS TM to update data in a three-tiered structure (such as a PC client running OS/2 or Windows, a PC server running IBM's LAN Server application, and an IBM mainframe running CICS-MVS) to distribute transaction-processing applications over a LAN instead of using 3270-style terminals. However, you also can run CICS for OS/2 version 2.0.1 in an IBM LAN Server-only environment in which the server holds the database and the applications reside on the client PCs.
As mentioned earlier, you can use either SQL action queries or Visual C++ code generated using AppWizard in transaction-processing applications. Traditional character-based desktop database applications such as dBASE, Clipper, and FoxPro use GET statements to assign entered data to a variable and REPLACE statements to update a table field with the variable's value. Visual C++ is more flexible than DOS desktop database applications, because it offers the three methods of updating database tables that are discussed in the following sections.
Action queries that involve joined tables, using either the SQL-92 or Access SQL JOIN syntax or the equals operator in the WHERE clause, must operate against updatable recordsets. Although action queries don't return recordsets, the rules for updating records of joined tables apply just as if action queries created "invisible" recordsets (which, in fact, they do). Access provides a visual clue that identifies a query as nonupdatable by omitting the tentative append record (the blank record with an asterisk in the record selector button) as the last record in the datasheet view of a recordset or by omitting an empty record as the last record of a continuous form or subform.
SQL is a set-oriented language. The SELECT queries you write define a set of records (rows) that Visual C++ and the Microsoft Jet database engine or an ODBC driver return to your application as a recordset object. Similarly, SQL action queries define a set of records that are to be updated, appended, or deleted. (Appending a new record to a table creates a set of one new record.) You can create SQL action queries with Visual C++ code or employ parameterized QueryDef objects to specify the set of records to which the action query applies. Using SQL action queries that act on multiple records is similar in concept to the batch-processing method described earlier in this chapter. SQL action queries attempt to update all records that meet the criteria of the SQL statement's WHERE clause.
Chapter 5, "Learning Structured Query Language," briefly discussed the SQL syntax for action queries. The following list describes the Access SQL and ANSI SQL reserved words that you use to create action queries.
When you use the Microsoft Jet database engine and an Access .MDB database with Visual C++, an action query is an "all-or-nothing" proposition—either all the records in the set are updated, appended, or deleted, or the query fails and you receive an error message. When you use the ODBC API and a multiple-tier ODBC driver with a client-server RDBMS, your ANSI SQL statement and the RDBMS are responsible for determining whether the action query can succeed. Some single-tier ODBC drivers—such as Q+E Software's ODBC Driver Pack drivers for dBASE, Paradox, and FoxPro databases—support the three ANSI TPL reserved words. Version 1.0 of the Microsoft ODBC Desktop Database Drivers kit supports TPL only when you use the Access ODBC driver or the Btrieve ODBC driver. Support for TPL should be a major determinant in your choice of desktop database drivers if you're using Visual C++ with dBASE, FoxPro, or Paradox files.
NOTE
Both ANSI and Access SQL statements need the source recordset objects of all queries to be persistent, and the source recordsets must be Table objects. You can't execute an SQL statement with virtual tables as source recordsets. Although you can include user-defined functions (UDFs) in Access's version of Access SQL, UDFs aren't permitted in SQL queries you execute with Visual C++.
You execute action queries by using one of two Visual C++ methods:
The Visual C++ syntax for the preceding SQLExecDirect() and ExecuteSQL() functions appears in Chapter 2, "Understanding MFC's ODBC Database Classes," and Chapter 3, "Using Visual C++ Data Access Functions." The MS Query application lets you execute SQL statements on a database directly. After the SQL statement has been built and tested using MS Query, it can be pasted into a Visual C++ application.
TIP
When you use the ExecuteSQL() method when running action queries against client-server databases, your action query might involve updates to more than one record.
You can use edit boxes or other controls to display data and to update fields of an updatable CRecordset class object that aren't calculated fields. Using edit boxes is the most common (but not necessarily the best) method of updating table data in Visual C++ and Access applications. You also can use third-party grid custom controls with Visual C++ transaction-processing applications. The following sections describe the benefits and drawbacks of using these two types of controls for data entry.
The simplest way to create a data-entry form for transaction processing is to add an edit box to the form for each field of the table or column of the datasource that your CRecordset is accessing. You bind the edit box to the application variable (using ClassWizard).
Bound controls minimize the code you need to write to create data-entry applications. Here are a few of the principal issues involved in using bound edit controls in conjunction with Visual C++ AppWizard-created database applications for transaction processing:
The following sections describe the use of edit boxes in conjunction with an AppWizard-created database application. The methods used here are for the sake of simplicity; production-grade applications use command buttons to commit or cancel edits and to manipulate record-pointers.
If you're converting an Access database application to Visual C++, you often need to emulate the continuous subform controls that are commonly employed in Access applications. There are several ways to do this. This chapter looks at two methods. The first method to emulate an Access continuous subform is to use a dialog box that has individual edit boxes for each displayed record (row) and field. A second method is to use Visual C++ 4's OLE Grid control. A third method (not discussed in this chapter) is to use a ListView control.
NOTE
Both of the sample programs in this chapter use the Access version of the Northwind Traders database, accessed using ODBC with the ODBC name of Northwind Access. If you've installed the Northwind Traders database using a different name, you can modify the source dataset name to reflect the name you used for your Northwind sample database as necessary. This name is found in the GetDefaultConnect function in the CRecordset class implementation, as this example shows:
CString CContinusingGridControlSet::GetDefaultConnect() { return _T("ODBC;DSN=Northwind Access"); }
Figure 15.1 shows an Access continuous subform. Figure 15.2 shows the Visual C++ version of this subform. Figure 15.3 shows a Visual C++ 4 OLE Grid control version of the program shown in Figure 15.2.
Figure 15.1. An Access continuous subform.
In Figure 15.2, the form's Product Name, English Name, Unit Price, Units in stock, and Units on Order are text-box controls. The Visual C++ code you write to fill the control arrays is similar to the C++ code needed to fill in a single control.
Figure 15.2. A Visual C++ form that emulates an Access continuous subform.
In Figure 15.3, all the form's fields are contained in a single Visual C++ 4 OLE Grid control. This OCX control could be supplemented with edit boxes to let the user modify the values displayed. Unlike the examples shown in Figures 15.1 and 15.2, in-place editing isn't supported using an OLE Grid control.
Figure 15.3. A Visual C++ 4 OLE Grid control that emulates an Access continuous subform.
The most important thing to remember about a continuous form-type view is that more than one record from the recordset must be accessed at any one time. In Figure 15.2, this is done by simulation. Ten rows are displayed, and 10 rows in the recordset are accessed sequentially to fill in the 10 rows in the display.
The OLE Grid example in Figure 15.3 displays more than 10 rows. When I created this sample program, it was impossible to determine exactly how many rows would be visible to the user.
NOTE
The OLE Grid control has some limitations. The maximum number of rows is 16,352, and the maximum number of columns is 5,450. It's unlikely that your application will exceed the column limitation, but it's possible to generate a query that can return more than 16,352 rows of data. You must check for this situation and warn the user that not all records returned are displayed.
NOTE
Contin, the sample application shown in Figure 15.2, is located by default in the CHAPTR15 directory on the CD that comes with this book. The project called Contin using Grid Control is also found in this directory.
Listing 15.1 shows the code you need to write in order to fill a simulated continuous subform with data from the table or query specified when the application was created. You need to explicitly handle null values, because the Microsoft versions of text boxes and edit controls don't convert null values to empty strings.
All the necessary changes are made in two files: the view class source file (in this program, CONTINVW.CPP) and the header file for the view class (CONTINVW.H). In Listing 15.1, the code that lets you access multiple rows appears in bold.
Listing 15.1. Code to emulate an Access subform using edit boxes.
// contivw.cpp : implementation of the CContinView class
//
#include "stdafx.h"
#include "contin.h"
#include "contiset.h"
#include "contidoc.h"
#include "contivw.h"
#ifdef _DEBUG
#undef THIS_FILE
static char BASED_CODE THIS_FILE[] = __FILE__;
#endif
/////////////////////////////////////////////////////////////////////////////
// CContinView
IMPLEMENT_DYNCREATE(CContinView, CRecordView)
BEGIN_MESSAGE_MAP(CContinView, CRecordView)
//{{AFX_MSG_MAP(CContinView)
// NOTE: ClassWizard will add and remove mapping macros here.
// DO NOT EDIT what you see in these blocks of generated code!
//}}AFX_MSG_MAP
// Standard printing commands
ON_COMMAND(ID_FILE_PRINT, CRecordView::OnFilePrint)
ON_COMMAND(ID_FILE_PRINT_PREVIEW, CRecordView::OnFilePrintPreview)
END_MESSAGE_MAP()
/////////////////////////////////////////////////////////////////////////////
// CContinView construction/destruction
CContinView::CContinView()
: CRecordView(CContinView::IDD)
{
//{{AFX_DATA_INIT(CContinView)
m_pSet = NULL;
//}}AFX_DATA_INIT
// TODO: Add construction code here
for (int i = 0; i < 10; i++)
{
m_EnglishName[i] = "";
m_ProductName[i] = "";
m_UnitPrice[i] = "";
m_UnitsInStock[i] = 0;
m_UnitsOnOrder[i] = 0;
}
m_ArraySet = FALSE;
}
CContinView::~CContinView()
{
}
BOOL CContinView::OnMove(UINT nIDMoveCommand)
{
int i;
int nStepBack;
//---------START OF DBVIEW.CPP OnMove()...
if (CDatabase::InWaitForDataSource())
{
#ifdef _DEBUG
if (afxTraceFlags & 0x20)
TRACE0("Warning: ignored move request\n");
#endif // _DEBUG
return TRUE;
}
if (m_pSet->CanUpdate())
{
if (!UpdateData())
return TRUE;
nStepBack = 0;
for (i = 0; i < 10; i++)
{// Save if the current record, and then get next one!
m_pSet->Edit();
m_pSet->m_English_Name = m_EnglishName[i];
m_pSet->m_Product_Name = m_ProductName[i];
m_pSet->m_Unit_Price = m_UnitPrice[i];
m_pSet->m_Units_In_Stock = m_UnitsInStock[i];
m_pSet->m_Units_On_Order = m_UnitsOnOrder[i];
m_pSet->Update();
if (!m_pSet->IsEOF())
{
TRY
{// Use old-style exceptions for Visual C++ 1.5x
m_pSet->MoveNext();
--nStepBack;
}
CATCH(CDBException, e)
{// Died. Should use message box to user!
TRACE("MoveNext() fail Ret = %d Error '%s', cause '%s'\n",
e->m_nRetCode,
(const char *)e->m_strError,
(const char *)e->m_strStateNativeOrigin);
}
END_CATCH
}
else
{
break;
}
}
// Restore the record pointer! Take nStepBack giant steps back!
TRY
{// Use old-style exceptions for Visual C++ 1.5x
m_pSet->Move(nStepBack); // Back to original record...
}
CATCH(CDBException, e)
{// Died. Should use message box to user!
TRACE("Move(nStepBack) failed Ret = %d Error '%s', cause '%s'\n",
e->m_nRetCode,
(const char *)e->m_strError,
(const char *)e->m_strStateNativeOrigin);
}
END_CATCH
}
switch (nIDMoveCommand)
{
case ID_RECORD_PREV:
m_pSet->MovePrev();
if (!m_pSet->IsBOF())
break;
case ID_RECORD_FIRST:
m_pSet->MoveFirst();
break;
case ID_RECORD_NEXT:
m_pSet->MoveNext();
if (!m_pSet->IsEOF())
break;
if (!m_pSet->CanScroll())
{
// Clear out screen since we're sitting on EOF
m_pSet->SetFieldNull(NULL);
break;
}
case ID_RECORD_LAST:
m_pSet->MoveLast();
break;
default:
// Unexpected case value
ASSERT(FALSE);
}
//---------END OF DBVIEW.CPP OnMove()...
SetArray();
// Show results of move operation
UpdateData(FALSE);
return(TRUE);
}
void CContinView::SetArray()
{
int i;
int nStepBack;
nStepBack = 0;
for (i = 0; i < 10; i++)
{// Save the current record and then get next one!
m_EnglishName[i] = m_pSet->m_English_Name;
m_ProductName[i] = m_pSet->m_Product_Name;
m_UnitPrice[i] = m_pSet->m_Unit_Price;
m_UnitsInStock[i] = m_pSet->m_Units_In_Stock;
m_UnitsOnOrder[i] = m_pSet->m_Units_On_Order;
if (!m_pSet->IsEOF())
{
TRY
{// Use old-style exceptions for Visual C++ 1.5x
m_pSet->MoveNext();
--nStepBack;
}
CATCH(CDBException, e)
{// Died. Should use message box to user!
TRACE("MoveNext() failed Ret = %d Error '%s', cause '%s'\n",
e->m_nRetCode,
(const char *)e->m_strError,
(const char *)e->m_strStateNativeOrigin);
}
END_CATCH
}
else
{
m_pSet->SetFieldNull(NULL);
}
}
// Restore the record pointer! Take nStepBack giant steps back!
TRY
{// Use old-style exceptions for Visual C++ 1.5x
m_pSet->Move(nStepBack);
}
CATCH(CDBException, e)
{// Died. Should use message box to user!
TRACE("Move(nStepBack) failed Ret = %d Error '%s', cause '%s'\n",
e->m_nRetCode,
(const char *)e->m_strError,
(const char *)e->m_strStateNativeOrigin);
}
END_CATCH
m_ArraySet = TRUE;
}
void CContinView::DoDataExchange(CDataExchange* pDX)
{
if (!m_ArraySet)
SetArray();
CRecordView::DoDataExchange(pDX);
//{{AFX_DATA_MAP(CContinView)
//}}AFX_DATA_MAP
DDX_Text(pDX, IDC_ENGLISH_NAME1, m_EnglishName[0]);
DDX_Text(pDX, IDC_PRODUCT_NAME1, m_ProductName[0]);
DDX_Text(pDX, IDC_UNIT_PRICE1, m_UnitPrice[0]);
DDX_Text(pDX, IDC_UNITS_IN_STOCK1, m_UnitsInStock[0]);
DDX_Text(pDX, IDC_UNITS_ON_ORDER1, m_UnitsOnOrder[0]);
DDX_Text(pDX, IDC_ENGLISH_NAME2, m_EnglishName[1]);
DDX_Text(pDX, IDC_PRODUCT_NAME2, m_ProductName[1]);
DDX_Text(pDX, IDC_UNIT_PRICE2, m_UnitPrice[1]);
DDX_Text(pDX, IDC_UNITS_IN_STOCK2, m_UnitsInStock[1]);
DDX_Text(pDX, IDC_UNITS_ON_ORDER2, m_UnitsOnOrder[1]);
DDX_Text(pDX, IDC_ENGLISH_NAME3, m_EnglishName[2]);
DDX_Text(pDX, IDC_PRODUCT_NAME3, m_ProductName[2]);
DDX_Text(pDX, IDC_UNIT_PRICE3, m_UnitPrice[2]);
DDX_Text(pDX, IDC_UNITS_IN_STOCK3, m_UnitsInStock[2]);
DDX_Text(pDX, IDC_UNITS_ON_ORDER3, m_UnitsOnOrder[2]);
DDX_Text(pDX, IDC_ENGLISH_NAME4, m_EnglishName[3]);
DDX_Text(pDX, IDC_PRODUCT_NAME4, m_ProductName[3]);
DDX_Text(pDX, IDC_UNIT_PRICE4, m_UnitPrice[3]);
DDX_Text(pDX, IDC_UNITS_IN_STOCK4, m_UnitsInStock[3]);
DDX_Text(pDX, IDC_UNITS_ON_ORDER4, m_UnitsOnOrder[3]);
DDX_Text(pDX, IDC_ENGLISH_NAME5, m_EnglishName[4]);
DDX_Text(pDX, IDC_PRODUCT_NAME5, m_ProductName[4]);
DDX_Text(pDX, IDC_UNIT_PRICE5, m_UnitPrice[4]);
DDX_Text(pDX, IDC_UNITS_IN_STOCK5, m_UnitsInStock[4]);
DDX_Text(pDX, IDC_UNITS_ON_ORDER5, m_UnitsOnOrder[4]);
DDX_Text(pDX, IDC_ENGLISH_NAME6, m_EnglishName[5]);
DDX_Text(pDX, IDC_PRODUCT_NAME6, m_ProductName[5]);
DDX_Text(pDX, IDC_UNIT_PRICE6, m_UnitPrice[5]);
DDX_Text(pDX, IDC_UNITS_IN_STOCK6, m_UnitsInStock[5]);
DDX_Text(pDX, IDC_UNITS_ON_ORDER6, m_UnitsOnOrder[5]);
DDX_Text(pDX, IDC_ENGLISH_NAME7, m_EnglishName[6]);
DDX_Text(pDX, IDC_PRODUCT_NAME7, m_ProductName[6]);
DDX_Text(pDX, IDC_UNIT_PRICE7, m_UnitPrice[6]);
DDX_Text(pDX, IDC_UNITS_IN_STOCK7, m_UnitsInStock[6]);
DDX_Text(pDX, IDC_UNITS_ON_ORDER7, m_UnitsOnOrder[6]);
DDX_Text(pDX, IDC_ENGLISH_NAME8, m_EnglishName[7]);
DDX_Text(pDX, IDC_PRODUCT_NAME8, m_ProductName[7]);
DDX_Text(pDX, IDC_UNIT_PRICE8, m_UnitPrice[7]);
DDX_Text(pDX, IDC_UNITS_IN_STOCK8, m_UnitsInStock[7]);
DDX_Text(pDX, IDC_UNITS_ON_ORDER8, m_UnitsOnOrder[7]);
DDX_Text(pDX, IDC_ENGLISH_NAME9, m_EnglishName[8]);
DDX_Text(pDX, IDC_PRODUCT_NAME9, m_ProductName[8]);
DDX_Text(pDX, IDC_UNIT_PRICE9, m_UnitPrice[8]);
DDX_Text(pDX, IDC_UNITS_IN_STOCK9, m_UnitsInStock[8]);
DDX_Text(pDX, IDC_UNITS_ON_ORDER9, m_UnitsOnOrder[8]);
DDX_Text(pDX, IDC_ENGLISH_NAME10, m_EnglishName[9]);
DDX_Text(pDX, IDC_PRODUCT_NAME10, m_ProductName[9]);
DDX_Text(pDX, IDC_UNIT_PRICE10, m_UnitPrice[9]);
DDX_Text(pDX, IDC_UNITS_IN_STOCK10, m_UnitsInStock[9]);
DDX_Text(pDX, IDC_UNITS_ON_ORDER10, m_UnitsOnOrder[9]);
}
void CContinView::OnInitialUpdate()
{
m_pSet = &GetDocument()->m_continSet;
CRecordView::OnInitialUpdate();
}
/////////////////////////////////////////////////////////////////////////////
// CContinView printing
BOOL CContinView::OnPreparePrinting(CPrintInfo* pInfo)
{
// Default preparation
return DoPreparePrinting(pInfo);
}
void CContinView::OnBeginPrinting(CDC* /*pDC*/, CPrintInfo* /*pInfo*/)
{
// TODO: Add extra initialization before printing
}
void CContinView::OnEndPrinting(CDC* /*pDC*/, CPrintInfo* /*pInfo*/)
{
// TODO: Add cleanup after printing
}
/////////////////////////////////////////////////////////////////////////////
// CContinView diagnostics
#ifdef _DEBUG
void CContinView::AssertValid() const
{
CRecordView::AssertValid();
}
void CContinView::Dump(CDumpContext& dc) const
{
CRecordView::Dump(dc);
}
CContinDoc* CContinView::GetDocument() // Non-debug version is inline
{
ASSERT(m_pDocument->IsKindOf(RUNTIME_CLASS(CContinDoc)));
return (CContinDoc*)m_pDocument;
}
#endif //_DEBUG
/////////////////////////////////////////////////////////////////////////////
// CContinView database support
CRecordset* CContinView::OnGetRecordset()
{
return m_pSet;
}
/////////////////////////////////////////////////////////////////////////////
// CContinView message handlers
CAUTION
If you want to use more than one record at a time from the CRecordView class object (which has a CRecordset object contained in it), you must override the default OnMove() CRecordView member function. This function normally isn't overridden, so there is no documentation from Microsoft on how the OnMove() member function actually works.
You can easily overcome this shortcoming in documentation by simply taking a peek at the MFC source code for the OnMove() member function, which is found in the source file DBVIEW.CPP (on the Visual C++ 4 distribution CD in the MSDEV\MFC\SRC folder).
To fully emulate Access's continuous subform, you need to add a vertical scroll bar to position the data control's record pointer. Because AppWizard-generated applications have record navigation buttons in their toolbars, it's usually not necessary to have a scroll bar as well. When your database access is part of a dialog box (dialog boxes don't have AppWizard-generated toolbars), you might want to code a scroll bar to let the user easily navigate through the database's records.
The problem you encounter when using scroll bars to position the record pointer is that scroll bars use numeric values, but recordsets don't have corresponding record number properties. As mentioned in Chapter 1, "Positioning Visual C++ in the Desktop Database Market," neither Access 1.x nor Visual C++ offers the equivalent of xBase's RECNO() function to return a record number.
You can choose from several approaches in order to create the equivalent of record numbers for recordset objects:
This last approach usually is the simplest and is used in the emulated subform application. Listing 15.3 shows the code for the function that overrides the CRecordView::OnMove() member function. This function started as a direct cut-and-paste of the original CRecordView::OnMove() code that is shown in Listing 15.2.
Listing 15.2 has some comments that explain the steps that OnMove() performs when moving from one record to another. These comments appear in bold.
Listing 15.2. CRecordView::OnMove() original handler.
// This is a part of the Microsoft Foundation Classes C++ library.
// Copyright 1993 Microsoft Corporation
// All rights reserved.
//
// This source code is intended only as a supplement to the
// Microsoft Foundation Classes Reference and Microsoft
// QuickHelp and/or WinHelp documentation provided with the library.
// See these sources for detailed information regarding the
// Microsoft Foundation Classes product.
#include "stdafx.h"
#ifdef AFX_DB_SEG
#pragma code_seg(AFX_DB_SEG)
#endif
#ifdef _DEBUG
#undef THIS_FILE
static char BASED_CODE THIS_FILE[] = __FILE__;
#endif
#define new DEBUG_NEW
/////////////////////////////////////////////////////////////////////////////
// Lines deleted ...
BOOL CRecordView::OnMove(UINT nIDMoveCommand)
{
// First, there are checks to make sure that we are not waiting for
// the datasource:
if (CDatabase::InWaitForDataSource())
{
#ifdef _DEBUG
if (afxTraceFlags & 0x20)
TRACE0("Warning: ignored move request\n");
#endif // _DEBUG
return TRUE;
}
// If we're not waiting for the datasource, we get the recordset.
// Then we check to see if we can update the current database. We
// then tell the recordset that we're going to edit (using Edit())
// the current record. We get the user's edits (if any) and then
// update (using Update()) the record in the recordset.
CRecordset* pSet = OnGetRecordset();
if (pSet->CanUpdate())
{
pSet->Edit();
if (!UpdateData())
return TRUE;
pSet->Update();
}
// The next step depends on what the move is. There are four
// choices: next record, previous record, first record, and
// last record. The switch() block manages these four choices.
switch (nIDMoveCommand)
{
case ID_RECORD_PREV:
pSet->MovePrev();
if (!pSet->IsBOF())
break;
case ID_RECORD_FIRST:
pSet->MoveFirst();
break;
case ID_RECORD_NEXT:
pSet->MoveNext();
if (!pSet->IsEOF())
break;
if (!pSet->CanScroll())
{
// Clear out screen since we're sitting on EOF
pSet->SetFieldNull(NULL);
break;
}
case ID_RECORD_LAST:
pSet->MoveLast();
break;
default:
// Unexpected case value
ASSERT(FALSE);
}
// Once the specified move has been made, we simply update
// the view's display of the record (for the user) and return.
// Show results of move operation
UpdateData(FALSE);
return TRUE;
}
Listing 15.2, the CRecordView::OnMove() handler, shows what the default action for OnMove() is. In the original handler, the current record (if there is one) is updated, and then the new record (if there is one) is loaded. At OnMove()'s completion, the view is updated to reflect the changes.
Listing 15.3 shows the new OnMove() handler. Much like the default OnMove(), this version updates the current records (all 10 of them) and then loads 10 "new" records. The facility can handle only single moves at a time, so nine of the 10 reads aren't necessary and could be replaced with assignments to move the currently loaded data to the new locations.
Listing 15.3 differs from Listing 15.1 in that the OnMove() handler doesn't call other functions that have been written. (In other words, it can run on its own.)
Listing 15.3. The OnMove() handler.
BOOL CContinView::OnMove(UINT nIDMoveCommand)
{
int i;
int nStepBack;
//---------START OF DBVIEW.CPP OnMove()...
// First, there are checks to make sure that we aren't waiting for
// the datasource:
if (CDatabase::InWaitForDataSource())
{
#ifdef _DEBUG
if (afxTraceFlags & 0x20)
TRACE0("Warning: ignored move request\n");
#endif // _DEBUG
return TRUE;
}
// If we aren't waiting for the datasource, we get the recordset.
// Then we check to see if we can update the current database. We
// then tell the recordset that we are going to edit (using Edit())
// the current record. We get the user's edits (if any) and then
// update (using Update()) the record in the recordset.
if (m_pSet->CanUpdate())
{
if (!UpdateData())
return TRUE;
nStepBack = 0;
for (i = 0; i < 10; i++)
{// Save the current record and then get next one!
m_pSet->Edit();
m_pSet->m_English_Name = m_EnglishName[i];
m_pSet->m_Product_Name = m_ProductName[i];
m_pSet->m_Unit_Price = m_UnitPrice[i];
m_pSet->m_Units_In_Stock = m_UnitsInStock[i];
m_pSet->m_Units_On_Order = m_UnitsOnOrder[i];
m_pSet->Update();
if (!m_pSet->IsEOF())
{
TRY
{// Use old-style exceptions for Visual C++ 1.5x
m_pSet->MoveNext();
--nStepBack;
}
CATCH(CDBException, e)
{// Died. Should use message box to user!
TRACE("MoveNext() fail Ret = %d Error '%s', cause '%s'\n",
e->m_nRetCode,
(const char *)e->m_strError,
(const char *)e->m_strStateNativeOrigin);
}
END_CATCH
}
else
{
break;
}
}
// Restore the record pointer! Take nStepBack giant steps back!
TRY
{// Use old-style exceptions for Visual C++ 1.5x
m_pSet->Move(nStepBack); // Back to original record...
}
CATCH(CDBException, e)
{// Died. Should use message box to user!
TRACE("Move(nStepBack) failed Ret = %d Error '%s', cause '%s'\n",
e->m_nRetCode,
(const char *)e->m_strError,
(const char *)e->m_strStateNativeOrigin);
}
END_CATCH
}
// The next step depends on what the move is. There are four
// choices: next record, previous record, first record, and
// last record. The switch() block manages these four choices.
switch (nIDMoveCommand)
{
case ID_RECORD_PREV:
m_pSet->MovePrev();
if (!m_pSet->IsBOF())
break;
case ID_RECORD_FIRST:
m_pSet->MoveFirst();
break;
case ID_RECORD_NEXT:
m_pSet->MoveNext();
if (!m_pSet->IsEOF())
break;
if (!m_pSet->CanScroll())
{
// Clear out screen since we're sitting on EOF
m_pSet->SetFieldNull(NULL);
break;
}
case ID_RECORD_LAST:
m_pSet->MoveLast();
break;
default:
// Unexpected case value
ASSERT(FALSE);
}
//---------END OF DBVIEW.CPP OnMove()...
nStepBack = 0;
for (i = 0; i < 10; i++)
{// Save the current record and then get next one!
m_EnglishName[i] = m_pSet->m_English_Name;
m_ProductName[i] = m_pSet->m_Product_Name;
m_UnitPrice[i] = m_pSet->m_Unit_Price;
m_UnitsInStock[i] = m_pSet->m_Units_In_Stock;
m_UnitsOnOrder[i] = m_pSet->m_Units_On_Order;
if (!m_pSet->IsEOF())
{
TRY
{// Use old-style exceptions for Visual C++ 1.5x
m_pSet->MoveNext();
--nStepBack;
}
CATCH(CDBException, e)
{// Died. Should use message box to user!
TRACE("MoveNext() failed Ret = %d Error '%s', cause '%s'\n",
e->m_nRetCode,
(const char *)e->m_strError,
(const char *)e->m_strStateNativeOrigin);
}
END_CATCH
}
else
{
m_pSet->SetFieldNull(NULL);
}
}
// Restore the record pointer! Take nStepBack giant steps back!
TRY
{// Use old-style exceptions for Visual C++ 1.5x
m_pSet->Move(nStepBack);
}
CATCH(CDBException, e)
{// Died. Should use message box to user!
TRACE("Move(nStepBack) failed Ret = %d Error '%s', cause '%s'\n",
e->m_nRetCode,
(const char *)e->m_strError,
(const char *)e->m_strStateNativeOrigin);
}
END_CATCH
// Once the specified move has been made, we simply update
// the view's display of the record (for the user) and return
// Show results of move operation
UpdateData(FALSE);
return(TRUE);
}
The code in Listings 15.1, 15.2, and 15.3 not only displays data but also updates any records the user has modified. The code in Listing 15.4 shows the process of updating.
This process is straightforward: Move to the correct record (the record that is to be updated), get the "new" values for the record, put the new values in the recordset's column variables, and update the record. In the sample program, this is accomplished 10 times (because 10 records can be displayed at once). This lets the user update 10 records at a time, eliminating the inconvenience of being able to edit only the current record.
Listing 15.4. The update code.
if (m_pSet->CanUpdate())
{
if (!UpdateData())
return TRUE;
nStepBack = 0;
for (i = 0; i < 10; i++)
{// Save the current record and then get next one!
m_pSet->Edit();
m_pSet->m_English_Name = m_EnglishName[i];
m_pSet->m_Product_Name = m_ProductName[i];
m_pSet->m_Unit_Price = m_UnitPrice[i];
m_pSet->m_Units_In_Stock = m_UnitsInStock[i];
m_pSet->m_Units_On_Order = m_UnitsOnOrder[i];
m_pSet->Update();
if (!m_pSet->IsEOF())
{
TRY
{// Use old-style exceptions for Visual C++ 1.5x
m_pSet->MoveNext();
--nStepBack;
}
CATCH(CDBException, e)
{// Died. Should use message box to user!
TRACE("MoveNext() fail Ret = %d Error '%s', cause '%s'\n",
e->m_nRetCode,
(const char *)e->m_strError,
(const char *)e->m_strStateNativeOrigin);
}
END_CATCH
}
else
{
break;
}
}
// Restore the record pointer! Take nStepBack giant steps back!
TRY
{// Use old-style exceptions for Visual C++ 1.5x
m_pSet->Move(nStepBack); // Back to original record...
}
CATCH(CDBException, e)
{// Died. Should use message box to user!
TRACE("Move(nStepBack) failed Ret = %d Error '%s', cause '%s'\n",
e->m_nRetCode,
(const char *)e->m_strError,
(const char *)e->m_strStateNativeOrigin);
}
END_CATCH
}
NOTE
You can add code to the OnMove() handler that tests the validity of the updates you make to any of the fields before the changes are processed.
The OLE Grid control sample program (called Contin using Grid Control) is simpler than the example just shown. The OLE Grid control sample program doesn't implement user editing of database data, but you can easily add this functionality, as shown in Listing 15.5. This listing shows the view class, which is the only modification needed to implement this program. As with other listings, the lines in bold provide the increased functionality.
Listing 15.5. Contin using Grid ControlView.cpp.
// Contin using Grid ControlView.cpp : implementation of the
// CContinusingGridControlView class
//
#include "stdafx.h"
#include "Contin using Grid Control.h"
#include "Contin using Grid ControlSet.h"
#include "Contin using Grid ControlDoc.h"
#include "Contin using Grid ControlView.h"
#include "gridctrl.h"
#ifdef _DEBUG
#define new DEBUG_NEW
#undef THIS_FILE
static char THIS_FILE[] = __FILE__;
#endif
/////////////////////////////////////////////////////////////////////////////
// CContinusingGridControlView
IMPLEMENT_DYNCREATE(CContinusingGridControlView, CRecordView)
BEGIN_MESSAGE_MAP(CContinusingGridControlView, CRecordView)
//{{AFX_MSG_MAP(CContinusingGridControlView)
ON_WM_DESTROY()
//}}AFX_MSG_MAP
// Standard printing commands
ON_COMMAND(ID_FILE_PRINT, CRecordView::OnFilePrint)
ON_COMMAND(ID_FILE_PRINT_DIRECT, CRecordView::OnFilePrint)
ON_COMMAND(ID_FILE_PRINT_PREVIEW, CRecordView::OnFilePrintPreview)
END_MESSAGE_MAP()
/////////////////////////////////////////////////////////////////////////////
// CContinusingGridControlView construction/destruction
CContinusingGridControlView::CContinusingGridControlView()
: CRecordView(CContinusingGridControlView::IDD)
{
//{{AFX_DATA_INIT(CContinusingGridControlView)
// NOTE: the ClassWizard will add member initialization here
m_pSet = NULL;
//}}AFX_DATA_INIT
// TODO: add construction code here
}
CContinusingGridControlView::~CContinusingGridControlView()
{
}
void CContinusingGridControlView::DoDataExchange(CDataExchange* pDX)
{
static bFirstTime = TRUE;
CRecordView::DoDataExchange(pDX);
//{{AFX_DATA_MAP(CContinusingGridControlView)
DDX_Control(pDX, IDC_GRID1, m_GridControl);
//}}AFX_DATA_MAP
int Column = 0;
int Row = 0;
CString Formatted;
VARIANT item;
// Go to and get first record
m_pSet->MoveFirst();
while (!m_pSet->IsEOF())
{// First, process the current record
item.lVal = ++Row;
m_GridControl.SetRow(Row);
m_GridControl.SetSelStartRow(Row);
m_GridControl.SetSelEndRow(Row);
// The grid control allows filling multiple columns in a
// record by separating each column with a tab. You can
// also fill multiple rows by separating each row with a
// CR (\x0D) (do not use a \n, however).
Formatted.Format(_T(" \t%s\t%s\t%s\t%d\t%d"),
m_pSet->m_Product_Name,
m_pSet->m_English_Name,
m_pSet->m_Unit_Price,
m_pSet->m_Units_In_Stock,
m_pSet->m_Units_On_Order);
m_GridControl.AddItem(Formatted, item);
m_pSet->MoveNext();
}
// Swap first and last entry to create a fixed first (title) row:
for (Column = 0; Column <= 5; Column++)
{// Do each column in the first and last rows:
m_GridControl.SetRow(0);
m_GridControl.SetCol(Column);
Formatted = m_GridControl.GetText();
m_GridControl.SetRow(Row);
m_GridControl.SetText(Formatted);
}
// Set title row's text:
m_GridControl.SetRow(0);
m_GridControl.SetCol(0);
m_GridControl.SetText(_T(""));
m_GridControl.SetCol(1);
m_GridControl.SetText(_T("Product"));
m_GridControl.SetCol(2);
m_GridControl.SetText(_T("Name"));
m_GridControl.SetCol(3);
m_GridControl.SetText(_T("Price"));
m_GridControl.SetCol(4);
m_GridControl.SetText(_T("Quantity in Stock"));
m_GridControl.SetCol(5);
m_GridControl.SetText(_T("Quantity on Order"));
// And fix the first row as a title row.
m_GridControl.SetFixedRows(1);
if (bFirstTime)
{// Set column widths the first time only. Do not reset on redraw!
bFirstTime = FALSE;
// Our default widths are just guesses based on
// the title's width. For the first two columns, the
// data is really wide, and the titles are narrow, so
// we add a bit more for a better view.
m_GridControl.SetColWidth(0,
AfxGetApp()->GetProfileInt(_T("GridStuff"), _T("ColWidth1"),
50));
m_GridControl.SetColWidth(1,
AfxGetApp()->GetProfileInt(_T("GridStuff"), _T("ColWidth2"),
strlen(_T("Product")) * 100 + 1000));
m_GridControl.SetColWidth(2,
AfxGetApp()->GetProfileInt(_T("GridStuff"), _T("ColWidth3"),
strlen(_T("Name")) * 100 + 1000));
m_GridControl.SetColWidth(3,
AfxGetApp()->GetProfileInt(_T("GridStuff"), _T("ColWidth4"),
strlen(_T("Price")) * 100));
m_GridControl.SetColWidth(4,
AfxGetApp()->GetProfileInt(_T("GridStuff"), _T("ColWidth5"),
strlen(_T("Quantity in Stock")) * 100));
m_GridControl.SetColWidth(5,
AfxGetApp()->GetProfileInt(_T("GridStuff"), _T("ColWidth6"),
strlen(_T("Quantity on Order")) * 100));
}
}
BOOL CContinusingGridControlView::PreCreateWindow(CREATESTRUCT& cs)
{
// TODO: Modify the Window class or styles here by modifying
// the CREATESTRUCT cs
return CRecordView::PreCreateWindow(cs);
}
void CContinusingGridControlView::OnInitialUpdate()
{
m_pSet = &GetDocument()->m_continusingGridControlSet;
CRecordView::OnInitialUpdate();
}
/////////////////////////////////////////////////////////////////////////////
// CContinusingGridControlView printing
BOOL CContinusingGridControlView::OnPreparePrinting(CPrintInfo* pInfo)
{
// Default preparation
return DoPreparePrinting(pInfo);
}
void CContinusingGridControlView::OnBeginPrinting
(CDC* /*pDC*/, CPrintInfo* /*pInfo*/)
{
// TODO: add extra initialization before printing
}
void CContinusingGridControlView::OnEndPrinting
(CDC* /*pDC*/, CPrintInfo* /*pInfo*/)
{
// TODO: add cleanup after printing
}
/////////////////////////////////////////////////////////////////////////////
// CContinusingGridControlView diagnostics
#ifdef _DEBUG
void CContinusingGridControlView::AssertValid() const
{
CRecordView::AssertValid();
}
void CContinusingGridControlView::Dump(CDumpContext& dc) const
{
CRecordView::Dump(dc);
}
CContinusingGridControlDoc* CContinusingGridControlView::GetDocument()
// Non-debug version is inline
{
ASSERT(m_pDocument->IsKindOf(RUNTIME_CLASS(CContinusingGridControlDoc)));
return (CContinusingGridControlDoc*)m_pDocument;
}
#endif //_DEBUG
/////////////////////////////////////////////////////////////////////////////
// CContinusingGridControlView database support
CRecordset* CContinusingGridControlView::OnGetRecordset()
{
return m_pSet;
}
/////////////////////////////////////////////////////////////////////////////
// CContinusingGridControlView message handlers
BEGIN_EVENTSINK_MAP(CContinusingGridControlView, CRecordView)
//{{AFX_EVENTSINK_MAP(CContinusingGridControlView)
ON_EVENT(CContinusingGridControlView, IDC_GRID1, -600
/* Click */, OnClickGrid1, VTS_NONE)
//}}AFX_EVENTSINK_MAP
END_EVENTSINK_MAP()
void CContinusingGridControlView::OnClickGrid1()
{
// TODO: Add your control notification handler code here
// When the user clicks an item in our grid control, we
// get this message. You could pop the item into an edit
// box for further modifications by the user.
// Use these functions:
//
// m_GridControl.GetSelStartCol()
// m_GridControl.GetSelEndCol()
// m_GridControl.GetSelStartRow()
// m_GridControl.GetSelEndRow()
//
// to determine the current selection. Be sure to
// correctly handle a range selection when doing this.
}
void CContinusingGridControlView::OnDestroy()
{
CRecordView::OnDestroy();
// We save the column widths for the user, so the next time
// the program is started, the user's column widths (and not
// the defaults) will be used.
m_GridControl.SetRow(0);
AfxGetApp()->WriteProfileInt(_T("GridStuff"), _T("ColWidth1"),
m_GridControl.GetColWidth(0));
AfxGetApp()->WriteProfileInt(_T("GridStuff"), _T("ColWidth2"),
m_GridControl.GetColWidth(1));
AfxGetApp()->WriteProfileInt(_T("GridStuff"), _T("ColWidth3"),
m_GridControl.GetColWidth(2));
AfxGetApp()->WriteProfileInt(_T("GridStuff"), _T("ColWidth4"),
m_GridControl.GetColWidth(3));
AfxGetApp()->WriteProfileInt(_T("GridStuff"), _T("ColWidth5"),
m_GridControl.GetColWidth(4));
AfxGetApp()->WriteProfileInt(_T("GridStuff"), _T("ColWidth6"),
m_GridControl.GetColWidth(5));
}
In Listing 15.5, you can see how the OLE Grid control is filled in. The OLE Grid control is initially created with two rows. It is then expanded as needed (using the AddItem() function). Because AddItem() doesn't work when your OLE Grid control has fixed columns (this shortcoming might be fixed in the next release of Visual C++), the column titles must be added to the control last and moved to the top and fixed after the control has been populated.
When you create an OLE Grid control using the dialog box editor, you can't specify column widths. This means that you must either accept the default width of 600 twips or modify the column widths at execution time. The user can modify column widths (and row heights) at runtime, so it's a good idea to save the column widths when your program exits. In the sample program, you simply write these values to an .INI file.
NOTE
Chapter 16, "Creating OLE Controls with Visual C++ 4," documents all the OLE controls supplied with Visual C++ 4.
In everyday English, the term transaction implies a business deal, such as trading cash for a new CD player or a tank car of acrylonitrile. The dictionary defines transact as "to drive through" or "to complete." Database transactions can involve changes to or additions of one or more records in a single table or in several tables. When more than one record or table is involved in a transaction, it's vital that all the records be updated simultaneously or as close to simultaneously as possible. The database is said to be in an inconsistent state until the records of each of the tables involved in the transaction have been updated successfully.
If hardware or software errors occur, if a domain or referential integrity violation is detected, or if the application can't alter or add a record because another user in a multiuser environment has placed locks on one or more of the records involved, the updates to all tables must be canceled. Any changes made to tables before the transaction operation terminates must be undone. An example is an automatic teller transaction. Your bank credits its cash account and debits your checking account whenever you make a cash withdrawal at an ATM. Obviously, your bank doesn't want a one-sided transaction to occur wherein you receive the cash but your account isn't debited. Canceling or undoing a transaction is called rolling back the transaction.
The following sections describe how and when Visual C++ supports the rolling back of aborted transactions.
Visual C++'s MFC provides the CDatabase::BeginTrans(), CDatabase::CommitTrans(), and CDatabase::Rollback() functions to maintain database consistency in transaction-processing applications. Also, the SQLTransact() function is a (more) direct method of managing transactions for applications that aren't based on MFC. This section covers the MFC methods, because SQLTransact() differs from the MFC functions only in that a single call to one function with a parameter specifying the different actions is made.
These three functions actually are methods that apply an invisible recordset (a buffer) that contains the pending update(s) to the database tables. Another invisible recordset, the transaction log, stores a copy of the data contained in the affected rows of the recordset before the updates occur. You can use CDatabase's three TPL instructions to ensure that all updates to database tables proceed to completion and that the database returns to a consistent state when the transaction completes or when the updates are rolled back. The following two sections describe the structure of code that employs Visual C++'s transaction-processing instructions and the limited applicability of the ROLLBACK instruction to supported database types.
The general structure of the Visual C++ transaction-processing commands, expressed in metacode, is as follows:
Name()
{
...
TRY
{
CDatabase.BeginTrans()
while(Whatever Condition)
{
[CDatabase.Edit()|AddNew()]
[Field update code...]
CDatabase.{Update()|Delete()}
}
CDatabase.CommitTrans()
}
CATCH_ALL
{
CDatabase.Rollback()
AFXMsgBox()
}
return()
}
The following is the metacode for an alternative structure that incorporates error processing within a loop structure:
Name()
{
...
CDatabase.BeginTrans
While (Whatever Condition && !fError)
{
TRY
{
[CDatabase.Edit|AddNew]
[Field update code...]
CDatabase.{Update|Delete}
}
CATCH_ALL
{
fError = TRUE
}
}
If (fError)
{
Error = FALSE
CDatabase.Rollback()
}
else
{
CDatabase.CommitTrans()
}
}
The CDatabase.BeginTrans() and CDatabase.CommitTrans() statements always are used in pairs. If you use CDatabase.BeginTrans() and forget CDatabase.CommitTrans(), the transaction log fills with all the pre-update recordset values that occur after the CDatabase.BeginTrans() statement is encountered. The accumulated data ultimately consumes all of your computer's memory, and all hell breaks loose. Code within the CDatabase.BeginTrans()...CDatabase.CommitTrans() structure can be indented to identify the elements that constitute the transaction. You can nest transactions applied to Access databases up to five levels deep. When you nest transactions, you need to write code that rolls back each set of transactions, beginning with the innermost nested transaction. For example, you can use the fError flag set in the preceding metacode example to cause ROLLBACK instructions to be executed at each transaction level.
When the CDatabase.BeginTrans() statement is executed, the following operations occur (with Access databases):
One of the advantages of using Visual C++'s transaction-processing instructions is that bulk updates to tables occur much faster than when you apply the Update or Delete methods to a single record. Each time you apply the Update or Delete methods singly, the Microsoft Jet database engine opens an instance of the table; adds, modifies, or deletes parts of the physical table file; and then closes the instance, flushing all disk write buffers in the process. When you use the transaction-processing instructions, all the operations are conducted in buffers (in memory), with a single write-buffer flush operation at the end of the process.
Listing 15.6 shows an actual transaction-processing function. This example updates more than one dataset.
Listing 15.6. A function to perform transaction processing.
BOOL CFoodDoc::RemoveIngredient(CString strIngredient)
{
// Remove Ingredient from all the recipies that the Ingredient is used in
CRecipiesSet rsRecipiesSet(m_dbIngredientReg);
rsRecipiesSet.m_strFilter = "IngredientID = " + strIngredient;
rsRecipiesSet.Open(CRecordset::dynaset);
CIngredientSet rsIngredientSet(m_dbIngredientReg);
rsIngredientSet.m_strFilter = "IngredientID = " + strIngredient;
rsIngredientSet.Open(CRecordset::dynaset);
if (!m_dbIngredientReg.BeginTrans())
return FALSE;
TRY
{
while (!rsRecipiesSet.IsEOF())
{
rsRecipiesSet.Delete();
rsRecipiesSet.MoveNext();
}
// Delete the Ingredient record
rsIngredientSet.Delete();
m_dbIngredientReg.CommitTrans();
}
CATCH_ALL
{
m_dbIngredientReg.Rollback();
return FALSE;
}
return TRUE;
}
Only Access databases fully support transaction-processing instructions when you connect these databases with the Microsoft Jet database engine. Transaction-processing support is inherent in Access databases, but Btrieve databases require the special network transactions file BTRIEVE.TRN. FoxPro, dBASE, and Paradox databases don't support the rolling back of transactions. You can test whether a Table or a Dynaset object supports transactions by testing the value of the object's Transactions property. You can roll back changes to most tables if the value of the Transactions property is TRUE.
TIP
The Microsoft Jet database engine doesn't support rolling back transactions on Access tables that are attached to the Access database that underlies the active database object. If you need to provide rollback capability for transactions that span multiple Access databases, don't attach Access tables to your primary database. Instead, open each of the databases that contain the native tables required by your Dynaset object.
The Microsoft Jet database engine doesn't support the rolling back of transactions on client-server databases attached by the ODBC API if the attached client-server database ODBC driver doesn't support transactions. (Access SQL doesn't recognize the ANSI SQL TPL reserved words.) To take advantage of the transaction-processing capabilities of client-server databases, you need to apply the ExecuteSQL() method or apply the Execute method with the SQL pass-through option. Instead of using the CDatabase.BeginTrans()...CDatabase.CommitTrans() code structure, you use the ANSI SQL BEGIN TRANS[ACTION] COMMIT TRANS[ACTION] structure (or its equivalent) to enclose the action query statements. The method of detecting the failure of COMMIT in order to execute the ROLLBACK TRANS[ACTION] statement varies with the RDBMS you use.
The SQL statement you pass to the server RDBMS must correspond to the transaction syntax requirements of the particular RDBMS in use. Some client-server RDBMSs, such as SQL Server, require that you identify the beginning of a transaction with a BEGIN TRANS[ACTION] statement. DB2, on the other hand, uses intrinsic transactions. A DB2 unit of work commences with the first change you make to a database that is in a consistent condition and terminates with a COMMIT statement. If the COMMIT is unsuccessful, the ROLLBACK [WORK] statement undoes the unit of work.
Regardless of whether you can roll back changes you make to tables, using the CDatabase.BeginTrans()...CDatabase.CommitTrans() structure for bulk changes to tables almost always improves your application's performance. However, substantial risk is inherent in using CDatabase.BeginTrans()...CDatabase.CommitTrans() when you can't roll back changes. If your transaction fails, there's no way to determine at what point the failure occurred.
WARNING
A failure in the middle of a bulk update can make it unlikely that you will ever bring the database back to a consistent state. Thus, the safe approach when you can't roll back transactions is to write code to duplicate the action of the update buffer and transaction log and then execute each transaction individually. If an error occurs, your error-processing routine can undo the related changes that were made before the error was encountered.
This chapter covered the basic principles of the design of Visual C++ transaction-processing applications using either SQL action queries or Visual C++ code to perform updates on database tables. A sample application, Contin (and Contin using Grid Control), showed you how to write code to aggregate multiple updates into a single set of transactions. This chapter concluded with a discussion of the Visual C++ transaction-processing instructions CDatabase.BeginTrans(), CDatabase.CommitTrans(), and CDatabase.Rollback(), which you can use in conjunction with updates to tables in Access databases.
The next two chapters deal primarily with interprocess communication (IPC) between Visual C++ database applications that act as OLE 1.0, OLE 2.0, and DDE clients.
