📄 vcg15.htm
字号:
<HTML>
<HEAD>
<TITLE>vcg15.htm </TITLE>
<LINK REL="ToC" HREF="index-1.htm" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/index.htm">
<LINK REL="Index" HREF="htindex.htm" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/htindex.htm">
<LINK REL="Next" HREF="vcg16.htm" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/vcg16.htm">
<LINK REL="Previous" HREF="vcg14.htm" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/vcg14.htm"></HEAD>
<BODY BGCOLOR="#FFFFFF" TEXT="#000000" LINK="#0000FF" VLINK="#800080">
<A NAME="I0"></A><P ALIGN=CENTER>
<A HREF="vcg14.htm" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/vcg14.htm" TARGET="_self"><IMG SRC="blanprev.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/blanprev.gif" WIDTH = 37 HEIGHT = 37 BORDER = 0 ALT="Previous Page"></A>
<A HREF="index-1.htm" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/index.htm" TARGET="_self"><IMG SRC="blantoc.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/blantoc.gif" WIDTH = 37 HEIGHT = 37 BORDER = 0 ALT="TOC"></A>
<A HREF="vcg16.htm" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/vcg16.htm" TARGET="_self"><IMG SRC="blannext.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/blannext.gif" WIDTH = 37 HEIGHT = 37 BORDER = 0 ALT="Next Page"></A>
<HR ALIGN=CENTER>
<P>
<UL>
<UL>
<UL>
<LI>
<A HREF="#E68E79" >Categorizing Transaction-Processing Applications</A>
<LI>
<A HREF="#E68E80" >Using SQL Statements and Recordsets for Transaction Processing</A>
<UL>
<LI>
<A HREF="#E69E207" >Determining When You Can Update Joined Tables</A>
<LI>
<A HREF="#E69E208" >SQL and Set-Oriented Transactions</A>
<UL>
<LI>
<A HREF="#E70E72" >A Review of SQL Action Query Syntax</A>
<LI>
<A HREF="#E70E73" >Executing SQL Action Queries with Visual C++ Code</A></UL>
<LI>
<A HREF="#E69E209" >Recordset Transactions with Bound Controls</A>
<UL>
<LI>
<A HREF="#E70E74" >Using Edit Boxes for Data Entry</A></UL>
<LI>
<A HREF="#E69E210" >Using Visual C++ Code for Transaction Processing</A>
<UL>
<LI>
<A HREF="#E70E75" >Emulating an Access Continuous Subform in Visual C++</A>
<LI>
<A HREF="#E70E76" >Adding Update Capability</A>
<LI>
<A HREF="#E70E77" >Using an OLE Grid Control</A></UL></UL>
<LI>
<A HREF="#E68E81" >Processing Multitable Transactions</A>
<UL>
<LI>
<A HREF="#E69E211" >Maintaining Transactional Consistency with Visual C++ Code</A>
<UL>
<LI>
<A HREF="#E70E78" >The Structure of the Transaction Instructions</A>
<LI>
<A HREF="#E70E79" >The Applicability of Visual C++ Transaction Instructions</A></UL></UL>
<LI>
<A HREF="#E68E82" >Summary</A></UL></UL></UL>
<HR ALIGN=CENTER>
<A NAME="E66E39"></A>
<H1 ALIGN=CENTER>
<CENTER>
<FONT SIZE=6 COLOR="#FF0000"><B>- 15 -</B>
<BR><B>Designing Online Transaction-Processing Applications</B></FONT></CENTER></H1>
<BR>
<P>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.
<BR>
<P>You update data with Visual C++ applications that are easily developed using AppWizard. Updating is done with SQL statements that Microsoft calls <I>action queries</I>. Semantically, the term <I>query</I> 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 <I>action query</I> 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.
<BR>
<P>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.
<BR>
<BR>
<A NAME="E68E79"></A>
<H3 ALIGN=CENTER>
<CENTER>
<FONT SIZE=5 COLOR="#FF0000"><B>Categorizing Transaction-Processing Applications</B></FONT></CENTER></H3>
<BR>
<P>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.
<BR>
<P>The majority of transaction-processing applications fall into one of these three categories or subclasses:
<BR>
<UL>
<LI>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.
<BR>
<BR>
<LI>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.
<BR>
<BR>
<LI>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.
<BR>
<BR>
</UL>
<P>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 <I>distributed OLTP</I> (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.
<BR>
<P>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.
<BR>
<BR>
<A NAME="E68E80"></A>
<H3 ALIGN=CENTER>
<CENTER>
<FONT SIZE=5 COLOR="#FF0000"><B>Using SQL Statements and Recordsets for Transaction Processing</B></FONT></CENTER></H3>
<BR>
<P>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.
<BR>
<BR>
<A NAME="E69E207"></A>
<H4 ALIGN=CENTER>
<CENTER>
<FONT SIZE=4 COLOR="#FF0000"><B>Determining When You Can Update Joined Tables</B></FONT></CENTER></H4>
<BR>
<P>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.
<BR>
<BR>
<A NAME="E69E208"></A>
<H4 ALIGN=CENTER>
<CENTER>
<FONT SIZE=4 COLOR="#FF0000"><B>SQL and Set-Oriented Transactions</B></FONT></CENTER></H4>
<BR>
<P>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.
<BR>
<BR>
<A NAME="E70E72"></A>
<H5 ALIGN=CENTER>
<CENTER>
<FONT SIZE=4 COLOR="#FF0000"><B>A Review of SQL Action Query Syntax</B></FONT></CENTER></H5>
<BR>
<P>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.
<BR>
<UL>
<LI>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.)
<BR>
<BR>
<LI>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 <I>unit of work</I> to define a transaction and use COMMIT WORK and ROLLBACK WORK to commit or roll back a unit of work. A <I>unit of work</I> 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.
<BR>
<BR>
<LI>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.
<BR>
<BR>
<LI>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.
<BR>
<BR>
</UL>
<P>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.
<BR>
<BLOCKQUOTE>
<BLOCKQUOTE>
<HR ALIGN=CENTER>
<BR>
<NOTE><B>NOTE</B>
<BR>
<BR>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++.</NOTE>
<BR>
<HR ALIGN=CENTER>
</BLOCKQUOTE></BLOCKQUOTE>
<BR>
<A NAME="E70E73"></A>
<H5 ALIGN=CENTER>
<CENTER>
<FONT SIZE=4 COLOR="#FF0000"><B>Executing SQL Action Queries with Visual C++ Code</B></FONT></CENTER></H5>
<BR>
<P>You execute action queries by using one of two Visual C++ methods:
<BR>
<UL>
<LI>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.
<BR>
<BR>
<LI>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.
<BR>
<BR>
</UL>
<P>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.
<BR>
<BLOCKQUOTE>
<BLOCKQUOTE>
<HR ALIGN=CENTER>
<BR>
<NOTE><B>TIP</B>
<BR>
<BR>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.</NOTE>
<BR>
<HR ALIGN=CENTER>
</BLOCKQUOTE></BLOCKQUOTE>
<BR>
<A NAME="E69E209"></A>
<H4 ALIGN=CENTER>
<CENTER>
<FONT SIZE=4 COLOR="#FF0000"><B>Recordset Transactions with Bound Controls</B></FONT></CENTER></H4>
<BR>
<P>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.
<BR>
<BR>
<A NAME="E70E74"></A>
<H5 ALIGN=CENTER>
<CENTER>
<FONT SIZE=4 COLOR="#FF0000"><B>Using Edit Boxes for Data Entry</B></FONT></CENTER></H5>
<BR>
<P>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).
<BR>
<BR>
<A NAME="E69E210"></A>
<H4 ALIGN=CENTER>
<CENTER>
<FONT SIZE=4 COLOR="#FF0000"><B>Using Visual C++ Code for Transaction Processing</B></FONT></CENTER></H4>
<BR>
<P>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:
<BR>
<UL>
<LI>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.
<BR>
<BR>
<LI>You can validate all the entries at once when the user clicks the OK button (or presses an Alt-<I>key</I> 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.
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -