vcg01.htm

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

<LI>Dynaset-level locking locks all of the tables that are used by the Dynaset object. A Dynaset, a unique feature of Visual C++ and Access, is an updatable view of a database. Dynaset-level locking is available for all database types. To resolve consistency problems at the expense of concurrency, you can deny others the capability to read data with the Dynaset object's Options property.

<BR>

<BR>

<LI>Record-level locking is used for databases whose tables have fixed-length records, such as dBASE, FoxPro, and Paradox. Record-level locking provides the highest level of concurrency. You open the table file for shared use to employ record-level locking.

<BR>

<BR>

<LI>Page-level locking is used for Access and most client-server databases that use variable-length records. Access databases, for example, lock 2,048-byte pages. Thus, locking a single page also can lock many contiguous records if each record contains only a small amount of data. Page-level locking usually results in a lower level of concurrency than record locking.

<BR>

<BR>

</UL>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>If you write a database program that appears unable to access a record because it's locked, but your application doesn't have that record locked, it's possible that the database page is locked by another application and that your program is actually functioning correctly.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<UL>

<LI>Pessimistic locking applies only to record-level and page-level locking. Pessimistic locking locks the page(s) or record(s) as soon as a user employs the Edit or BeginTrans method and doesn't release the lock until the Update or CommitTrans method completes the edit, or until the edit is canceled with the Rollback method. Pessimistic locking is Visual C++'s default locking method that guarantees that your edit will succeed.

<BR>

<BR>

<LI>Optimistic locking also is restricted to record-level and page-level locking. Optimistic locking places locks on the record or page only during the time that it takes for the Update or CommitTrans method to execute. Optimistic locking offers a greater degree of concurrency, but you can't be assured which of two simultaneous edits will prevail.

<BR>

<BR>

</UL>

<P>When you use a client-server RDBMS, the server back end usually handles the page-level locking process for you. The majority of client-server RDBMSs let you specify the level of locking and the page-level locking method to be employed through SQL keywords such as SQL Server's HOLDLOCK instruction. You need to use the SQL pass-through option when you want to use SQL reserved words that aren't included in Access SQL. SQL pass-through is discussed in the section &quot;Client-Server RDBMSs&quot; later in this chapter.

<BR>

