vcg01.htm

Visual C++与数据库的连接经典实例

<BR>

<BR>

<PRE>

<FONT COLOR="#000080">USE customer LIST name, address, city, state, zip_code FOR zip_code &gt;= 90000</FONT></PRE>

<P>and the single SQL statement contained in a Visual C++ string variable:

<BR>

<BR>

<PRE>

<FONT COLOR="#000080">SELECT name, address, city, state, zip_code FROM customer WHERE zip_code &gt;= 90000</FONT></PRE>

<P>Both return the same result: a list of the names, addresses, cities, states, and zip codes of all customers whose zip codes are equal to or greater than 90000.

<BR>

<P>Most of the recent implementations of desktop RDBMSs include SQL implementations that have varying degrees of conformance to the ANSI SQL-89 specification. Access's dialect of SQL conforms quite closely to ANSI-89 syntax, but it's missing the Data Definition Language (DDL) elements of SQL that you need to create databases and tables with conventional SQL statements. Access SQL also omits the Data Control Language (DCL) that lets you GRANT or REVOKE privileges for users to gain access to the database or the tables it contains. Access SQL compensates for this omission, at least in part, by providing the TRANSFORM and PIVOT keywords that let you create very useful crosstab queries (which are described in a moment). Chapter 5, &quot;Learning Structured Query Language,&quot; describes the structure of SQL statements and how to implement SQL in your Visual C++ code.

<BR>

<BR>

<A NAME="E69E26"></A>

<H4 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Classifying Database Front-End Applications</B></FONT></CENTER></H4>

<BR>

<P>Database front-end applications that you create with front-end generators fall into two broad categories:

<BR>

<UL>

<LI>Decision-support applications that only let you display and print information culled from the database by predefined (hard-coded) or user-defined queries

<BR>

<BR>

<LI>Transaction-processing front-end applications that include the capability to edit data or add data to the database

<BR>

<BR>

</UL>

<P>The following sections describe the basic characteristics of these two categories of database front ends.

<BR>

<BR>

<A NAME="E70E1"></A>

<H5 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Database Front Ends for Decision Support</B></FONT></CENTER></H5>

<BR>

<P>Decision-support applications represent the most common type of database front-end application. Single-purpose decision-support front ends typically display sales information for selected customers or products. At the other end of the decision-support spectrum, complex management information systems (MIS) provide summarized information concerning virtually all of the quantifiable aspects of large organizations' operations. Decision-support applications usually involve read-only access to the data in the underlying database. Chapter 9, &quot;Designing a Decision-Support Application,&quot; is devoted to writing Visual C++ code to display information gleaned from relational databases.

<BR>

<P>Many decision-support front-end development tools include the capability to create graphs and charts based on summary data. Grouping and totaling data to create summary information often is called <I>rolling up</I> the data. The Access database engine lets Visual C++ decision-support applications perform crosstab rollups. Crosstab methods let you display summary data in worksheet format, usually as a time series. Using a crosstab query, you can display sales of products (in rows) by month or by quarter (in columns) directly from tables that contain only raw invoicing data. Crosstab queries is one of the subjects of Chapter 8, &quot;Running Crosstab and Action Queries.&quot; Drill-down methods let you show the detailed data that underlies your summary information.

<BR>

<P>In-house and independent database-application developers use Visual C++ to create a wide variety of single-purpose and MIS decision-support front ends. Here are the principal advantages of Visual C++ over competing front-end development tools for creating decision-support applications:

<BR>

<UL>

<LI>You can distribute unlimited numbers of your compiled Visual C++ front-end applications without paying royalties. Most other front-end generators require that you pay a license fee for each copy of the compiled front-end applications you install. (License fees for applications you create are called <I>per-seat</I> charges.) However, you might need to pay a per-seat license fee for the ODBC drivers you use with your Visual C++ application if you need to use drivers other than those supplied with Visual C++.

<BR>

<BR>

<LI>The purchase price of Visual C++ is substantially less than the prices charged for other front-end generators with comparable or inferior feature sets.

