| Click here to support our advertisers

SOFTWARE FOR SALE
BOOKS FOR SALE
SEARCH CENTRAL
JOB BANK
CLASSIFIEDS
DIRECTORIES
REFERENCE Online Library
LEARNING CENTER
JOURNAL
NEWS CENTRAL
DOWNLOADS
COMMUNITY
CALENDAR
ABOUT US
Journal:
Get the weekly email highlights from the most popular journal for developers! Current issue
> > > developer.com developerdirect.com htmlgoodies.com javagoodies.com jars.com intranetjournal.com javascripts.com | All Categories : C/C++ vcg15.htm Categorizing Transaction-Processing Applications Using SQL Statements and Recordsets for Transaction Processing Determining When You Can Update Joined Tables SQL and Set-Oriented Transactions A Review of SQL Action Query Syntax Executing SQL Action Queries with Visual C++ Code Recordset Transactions with Bound Controls Using Edit Boxes for Data Entry Using Visual C++ Code for Transaction Processing Emulating an Access Continuous Subform in Visual C++ Adding Update Capability Using an OLE Grid Control Processing Multitable Transactions Maintaining Transactional Consistency with Visual C++ Code The Structure of the Transaction Instructions The Applicability of Visual C++ Transaction Instructions Summary # - 15 - Designing Online Transaction-Processing Applications 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. ### Categorizing Transaction-Processing Applications 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: Interactive transaction-processing applications combine decision-support and transaction-processing methods. Interactive applications let the user review and edit existing information. Most allow new records to be added to tables. Personal information management (PIM), live (telephone) order entry, and airline reservations systems are typical interactive OLTP applications. Concurrency (page- or record-locking) issues arise in multiuser interactive TP applications. Chapter 19, "Running Visual C++ Database Applications on a Network," discusses page- and record-locking methods to resolve concurrency issues in multiuser applications. Input-only TP applications add records to database tables. The majority of input-only applications only let the user append new records; they don't provide the capability to review data other than the data currently being entered—although lookup tables often are used to speed the data-entry process. Many input-only applications fall into the "heads-down" data-entry class, in which data-entry operators process information found on paper forms. OLTP applications for heads-down data entry emphasize use of the keyboard rather than the mouse to perform every data-entry function. Concurrency problems seldom arise in multiuser input-only applications, because the data-entry operator "owns" the new record until the record is added to the table. Data-validation applications are used to test update data for errors before making final updates to primary database tables. The most common use of data-validation applications is in batch processing systems. In a multiuser environment, data-validation applications need to take into account pending changes to the same record in a primary table initiated by more than one user. The problem of resolving conflicts that arise as a result of multiple changes to a single primary record is one of the reasons that OLTP is favored over batch processing in today's transaction-processing environment. Consistency is the primary issue in data-validation applications. 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. ### Using SQL Statements and Recordsets for Transaction Processing 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. #### Determining When You Can Update Joined Tables 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 and Set-Oriented Transactions 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. ##### A Review of SQL Action Query Syntax 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. The INSERT, UPDATE, and DELETE verbs append, modify, and delete rows in tables, respectively. These verbs are part of ANSI and Access SQL's data manipulation language (DML). The Microsoft Jet database engine and all ODBC drivers support these three verbs. If the engine can't execute the action query in its entirety, the query fails and you receive an error. (No changes are made to any records.) All client-server RDBMSs support the COMMIT and ROLLBACK statements of SQL's transaction-processing language (TPL) to ensure that all the rows of tables affected by a DML statement are updated at once. A full discussion of the COMMIT and ROLLBACK statements appears later in this chapter. Some RDBMSs, such as IBM's DB2, use the term unit of work to define a transaction and use COMMIT WORK and ROLLBACK WORK to commit or roll back a unit of work. A unit of work is defined as all statements since the last COMMIT or point of known consistency of the table's data. The majority of client-server RDBMSs use the BEGIN TRANS[ACTION] statement to explicitly specify the beginning of a transaction. Access SQL employs the unit of work approach and doesn't support TPL statements. If the database to which the Microsoft Jet database engine is connected supports transactions, the engine automatically rolls back an action query that encounters errors during its execution. The SELECT...INTO structure lets you create new tables in a database with the Microsoft Jet database engine and most ODBC drivers. (DB2 uses the SELECT...INTO structure to assign variables in a single row to a host program variable.) Using SELECT...INTO to create a new table is much simpler than writing Visual C++ code to add a new TableDef object to the TableDefs collection. The majority of single- and multiple-tier ODBC drivers also support the SQL data definition language (DDL) statements to create new tables (CREATE TABLE), add indexes to tables (CREATE INDEX), and prevent empty field values (NOT NULL). Some ODBC drivers let you delete tables and/or indexes (DROP TABLE and DROP INDEX). Access SQL doesn't support ANSI SQL DDL statements. Few ODBC drivers currently support the SQL-92 domain integrity enforcement (CHECK and CONSTRAINT) or referential integrity enforcement (PRIMARY KEY, FOREIGN KEY, and REFERENCES). Client-server RDBMSs such as Sybase SQL Server use triggers (a form of stored procedure) to detect domain or referential integrity violations. Access action queries enforce referential integrity when you create your database with the retail version of Access. You specify default relationships and check the Maintain Referential Integrity check box of the Relationships dialog box to emulate the effect of the PRIMARY KEY, FOREIGN KEY, and REFERENCES clauses. Although you can apply domain integrity rules (using the ValidationRule property) to fields of tables you create with Access, Visual C++ doesn't recognize these rules. You need to create your own data validation procedures with Visual C++ code. 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++. ##### Executing SQL Action Queries with Visual C++ Code You execute action queries by using one of two Visual C++ methods: The SQLExecDirect() function lets you execute an SQL statement that has been previously prepared using the SQLPrepare() function. In most cases, you pass the operands of the criteria statements in the WHERE clause as parameters. Alternatively, you can substitute an SQL statement as a string variable and use the SQLExecDirect() function. The CDatabase member function ExecuteSQL() is applicable only to databases connected by the ODBC API and uses SQL pass-through exclusively. ExecuteSQL() doesn't return records. Therefore, to execute an SQL command that returns records, you must create and use a CRecordset object. You can't specify options when you use the ExecuteSQL() member function; the sole argument of ExecuteSQL() is the SQL statement. 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. #### Recordset Transactions with Bound Controls 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. ##### Using Edit Boxes 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). #### Using Visual C++ Code for Transaction Processing 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 data-entry operator can make changes to the content of the edit controls at will without affecting the status of the database tables until he or she is satisfied that the edit is correct. Although edit changes to tables don't occur until the record pointer is moved and you call the CRecordset::Update() member function in your code (CRecordset won't automatically call Update() when the pointer is moved), a new record is added to the table when any field of the tentative append record is filled. You can validate all the entries at once when the user clicks the OK button (or presses an Alt-key accelerator key combination) to accept the edited values instead of validating each entry individually. All-at-once validation is particularly useful when entries are interdependent, as in the case of city, state, and zip code entries. When you use bound controls to update existing records, the application places a lock on the current page (Access, Btrieve, and SQL Server databases) or record (dBASE, FoxPro, and Paradox databases) for the duration of the editing process. If the person who is editing the record goes to lunch without completing the edit, no one else can edit the record (or any record in the same 2K page) for the duration. 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. ##### Emulating an Access Continuous Subform in Visual C++ 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: Create an array of the data in the recordset object and use the array elements instead of the data in the recordset object to populate the emulated subform. The element number of the array substitutes for the record number. However, you need to update the array each time you edit or add a new record to the recordset object. Conventional 16-bit Visual C++ arrays are limited to 64K (for all practical purposes), so this approach works best with small tables. You can create an array larger than 64K, but you need to have the length of the array elements be a power of 2, which might waste substantial storage space. With Visual C++ 2.x (or any other version of Visual C++ that creates 32-bit programs, including Visual C++ 4.x), an array isn't limited to 64K, and elements in the array don't need to be a power of 2. Create an array of bookmarks for the recordset. This is a faster process than creating an array of the recordset data, but you still have a 64K limit on the size of the array. An SQL_C_BOOKMARK object is an unsigned long int (4 bytes long), so you could have up to 16,385 bookmark objects in a 64K block of memory. You can easily define a huge array of SQL_C_BOOKMARK objects because sizeof(unsigned long int) is a power of 2. Save the last value of the vertical scroll bar's position and use the MoveNext or MovePrevious methods to position the record pointer incrementally. Set the Min value of the scroll bar to 1 and the Max value to CRecordset::GetRecordCount(). (You must read all records in the recordset to ensure that CRecordset::GetRecordCount() returns the actual number of records in the recordset.) The range of scroll bars is limited to the range of integer variables (+32,767 to –32,768). If you expect your recordset to contain more than 32,000 records, you need to use math to move the record pointer more than one record for each scroll bar Value increment or decrement. 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);