<P>The Access database engine can create and maintain indexes for each database type that the engine supports. You need a primary key index in order to update data contained in Paradox and client-server database tables. (Visual C++ doesn't use or maintain Paradox secondary or query speed-up indexes that are created on more than one column or that are designated as unique.) It's good database-programming practice to create indexes on the primary key field(s) of all of the tables in your database. (Visual C++, however, doesn't recognize indexes on primary key fields of dBASE or Btrieve tables as PrimaryKey indexes.) Adding indexes on the foreign key fields of related tables speeds queries that involve more than one table.

<BR>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>Visual C++'s ODBC drivers can neither read nor maintain the .NTX index files created for .DBF files by CA-Clipper applications. Intersolv Software offers an ODBC driver that can read and update CA-Clipper .NTX indexes. If you want to use Visual C++ front ends concurrently with CA-Clipper DOS applications, you need to use the Intersolv ODBC driver to convert all the database indexes to dBASE-compatible index file formats.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<P>As you add more indexes to your tables, the speed of transaction processing operations decreases when you update the data values contained in the indexed fields. Thus, the number of indexes you create for a table depends on whether the table is used primarily for decision-support or transaction-processing applications. Choosing the right index structure is discussed in Chapter 4.

<BR>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>Multiple indexes drastically slow the importation of data from unsupported file types, such as delimited text files, to your existing tables.

<BR>

<BR>When you import data, you might find it much faster to create a new table to hold the imported data, then index the new table and append the data from the new table to the existing table.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<BR>

<A NAME="E68E17"></A>

<H3 ALIGN=CENTER>

<CENTER>

<FONT SIZE=5 COLOR="#FF0000"><B>Categorizing Database Management Systems for Visual C++</B></FONT></CENTER></H3>

<BR>

<P>You can write Visual C++ front ends for a variety of types of database management systems. In fact, if you use the ODBC drivers and write SQL statements that use reserved words included in the ODBC Core-level SQL grammar, it's likely that you can create a single application that will perform satisfactorily with virtually any of the more commonly used relational database management systems. The ODBC Core-level SQL grammar is a subset of ANSI SQL-89 and is specified in Microsoft's Programmer's Reference for the Microsoft Open Database Connectivity Software Development Kit (ODBC SDK). The Microsoft ODBC Desktop Database Drivers Kit supports Core-level SQL grammar, as do the SQL Server and Oracle ODBC drivers supplied with Visual C++, and the ODBC drivers supplied by Intersolv Software. Chapter 5 includes a list of the SQL reserved words included in the Basic-level, Core-level, and Extended-level SQL grammars of ODBC.

<BR>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>The 32-bit ODBC driver for Microsoft SQL Server also can be used with Sybase SQL Server and Sybase System 10, but the driver isn't supported by Microsoft for use with Sybase RDBMSs. When used with Sybase products, some features of Sybase System 10 aren't available when using the Microsoft driver.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<P>The following sections describe the four basic categories of database management systems you can use with your Visual C++ database applications.

<BR>

<BR>

<A NAME="E69E27"></A>

<H4 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Traditional Desktop RDBMSs</B></FONT></CENTER></H4>

<BR>

<P>Traditional desktop RDBMSs, typified by dBASE and Paradox, use separate files for each table and index, or collection of indexes for a single table in the case of dBASE IV and later .MDX and FoxPro .CDX indexes. dBASE and Paradox tables use fixed-width (also called fixed-length) records. You specify the maximum size of each field of the Character data type. Data values shorter than the maximum size automatically are padded with blanks (spaces) to the maximum size of the field. Btrieve tables provide for variable-length character fields. Variable-length character fields can save a substantial amount of disk space if the length of data values in character fields varies greatly.

<BR>

<P>The Visual C++ documentation defines a database comprising traditional desktop RDBMS table and index files as an <I>external database</I>. This book doesn't use the term <I>external database</I> because no complementary internal database is defined in Visual C++. The dBASE, FoxPro, Paradox, or Btrieve database is specified as the <I>well-formed path</I> to the directory that contains the table and index files that you need for your application. A well-formed path, also called a <I>fully-qualified path</I>, consists of the drive designator and the path to the folder that contains the table and index files, such as C:\VBDBS\DBASE. If your tables are stored on a file server (such as Windows NT or Windows 95) that uses the Uniform Naming Convention (UNC), you substitute the server name for the drive identifier, as in \\SERVER\VBDBS\DBASE.

<BR>

<P>You specify the indexes to be used with each of the tables in individual .INF files located in the same directory. The filename of the .INF file is the same as the table file. Thus, the information file for CUSTOMER.DBF is named CUSTOMER.INF. If you use dBASE IV multiple-index files, only one entry is required: NDX1=CUSTOMER.MDX. For dBASE III+ indexes, the index files are identified by sequentially numbered entries, such as NDX1=CUSTNAME.NDX, NDX2=CUSTZIP.NDX, and so on, with each entry on a separate line. You need .INF files for dBASE III+, dBASE IV, and FoxPro files, but not for Paradox or Btrieve fields. When you create a dBASE or FoxPro table and specify an index, Visual C++ automatically creates the .INF files for you. To use existing .MDX or .NDX index files with your .DBF file, you need to use Windows Notepad or another text editor to create the .INF file.

<BR>

<P>Btrieve's data definition file, FILES.DDF, serves the same purpose as the .INF file. Access can't create the FILES.DDF file for Btrieve databases. You need Xtrieve or a third-party Btrieve utility program to create the necessary Btrieve data definition file. Other requirements for the creation of Btrieve files are discussed in Chapter 6.

<BR>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>The Access database engine doesn't have the capability to remove deleted records from dBASE and FoxPro table files. You need an application that supports the xBase PACK statement to eliminate deleted records and recover the fixed disk space that the deleted records consume.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<P>FoxPro and dBASE III+/IV memo files that are associated with database tables must be stored in the same directory as the table that contains a Memo field data type. If the associated memo file is missing or corrupted, you receive an error message from Visual C++ when you attempt to open the table file. With dBASE 5/Visual dBASE databases, you also have OLEOBJECT data types, which are stored externally from the main database file(s).

<BR>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>It's good database-programming practice to place all the table, memo, and index files you need for your application in a single database directory. Some xBase applications, such as accounting products, require that groups of files be stored in different directories. Visual C++ lets you open more than one database at a time; thus, you can deal with table, memo, and index files that are located in more than one directory.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<P>The manipulation of data in the table files and the maintenance of the indexes of traditional desktop databases are the responsibility of the database application. The application translates high-level statements, such as SQL's SELECT or dBASE's LIST expressions, into low-level instructions that deal directly with records in the table files. If you run queries from a workstation against large table files that are located on a network file server, a very large number of low-level instructions are sent across the network to the file server. When a large number of users attempt to run queries simultaneously, to the same or other tables on the server, performance can suffer dramatically because of network congestion.

<BR>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>There is no equivalent in Visual C++ to the record number associated with traditional RDBMS tables. Microsoft makes the valid point that record numbers are meaningless in SQL databases. (However, Access assigns record numbers to tables and query results that Access displays in datasheet mode.)</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<BR>

<A NAME="E69E28"></A>

<H4 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Client-Server RDBMSs</B></FONT></CENTER></H4>

<BR>

<P>The term <I>front end</I> originally appeared in conjunction with client-server RDBMS applications. <I>Front end</I> refers to the client application that runs on a workstation connected to the server (back end) on a local area network (LAN) or wide area network (WAN). The rapid growth of the client-server database market in the 1990s is because users of mainframe and minicomputer database management systems want to downsize their information systems. <I>Downsizing</I> means substituting relatively low-cost file servers, most often based on PC architecture, for expensive mainframe and minicomputer hardware and database software products that are costly to maintain. Today's trend is toward distributed client-server systems. In <I>distributed</I> database systems, tables that contain the data to satisfy a query might be located on several different servers in widely varying locations that are connected by a WAN.

<BR>

<P>The operating system for the server portion of the client-server RDBMS need not be (and often is not) the same as the operating system used by the client workstations. For example, Microsoft SQL Server 6 runs under Windows NT Server, and Sybase SQL Server runs under UNIX on minicomputers or as a NetWare Loadable Module (NLM) on Novell PC file servers. However, it's likely that the majority of both Microsoft and Sybase SQ