<BR>

<BR>

<LI>Few front-end generators support the Access SQL TRANSFORM and PIVOT statements, which let you quickly create crosstab queries when you use the Microsoft Jet database engine.

<BR>

<BR>

<LI>Visual C++ applications can embed OLE graphic objects from applications such as Microsoft Graph.

<BR>

<BR>

<LI>Visual C++ is OLE compliant (as a destination or client application and as a server application) and includes OLE Automation capability. You can use <I>in-situ editing</I> (also called <I>in-place editing</I>) and exercise control over other Windows applications that share OLE Automation capability.

<BR>

<BR>

<LI>You don't need to learn a new and arcane programming language to develop Visual C++ database front ends. The structure and syntax of Visual C++ is closely related to traditional database programming languages such as xBase and the Paradox Application Language (PAL).

<BR>

<BR>

<LI>Visual C++ is flexible. Often, constructs that are difficult or impossible using other development platforms can easily be created using Visual C++.

<BR>

<BR>

<LI>Visual C++ is an object-oriented language. Visual C++ qualifies as a full-scale, object-oriented programming (OOP) language. It's likely that future versions will include an even more extensive implementation of MFC. Competing front-end development tools that claim to be object-oriented seldom reach Visual C++'s level of compliance with the object programming model.

<BR>

<BR>

</UL>

<P>Many of the advantages in the preceding list apply equally to decision-support and transaction-processing front ends. This list is by no means comprehensive. Many other advantages that derive from choosing Visual C++ as your database front-end development tool will become apparent as you progress through this book.

<BR>

<P>Here are the principal drawbacks to using Visual C++ as a decision-support front end:

<BR>

<UL>

<LI>Visual C++ has limited support for graphics formats in image and picture boxes. Visual C++ supports only Windows bitmaps (.BMP and .DIB), icons (.ICO), and Windows metafile (.WMF) vector images. However, a variety of third-party add-ins and custom controls are available that dynamically convert .PCX, .TIF, .JPG, .GIF, and other common graphics file formats to one of Visual C++'s supported formats. It can be expected that there will be many OLE Custom Controls for graphics available to Visual C++ programmers in the next few years.

<BR>

<BR>

<LI>With ODBC, Visual C++ lacks the direct capability to establish rules that enforce referential integrity at the database level with Access .MDB files, and ODBC can't add validation rules to enforce domain integrity at the Access table level. You need to write Visual C++ code to enforce referential and domain integrity in all supported databases when using ODBC. Visual C++ enforces referential and domain integrity rules that you establish when you create the database with Access. Many Visual C++ 4 front-end applications will use DAO for accessing Access databases.

<BR>

<BR>

<LI>Visual C++ can't directly implement the security features inherent in Access .MDB databases when using ODBC. By default, Visual C++ doesn't use Access's SYSTEM.MDA file (or Access 7's SYSTEM.MDW file), which contains user names, passwords, and other security information for .MDB files created by Access. If your front-end application is used by members of only one workgroup, you can specify the name and location of the workgroup's SYSTEM.MDA/MDW file in Visual C++'s VB.INI file or the Visual C++ <I>APPNAME</I>.INI file associated with the <I>APPNAME</I>.EXE file for your application. (Visual C++ expects the filename of the .EXE and .INI files to be the same.) If you have implemented or need to implement security features, such as adding new users to your Access database, you can use the Access ODBC driver (RED110.DLL). This lets you attach a SYSTEM.MDA/MDW file to implement database security instead of using a Visual C++ database object to connect directly to the Access database engine. Use the GRANT and REVOKE SQL reserved words to manage database- and table-level security. These limits don't apply to applications developed using the MFC DAO classes, however.

<BR>

<BR>

</UL>

<P>The limitations of Visual C++ are likely to affect only a small portion of the decision-support front ends you create for production-database applications. Future versions of Visual C++ probably will include an equivalent to Access's OLE object frame controls.