>     }
> ##### Adding Update Capability 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. ##### Using an OLE Grid Control 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. ### Processing Multitable Transactions 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. #### Maintaining Transactional Consistency with Visual C++ Code 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 Structure of the Transaction Instructions 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): Visual C++ instructs the Microsoft Jet database engine to open a temporary update buffer and a temporary transaction log. Both the update buffer and the transaction log are virtual tables that are stored in memory. All records for transactions that occur before the execution of the CDatabase.CommitTrans() statement are stored in the update buffer. When the CDatabase.CommitTrans() statement is reached, the Microsoft Jet database engine commences the execution of the transactions stored in the update buffer. Before records are replaced or deleted, the record that is to be updated or deleted is saved in the transaction log. The Microsoft Jet database engine attempts to update, delete, or add new records to the table. If no errors occur, an SQL COMMIT statement is executed, the changes are made permanent in the tables, and the temporary transaction log is cleared. If an error is generated during the transaction process, the update buffer is cleared, program execution jumps to the error-handling routine, and an SQL ROLLBACK instruction is executed. The SQL ROLLBACK instruction replaces records that were updated or deleted with records from the transaction log file. Any records added to tables using the AddNew method are deleted. When the SQL ROLLBACK operation is completed, the temporary transaction log file is cleared. 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;

>     }
##### The Applicability of Visual C++ Transaction Instructions 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. ### Summary 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. Contact reference@earthweb.com with questions or comments. Copyright 1998 EarthWeb Inc., All rights reserved. PLEASE READ THE ACCEPTABLE USAGE STATEMENT. Copyright 1998 Macmillan Computer Publishing. All rights reserved. |