<BR>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>Unlike earlier versions of Visual C++, the Visual C++ 4.0 product includes a redistributable copy of the Access Jet database engine. The Jet engine is used by the DAO functionality of Visual C++ 4.0. This version of the Microsoft Jet database engine is 32-bit only and doesn't support 16-bit applications. There is no 16-bit version of the Microsoft Jet database engine for Visual C++.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<BR>

<A NAME="E70E2"></A>

<H5 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Transaction-Processing Applications</B></FONT></CENTER></H5>

<BR>

<P>Front ends for transaction processing let users update the tables of databases. Transaction processing involves editing or deleting existing records in the database tables or adding new records to the tables. Thus, users of transaction-processing applications need read-write access to the tables they want to modify. Transaction-processing applications require that either the database itself or your Visual C++ code preserve the integrity (related to accuracy) of the data. Enforcing domain (data value) integrity and referential (record) integrity in databases that users can update is covered in Chapter 4, &quot;Optimizing the Design of Relational Databases.&quot;

<BR>

<P>Transaction processing implies the capability of using the SQL reserved words COMMIT and ROLLBACK to execute or cancel pending changes to the tables, respectively. All modern client-server databases support COMMIT and ROLLBACK transaction processing, but only a few desktop databases incorporate native transaction-processing capabilities. Access databases, for example, support transaction processing internally, whereas dBASE databases do not. Visual C++ supports transaction processing with the functions SQLPrepare(), SQLTransact(), and the keywords SQL_COMMIT and SQL ROLLBACK. Chapter 15, &quot;Designing Online Transaction-Processing Applications,&quot; shows you how to use Visual C++'s transaction-processing keywords to speed updates to RDBMS tables.

<BR>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>ODBC drivers can provide transaction-processing capability for databases that don't ordinarily support SQL COMMIT/ SQL ROLLBACK transaction processing. Intersolv's dBASE ODBC driver, for example, lets you use SQL COMMIT or SQL ROLLBACK in your call to SQLTransact() that operates on dBASE tables.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<P>In a multiuser environment, transaction-processing front ends must maintain database consistency and concurrency. Simplified descriptions of these two terms follow:

<BR>

<UL>

<LI><I>Consistency</I> problems occur when the first user executes a transaction that updates a set of records and a second user attempts to view the records while the transaction is in process. Depending on the level of localization provided by the database management system, the second user might see the wrong data (called a <I>dirty read</I>), the wrong data followed by the right data (a <I>nonrepeatable read</I>), or erroneous data that results from the first user's transactions, which alter the rows that are included in the result of the second user's query (called <I>phantom data</I>).

<BR>

<BR>

<LI><I>Concurrency</I> problems result when two or more users attempt to update the same record simultaneously. Unless a method is provided of locking the values of data in a record until the first user's transaction completes, you can't predict which user's update will prevail. Database, table, page, and/or record locking are provided by most database management systems to overcome concurrency problems. Locking the entire database or one or more tables during a transaction is seldom a satisfactory method in a multiuser environment because of the lock's affect on other users. Page or record locking, however, can result in a condition called <I>deadlock,</I> in which two users attempt to execute transactions on the same records in a set of two or more tables. Client-server database management systems use a variety of methods to detect and overcome deadlock conditions. If you're using a desktop RDBMS, you usually need to write your own anti-deadlock code in Visual C++.

<BR>

<BR>

</UL>

<P>Both consistency and concurrency issues can be controlled by the locking methods employed in multiuser environments. Visual C++ supports the following locking methods:

<BR>

<UL>

<LI>Database-level locking for client-server and Access .MDB databases, in which your application opens the database for exclusive rather than shared use. Database-level locking ordinarily is used only when you alter the structure of a database or when you compact or repair an Access database.

<BR>

<BR>

<LI>Table-level locking is available for all database types. A table lock opens a dBASE, Paradox, or Btrieve file for exclusive use. You open Access and client-server databases for shared use and then open the table for exclusive use. You can prevent consistency problems by setting the value of the Options property of the table to deny other users the capability to read the values in the table while it's locked.

<BR>

<BR>