***end note***

Visual C++ is Microsoft's most extensive and powerful programming language. Microsoft's original objective for Visual C++ was to provide a powerful and flexible platform that programmers could use to create their own Windows applications while running under Windows. Microsoft achieved this goal with Visual C++ 1.0. Many experienced programmers abandoned DOS-based C, C++, and Pascal in favor of Visual C++ because they could develop Windows applications faster than with traditional programming languages while working with Windows' graphical interface. Microsoft enriched Visual C++ 1.5 with improvements in the interface and extensions to the MFC C++ libraries, while Visual C++ 2.x moved programmers into the 32-bit application world. Visual C++ 4.0 moves the programmer interface, class library, and feature set to a new high. With the introduction of Visual C++ 4.0, a new set of database features has been added. Visual C++ 4.0 supports DAO (Data Access Objects) in addition to ODBC and also greatly extends other support, such as the addition of container support for OLE Custom Controls. Independent firms have created a variety of utilities, libraries, and add-on features for Visual C++, the majority of which addressed database applications. There will be, in the very near future, a plethora of new OLE Custom Controls for database programmers.

By early 1993, a Microsoft market study showed that more than 70 percent of Windows applications involved databases in one form or another. In October of 1995, Microsoft's Visual C++ product manager noted in a speech in Boston that between 40 and 60 percent of all Visual C++ applications were database oriented. Visual C++ can be expected to also be a popular database applications development tool. Even before the introduction of Visual C++ 4.0, with its data access objects (CRecordset, CDatabase, and CRecordView) that greatly enhance database functionality, C and C++ were major but unrecognized players in the Windows database market. The introduction of Visual C++ 4.0 has now pushed Visual C++ to be a strong competitor to Visual Basic in the database development platform arena. The failure of market research firms to place Visual C++ in the Windows database category caused significant distortion of the desktop database market statistics for 1993 and later.

This chapter describes Visual C++'s role in database application development and shows how Visual C++, OLE (Object Linking and Embedding) automation, ODBC (Open Database Connectivity), DAO, and MFC fit into Microsoft's strategy to maintain its domination of the Windows application marketplace. This chapter also discusses the advantages and drawbacks of using Visual C++ for database applications and gives you a preview of many of the subjects that are covered in detail in the remaining chapters of this book.

It's becoming a 32-bit, "Designed for Windows 95" world out there, so this book concentrates on 32-bit application development with Visual C++ 4.0.

(c)Choosing Visual C++ as Your Database Development Platform

Visual C++ now includes the database connectivity and data handling features that qualify the language as a full-fledged database application development environment. The new data access features that Microsoft added to Visual C++ position the product as a direct competitor to Visual Basic and make Visual C++'s support of Access, FoxPro, and Paradox for Windows in the desktop database market even more complete. Visual C++'s primary advantages over its database competitors are simplicity, flexibility, and extensibility:

The most important benefit of selecting Visual C++ as your primary database development platform, however, isn't evident in Microsoft's feature list. There is a vast array of tools and support for ODBC and database development with Visual C++ today. Examples of the use of Visual C++ are found throughout this book.

Another reason for choosing Visual C++ for database application development is its OLE compatibility. At the time this book was written, Visual C++ was the best database development environment that incorporated OLE.

OLE automation is likely to be the most significant OLE feature for the majority of Visual C++ database developers. OLE automation lets you control the operation of other OLE-compliant server applications from within your Visual C++ database application. Applications need not include Visual Basic for Applications to act as OLE automation source applications (servers); Word for Windows 6 and later supports OLE automation using the conventional Word Basic macro language syntax.

The Windows database war wasn't over at the time this book was written (heck, it may never be over), but Microsoft's multipronged attack with Visual Basic, Access, Visual FoxPro, SQL Server, and Visual C++ is forcing competing publishers of desktop database managers into their defensive trenches. As a group, Microsoft's database applications for Windows, together with ancillary products such as ODBC, DAO, and the Access Jet database engine, have a breadth and depth that no other software publisher presently can match.

(c)Using Visual C++ as a Database Front End

All of Microsoft's 32-bit mainstream Windows productivity applications and programming languages presently support or soon will accommodate the 32-bit Microsoft Jet 3.0 database engine. The Jet database engine is a software component that adheres to Microsoft's Component Object Model (COM) architecture. COM is Microsoft's infrastructure for creating code modules (called objects) that are independent of programming languages and computer platforms. COM defines a set of interfaces that applications must support; OLE 2+ is a high-level implementation of COM designed for sharing objects between Windows applications and for application programming. OLE 2+ defines its own set of interfaces to permit use of one program's objects by another program. As an example, applications that support VBA, such as Visual Basic 4.0, Access 95, Excel 95, and Project 95, communicate with the Jet 3.0 database engine (Msjt3032.dll) through the Microsoft Jet 3.0 Data Access Object (DAO), Dao3032.dll, an in-process OLE Automation server. (Access 95 also directly calls functions in Msjt3032.dll). Thus, only a single copy of the Jet 3.0 DLLs is required in your \Windows\System folder; each application creates its own instance of Jet as needed. A similar approach is used to share other components, such as DAO, spell-checking, graphing, and VBA 2.0; the component files are located in subfolders of your \Windows\Program Files\Common Files\Microsoft Shared folder if you're running Windows 95.

Visual C++ is the wildcard in the desktop database deck. Visual C++ doesn't really have a native database structure. All four of the basic database types supported by Visual C++ are treated the same. Microsoft Access is simply the default database type. Visual C++ and its Access database engine, combined with Microsoft or third-party ODBC drivers, presently can connect to the relational database management systems (RDBMS) listed in Table 1.1.

Table 1.1. Visual C++-compatible databases and file drivers.

Access Database Microsoft Third-Party
Engine Drivers ODBC Drivers ODBC Drivers

Access (.MDB) Microsoft SQL Server Digital Rdb

Btrieve (.DAT) Oracle 6 Gupta SQLBase

dBASE III+ (.DBF, .NDX) Sybase SQL Server HP AllBase/SQL

dBASE IV (.DBF, .MDX) Excel (.XLS)^* HP Image/SQL

FoxPro (.DBF, .CDX, .IDX) Text (.TXT)^* IBM DB2, DB2/2

Paradox (.DB, .PX) Access^* IBM OS/2 DBM

Btrieve^* IBM SQL/DS

dBASE III+^* Informix

dBASE IV^* Ingres

FoxPro 2.x^* NCR Teradata

Paradox^* NetWare SQL

Progress

Tandem Nonstop SQL

Watcom SQL

XDB

***Begin Note***

***End Note***

The Access database engine that is included with ODBC lets you use dBASE III+, dBASE IV, FoxPro, Paradox, Btrieve, and Access databases with equal facility. Microsoft's ODBC Administrator application and the ODBC drivers created by Microsoft and third-party developers add at least 20 additional databases and file types to the list of those with which a Visual C++ database application can be connected. Only Access can rival Visual C++'s universal database connectivity. Details of the two methods of adding database functionality to Visual C++ applications are given in Chapter 6, "The Microsoft Jet Database Engine."

***Begin Note***

***End Note***

Borland's Paradox 7 for Windows 95 takes a tentative step in the multidatabase direction by letting you use Paradox or dBASE files interchangeably. If you want to, you can create a FoxPro or Paradox application that doesn't involve a single database file. However, you need to open an .MDB file to use Access; only a few Access database utility functions are available before you open a new or existing .MDB file.

Included with Microsoft Office and the 16-bit versions of Visual C++ is an add-in application, MS Query, that lets you create new Access databases as well as add, delete, and modify tables in new or existing Access, dBASE, FoxPro, and Paradox databases. Figure 1.1 shows MS Query's Table window for the Orders table of NorthWind.MDB, the sample Access database supplied with Access.

***01VCG01***

Figure 1.1. Visual C++'s data manager application, MS Query.

***Begin Note***

***End Note***

The Table window displays the structure of the existing fields of a table and lets you add new fields and indexes to a table. MS Query is an example of a Visual C++ database application that uses MDI forms. MDI lets you create database applications with several windows (called MDI child windows or forms) that are contained within a conventional window (called the parent window or form).

The Microsoft ODBC Administrator application, included with Visual C++, lets you connect to the Microsoft and Sybase versions of SQL Server and to Oracle client-server relational database management systems. Client-server RDBMSs are discussed later in this chapter. You can even treat text files and Excel worksheets as database tables by using the Microsoft ODBC Desktop Database Drivers kit. Independent software development firms, such as Intersolv Software, provide a variety of ODBC drivers for client-server and desktop databases, as well as for worksheet and text files. Some of Intersolv's ODBC drivers provide features that aren't available when you use the Access database engine; an example is Intersolv's capability to employ transactions with dBASE III+, IV, and 5.0 files. Figure 1.2 shows the ODBC Setup window for the Pubs sample database of Microsoft SQL Server 4.2 running on LAN Manager 2.2. Using ODBC drivers with Visual C++ is the subject of later sections in this chapter and, in fact, the entire book. The Intersolv DataDirect ODBC pack supports ALLBASE, Btrieve, CA-Ingres, Clipper, DB2, DB2/2, DB2/6000, dBASE, Excel, FoxBase, FoxPro, Gupta SQLBase, IMAGE/SQL, INFORMIX, InterBase, Microsoft SQL Server, Oracle, Paradox, PROGRESS, Scalable SQL (formerly Netware SQL), SQL/400, SQL/DS, SYBASE System 10, SYBASE SQL Server 4, Teradata, text files, and XDB.

***01VCG02***

Figure 1.2. The ODBC Setup window for the Microsoft SQL Server.

***Begin Note***

***End Note***

Visual C++'s broad spectrum of database connectivity makes Visual C++ an excellent candidate for developing database front-end applications. The term database front end is used to describe a computer application that can select items of data contained in a database and display the chosen data items as information that is meaningful to the user. The database system itself is called the back end. The back-end database is, at the minimum, a collection of related tables. Traditional desktop database managers store these related tables as individual files in a single directory, together with index files that are used to speed the data-gathering process. Access and client-server RDBMS store all related tables and indexes in a single database file.

Microsoft has achieved dramatic success in making the Windows graphical user interface (GUI) a worldwide standard for use on corporate PCs. At the time this book was written, Microsoft claimed to have sold more than 25 million copies of Windows 3.x. Windows 95 earned Microsoft more than $260 million in the quarter when it was released and more than $180 million in the following quarter.

Windows 95 was released in August of 1995. Even in early 1995, Windows 95 had garnered enormous attention. Thus, it's no surprise that virtually all of today's database front ends are being created to run under Windows 95, or Windows NT. With Visual C++ and Access, Microsoft also has the upper hand in creating Windows database applications that employ a variety of database structures. Wide-ranging database connectivity is one of the major elements of Microsoft's strategy to obtain a major share of the enterprise-wide computing market.

(d)Database Front-End Generators

This book uses the term front-end generator to describe a Windows application with which you can quickly create a database front-end application for a wide variety of desktop and client-server RDBMSs. Theoretically, any programming language that can create executable files for Windows can qualify as a front-end generator. You can write a Windows front end by using Visual Basic, C, C++, or Pascal compilers; and many large-scale MIS applications are written in C or C++. Writing even a simple Windows database front end in Visual Basic, however, requires a major programming effort that fails the "quickly" test. Visual Basic isn't as easy to use as it is sometimes purported to be. Thus, this book restricts the classification of front-end generators to the following two types of products:

More than 200 commercial Windows front-end generators were available at the time this book was written, about evenly divided between the two preceding categories. Most of these products also include a report generator to print formatted data. The retail version of Access uniquely qualifies in both categories of front-end generators because Access's user interface (UI) is simple enough that nonprogrammers can create their own database applications. Presently, Access is one of Visual C++'s most viable competitors in the front-end development tool market, as is Visual Basic.

A critical requirement of any front-end generator is the capability to transfer data to other Windows applications easily. Copying database information to the Windows Clipboard and pasting the Clipboard data into a compatible application, such as Excel, provides basic interapplication or interprocess communication (IPC) capability. Windows DDE (dynamic data exchange) is the most universal method of automatically transferring data to and from database front ends; however, DDE implementations, other than pasted dynamic links, seldom meet the "easily" part of the requirement. Visual C++ currently offers a combination of database connectivity and OLE compatibility.

(d)Visual C++ and SQL

If you aren't proficient in SQL, you probably will need to learn yet another programming language to create database front ends with Visual C++. To select the data you want from a database attached to a Visual C++ application, write the necessary SQL statement and then send the statement as a string variable to the Access database engine or an ODBC driver. SQL (properly pronounced "S-Q-L," not the more common "sequel" or "seekel") is the lingua franca of relational database systems. SQL has its roots in a language called SEQUEL (Structured English Query Language), which IBM developed at its San Jose Research Laboratory in the mid-1970s. SEQUEL later became SEQUEL/2 and ultimately was renamed SQL. The first two relational databases to use SQL were Oracle, developed by Relational Software, Inc. (now Oracle Corporation), and IBM's SQL/DS.

The purpose of SEQUEL and its successors was to provide a relatively simple, nonprocedural programming language to manipulate relational database systems. Visual C++ is a procedural language: You write a series of statements, such as if...else, to instruct the Visual C++ compiler to generate a series of instructions in a sequence you define. You control how the program executes to achieve the result you want. A nonprocedural language, on the other hand, expects you to write a series of statements that describes what you want to happen, such as SELECT * FROM TableName. The application that processes the statement determines how the statement is executed and simply returns the result[md]in this case, all the records contained in TableName.

One of the advantages of using SQL to manipulate relational databases is that the language has been standardized by a committee (X3.135) of the American National Standards Institute (ANSI). The first standardization effort began in the mid-1980s; ANSI X3.135-86 (SQL-86) specified the first standardized version of SQL. The 1986 standard was updated in 1989 (SQL-89) and in 1992 (SQL-92). Developers of RDBMSs that use SQL are free to extend the language in any way they see fit; however, SQL-reserved words that are included in the ANSI standard must return the result specified by the standard. Extended SQL languages[md]such as Transact-SQL, which is used by the Microsoft and Sybase SQL Server RDBMS[md]offer useful extensions to SQL. Some implementations of SQL, such as IBM's version for DB2, don't comply with the latest ANSI standards; for instance, you can't use the AS keyword to assign a derived column name to a DB2 column that contains a value expression, such as SUM(Expr).

***Begin Note***

***End Note***

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

(e)Database Front Ends for Decision Support

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, "Designing a Decision-Support Application," is devoted to writing Visual C++ code to display information gleaned from relational databases.

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 rolling up 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, "Running Crosstab and Action Queries." Drill-down methods let you show the detailed data that underlies your summary information.

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:

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.

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

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.

***Begin Note***

(e)Transaction-Processing Applications

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, "Optimizing the Design of Relational Databases."

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, "Designing Online Transaction-Processing Applications," shows you how to use Visual C++'s transaction-processing keywords to speed updates to RDBMS tables.

***Begin Note***

***End Note***

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

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

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 "Client-Server RDBMSs" later in this chapter.

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.

***Begin Note***

***End Note***

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.

***Begin Note***

(c)Categorizing Database Management Systems for Visual C++

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.

***Begin note***

***End note***

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

(d)Traditional Desktop RDBMSs

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.

The Visual C++ documentation defines a database comprising traditional desktop RDBMS table and index files as an external database. This book doesn't use the term external database because no complementary internal database is defined in Visual C++. The dBASE, FoxPro, Paradox, or Btrieve database is specified as the well-formed path to the directory that contains the table and index files that you need for your application. A well-formed path, also called a fully-qualified path, 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.

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.

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.

***Begin Note***

***End Note***

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).

***Begin Note***

***End Note***

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.

***Begin Note***

(d)Client-Server RDBMSs

The term front end originally appeared in conjunction with client-server RDBMS applications. Front end 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. Downsizing 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 distributed 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.

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 SQL server clients now run under the Windows graphical environment.

Client-server systems differ greatly from desktop database management systems. The primary distinction is that all SQL statements issued by the front-end application are executed by the server. When a workstation sends a conventional SELECT query to the server, only the rows that meet the query's specifications are returned to the workstation. The server is responsible for executing all SQL statements sent to the server by workstations. The server also handles all concurrency and consistency issues, such as locking. If a query issued by a workstation can't be completed by the server, the server returns an error message to the workstation. Combining high-level and low-level instruction processing at the server solves most network congestion issues.

The majority of client-server RDBMSs store all databases in a single, very large file. Where necessary, the file can be divided between server computers, but the divided file is treated as a single file by the server's operating system. Client-server RDBMSs include other sophisticated features, such as the maintenance of transaction logs that let databases be re-created in the event of corruption by a major hardware or software failure. Most client-server products now can use fixed disk arrays and mirrored fixed disks that reduce the likelihood that a failure of part or all of a single fixed disk drive will bring client services to a halt.

The easiest method of connecting your Visual C++ database application to a client-server database is to use the appropriate ODBC driver. This book refers to a client-server database connected through an ODBC driver as a datasource. To open a connection to a datasource, you need to have previously defined the datasource with the ODBC Administrator application that is supplied with the Professional Edition of Visual C++ or another Microsoft application, such as Microsoft Query, that uses ODBC. You need the datasource name (DSN), a valid user login identifier (UID), and a valid password (PWD) to open a client-server datasource as a Visual C++ CDatabase object or to attach tables from the datasource to an open Access database.

***Begin Note***

***End Note***

Although you can use the Access database engine to process queries against client-server databases that you open as a Visual C++ Database object or that you attach to an Access database, using the SQL pass-through option takes better advantage of the client-server environment. When you specify the use of SQL pass-through, the server processes the query and returns a recordset structure that contains the rows returned by the query (if any). The term recordset refers to any database object that contains data in the form of records. You also can use SQL pass-through to execute action queries that append, update, or delete records but don't return a query result set. SQL pass-through lets you execute stored procedures if your client-server database supports stored procedures. (The Microsoft and Sybase versions of SQL Server support stored procedures.) A stored procedure is a compiled version of a standard SQL query that your application uses repeatedly. Stored procedures execute much faster than conventional SQL queries, especially when the query is complex.

Client-server RDBMSs vary widely in purchase price. As a rule, the price of the server software depends on the number of simultaneous workstation connections that the server supports. As with runtime versions of traditional RDBMSs, you purchase copies of the workstation software that are necessary to connect to the server. Microsoft SQL Server is currently the lowest-cost commercial client-server RDBMS available from a major software publisher. You can run Microsoft SQL Server as a service of the Microsoft LAN Manager 2.2 network operating system (NOS), under Novell NetWare, or under Windows NT. Chapter 20, "Creating Front Ends for Client-Server Databases," describes how to use these Microsoft RDBMSs with Visual C++ front ends.

(d)Access: A Nontraditional Desktop RDBMS

Access deserves its own category because Access databases bear little resemblance to traditional desktop database structures. The Microsoft documentation for Visual C++ refers to both "Access databases" and "Visual C++ databases." It's likely that Microsoft intended these two terms to mean "databases created with Access" (which requires a SYSTEM.MDA file for versions of Access prior to 7 and SYSTEM.MDW for Access 7 and later) and "databases created with Visual C++" (which doesn't require SYSTEM.MDA or SYSTEM.MDW), respectively. For consistency, this book uses the term Access database no matter what application is used to create the .MDB file (which contains the actual data).

***Begin note***

***End note***

As mentioned at the beginning of this chapter, Access is the default database type for Visual C++. Microsoft's choice for the default database type is understandable because Access .MDB files have a structure that is proprietary to Microsoft Corporation. Thus, you need to purchase a Microsoft product to use Access database files. All the Microsoft applications that can handle Access database files are Windows applications. It is highly unlikely that Microsoft will publish the intimate details of the Access .MDB file structure as an "open standard," at least in the foreseeable future. Despite the proprietary nature of Access database files, you're likely to find that Access is the database type to select when the choice is yours to make.

Access database files include many of the features of the client-server databases described in the preceding section. Much of the architecture of Access .MDB files is based on the structure of Microsoft SQL Server database files. Here are some similarities between Access and client-server databases:

Other advantages of using Access databases include the capability to attach tables of other supported database types. The Microsoft documentation contains ambiguous references to external tables and attached tables. As I mentioned earlier in this chapter, this book doesn't use the term external tables. You can gain a significant speed advantage if you attach tables from client-server databases to an Access database rather than opening the client-server data source as a Visual C++ CDatabase object.

***Begin Note***

(d)Mainframe and Minicomputer Database Management Systems

If you have the appropriate software and hardware (called a gateway or MiddleWare), you can connect to several popular mainframe and minicomputer RDBMSs, such as IBM's DB2 or Digital Equipment Corporation's Rdb. Suppliers of gateways to DB2 databases that are compatible with ODBC include Micro Decisionware, Inc. (now part of Sybase); Information Builders, Inc.; Sybase; TechGnosis, Inc.; and IBM Corporation. (Additional information on these gateways is included in Appendix A.) In addition to the gateway, you need the appropriate ODBC driver for the mainframe or minicomputer database to which you want to connect. One of the principal commercial uses of Visual C++ is to create front ends for IBM DB2 databases.

***Begin Note***

***End Note***

You can even use SQL statements to query nonrelational databases such as CODASYL network databases or hierarchical databases typified by IBM's IMS. Products such as Information Builder's EDA/Link for Windows and the IBI EDA/SQL database engine make network and hierarchical databases behave like client-server applications.

(c)Abandoning Traditional Database Programming Languages

This book's Introduction states that this book is intended for readers who are familiar with Visual C++ programming techniques. Some readers have a tendency to skip Introductions and proceed to the first chapters in a book of this type. The following sections are designed for database developers who haven't yet fully mastered Visual C++ programming or for whom creating a database application will be their first experience with Visual C++ programming. Developers evaluating Visual C++ as an alternative to Access or Visual Basic also might benefit from the brief description of Visual C++ programming that follows. Visual C++ pros undoubtedly will want to skip the following two sections.

(d)Adapting to the Windows Event-Method Environment

Creating database applications for the character-based environment of DOS traditionally has involved top-down programming techniques. Using xBase as an example, you start at the "top" with a main program, such as APPNAME.PRG, in which you declare your PUBLIC (Global) variables and constants, and then you add the code you need to create the DO WHILE .T....ENDDO main menu loop for your application. Next, you add the procedures that include more menu loops for the next level of submenus. Then you write the procedures that contain the @...SAY and @...GET statements to create the screens that constitute the core of your DOS application. Finally, you add the accouterments, such as data validation procedures and report printing operations. As an experienced database developer, you write modular source code. You've written your own libraries of standard procedures and user-defined functions that you reuse in a variety of applications. You also might employ add-in libraries created by other developers. If you use CA-Clipper, you spend a substantial amount of time recompiling and linking your application during the development cycle.

To use Visual C++, you'll need to abandon most of the programming techniques to which you've grown accustomed and adopt Windows' object-oriented, event-driven, method-centered programming style. The first major difference you'll discover when you switch to Visual C++ as your database development platform is that you don't create a "main" program. The "main" program is Microsoft Windows. There is a hidden WinMain function in every Visual C++ program, but Windows itself has the final say on how your application executes. Your application can't execute any code until an event occurs because Visual C++ procedures begin as event handlers. Event handlers are methods that your application executes in response to events.

***Begin Note***

***End Note***

You can't generate or respond to an event without creating an application because

(d)Dealing with Programming Objects

Visual C++ makes extensive use of object-oriented programming terminology to describe the components of applications. Visual C++ classifies dialog boxes, controls on dialog boxes, databases, and tables as objects. An object possesses characteristics and behavior. The characteristics of an object are the object's properties (data), and the behavior of the object is determined by its methods (incorporated in event-handling code). An object is a container for data and code. Objects can contain other objects; dialog boxes, for example, contain control objects. Each Visual C++ object has its own predetermined set of properties to which Visual C++ assigns default values. The methods that are applicable to a programming object are a set of Visual C++ reserved words that are pertinent to the class of the object. The set of methods that is applicable to dialog boxes differs greatly from the set of methods that is used with recordset objects.

Visual C++ lets you create object variables that refer to objects with the CObject * ObjectPointer and ObjectPointer = &Object statements. After these two statements are executed, ObjectPointer is a reference (pointer) to the original object. You can assign as many different variables to the same object as you want. If you add the reserved word new to the assignment statement, as in NewObject = new ObjectName, you can create a new instance of the original object. An instance of an object is a copy of the object that you can manipulate independently of the object you have copied. Object variables are an essential element of database application programming with Visual C++.

(d)The Data Types of Visual C++

Variables declared with the xBase reserved words PUBLIC and PRIVATE default to the Logical data type and are assigned a new data type when they are initialized with a value other than .T. or .F.. Because xBase has only the four fundamental field data types used in dBASE III+ .DBF files (Character, Numeric, Date, and Logical), it's a simple matter for an xBase interpreter to determine the data type from the assigned value and to treat the variable according to its content. xBase is said to have weak data typing. In contrast, compiled languages such as Pascal and C have strong data typing. You must explicitly declare the data type when you name the variable.

Early versions of the C language took the middle road to data typing: All variables were explicitly declared, but assignments between differing types were only weakly controlled. As the sophistication of C increased (actually, with the introduction of C++), C/C++ compilers began to more strictly enforce the usage of data types. You can still cast a variable of one type and assign the result to a variable of a differing type, but using explicit casts is no longer considered an acceptable programming technique.

There are three problems with strong data typing when you're dealing with objects and databases:

Visual C++ uses the SQLBindCol() function, which solves all the preceding problems of matching the SQL datatypes with Visual C++ variable types. An added benefit of the SQLBindCol() function is that you can use a number of different type conversions. Table 1.2 shows the acceptable conversions between C/C++ variable types and SQL data types. A D signifies a default conversion, a dot is a possible alternative conversion, and an empty space signifies that there is no conversion between these types. The types SQL_C_TINYINT and SQL_C_SHORT don't have default conversions.

Table 1.2. SQL-datatype-to-C-datatype conversions.

***01VCG00***

Table 1.3 contains the SQL C types (shown in Table 1.2) matched to native C/C++ types. The DATE_STRUCT, TIME_STRUCT, and TIMESTAMP_STRUCT structures are defined in the SQLEXT.H header file. They make date and time manipulation easier.

Table 1.3. SQL C types matched to native C/C++ types.

SQL C Type
Identifier ODBC C Type C/C++ Type

SQL_C_BINARY UCHAR FAR * unsigned char far *

SQL_C_BIT UCHAR unsigned char

SQL_C_CHAR UCHAR FAR * unsigned char far *

SQL_C_DATE DATE_STRUCT struct DATE_STRUCT

SQL_C_DOUBLE SDOUBLE double

SQL_C_FLOAT SFLOAT float

SQL_C_SLONG SDWORD long int

SQL_C_SSHORT SWORD short int

SQL_C_STINYINT SCHAR signed char

SQL_C_TIME TIME_STRUCT struct TIME_STRUCT

SQL_C_TIMESTAMP TIMESTAMP_STRUCT struct TIMESTAMP_STRUCT

SQL_C_ULONG LDWORD unsigned long int

SQL_C_USHORT UWORD unsigned short int

SQL_C_UTINYINT UCHAR unsigned char

The three date and time structures are shown in Listing 1.1. These structures are defined in SQLEXT.H so that you don't have to define them in your application.

In the preceding list, the examples of the syntax of statements that create the database objects represent the simplest form of these statements. CDatabase and CRecordset objects have optional arguments or required calls to initialization functions to open and define the actual dataset. You set the value of the optional arguments based on the database type you choose and the type of locking you want.

(d)Object Collections

A collection is a set of references to related objects, similar to but not identical to an array. The specification for creating and naming collections is included in the Microsoft OLE publication Creating Programmable Applications. The references (pointers) to objects in a collection are called members of the collection. Each member of a collection has a unique name and index value. Unlike arrays, however, the index number of a member may change, and index numbers need not be contiguous. It's possible for a collection to contain no members at all. Most collections have a property, Count, that returns the number of members of the collection. The index to a collection need not be an integer, but it usually is. Some objects use string indexes. The safest approach is to always specify the unique name of the member of a collection you want to use.

The name of a collection is the English plural of the class of object in the collection. In Visual C++, collections might include dialog boxes (all dialog boxes that have been loaded by the application), controls (each control on a loaded dialog box), the data access object collections in the following list, and collections of objects exposed by OLE applications that support OLE Automation. This discussion is limited to data access objects that incorporate the following three object class collections:

(d)The Data Control Object

Visual C++ provides a CRecordView object that lets you add controls to a Visual C++ dialog box that may be used to display records from a dataset. Controls may be used to display and update data in the current record of a specified CRecordset object. Figure 1.3 illustrates a Visual C++ application's use of the CRecordView dialog box and controls to display and update information contained in the Customers table of NorthWind.MDB (supplied with Access).

***01VCG03***

Figure 1.3. A CRecordView-based application.

The advantage of using the CRecordView object is that you can create a form to browse through the records in a CRecordset object without writing any Visual C++ code at all. The source for this program is in the CHAPTR02\Record View folder on the CD that comes with this book.

***Begin Note***

***End Note***

One feature of the program that AppWizard creates is the toolbar with its VCR-style buttons, similar to the record selector buttons of Access's datasheet view. Many database developers prefer to use command buttons with Alt-key combinations for record selection. The majority of the sample applications in this book use Visual C++ code generated by using AppWizard rather than trying to code the database access by hand.

Visual C++ provides the following dialog box control objects that you can use in conjunction with dialog boxes:

Chapter 3, "Using Visual C++ Data Access Functions," provides examples of simple decision-support and transaction-processing applications that you can create with the data control and bound control objects.

***Begin Note***

(c)OLE and Visual C++

Visual C++ applications can be a data-aware application that supports OLE and OLE Automation. A data-aware application is one that includes the built-in capability to extract data from a variety of databases, with the Access database engine using ODBC or DAO. OLE extends the capabilities of OLE 1.0 by adding the following features:

The first commercial product to support OLE was CorelDRAW! 4.0, which Corel Systems released after the first version of Visual C++ appeared.

The lack of OLE-compliant applications caused the description of OLE features in the Visual C++ documentation to be sketchy at best. The OLE sample applications provide you with little assistance when you want to add OLE features to your Visual C++ applications. To fill this gap, the following sections provide an introduction to OLE Automation. Chapter 17, "Using OLE Controls and Automation with Visual C++ Applications," includes sample applications that demonstrate OLE features that are especially useful for database applications.

***Begin Note***

(d)OLE Automation

Visual C++ lets you create applications that orchestrate interprocess communication among Windows applications without requiring that you suffer through the coding and testing of DDE operations. In the language of OLE, Visual C++ is called an external programming tool. OLE Automation programming tools let you do the following:

Prior to Visual C++ and OLE Automation, you could link or embed source documents in a destination document created by the OLE control, but you couldn't use Visual C++ code to edit the source document. DDE was the only practical method of programmatically altering data in an object created by another application. (Programmatically is an adverb recently added to computerese by Microsoft. It refers to the capability of manipulating an object with program code.)

The following list explains the principal advantages of the use of OLE Automation to replace DDE for applications that require IPC (interprocess communication):

OLE Automation offers the most significant opportunity for the improvement of Windows applications since Microsoft introduced OLE 1.0 with Windows 3.1. The majority of the major software publishers have announced their intention to support OLE, but few firms other than Microsoft have committed to dates when such products will reach the shelves of software retailers. OLE 1.0 proved difficult to implement, and creating OLE applications is an even more challenging task. At the time this book was written, Symantec's C++ product and Borland's C++ 4.5 were a few of the programming tools to compete with Visual C++. At the present, only Microsoft's Windows applications offer you the sizable benefits of OLE Automation. OLE Automation is expected to be a feature of future versions of other popular Microsoft applications, such as Access and PowerPoint. By the end of 1995, virtually all mainstream Windows applications implemented OLE Automation. Much of the adoption of OLE has been forced by Microsoft, which requires all certified Windows 95 applications to be fully OLE-compliant, if applicable.

(d)Visual Basic for Applications

Why am I mentioning Visual Basic in a book on Visual C++? Mostly for background. It's likely that you have some background in Visual Basic or that you're interested in Visual Basic's relationship to Microsoft products. Also, Visual Basic for Applications is the OLE automation language.

Bill Gates, chairman and chief executive officer of Microsoft, decreed in 1991 that all of Microsoft's mainstream applications for Windows would share a common macro language (CML) derived from BASIC. His pronouncement wasn't surprising because Microsoft's first product was a BASIC interpreter for the original personal computers that used the Intel 8080 as their CPU. Microsoft's QuickBASIC and QBasic products ultimately became the most widely used flavors of structured BASIC of the world's IBM-compatible PCs. Word for Windows 1.0's Word BASIC was the first macro language for a Microsoft application that used a dialect of BASIC.

No other Microsoft application adopted BASIC as its macro language until Microsoft released Access 1.0 in November 1992. Access, however, had its own macro language that wasn't derived from BASIC, so Microsoft called Access Basic an application programming language. Access Basic is a direct descendant of Visual Basic 2.0 that introduced object variables to the Visual Basic language. Access Basic was originally called "Embedded Basic." You see occasional references to "EB" and "Cirrus Basic" in Access 1.0 help files and add-in library code. Cirrus was the code name for Access during its beta-testing period.

Visual Basic for Applications is an OLE Automation programming tool classified as an embedded macro language. Visual Basic for Applications is based on Visual Basic and offers many of Visual Basic's capabilities. The structure and syntax of Visual Basic for Applications code is very similar to that of Visual Basic. Following are some of the most significant differences you'll find between Visual C++ and Visual Basic for Applications:

During the development of Visual Basic for Applications (when its code name was Object Basic), Microsoft reportedly was willing to license Visual Basic for Applications to other software publishers for incorporation in their applications. Subsequently, Microsoft announced that Visual Basic for Applications would remain a proprietary Microsoft product and would be available only as a component of Microsoft applications for Windows.

Lotus now provides a common programming interface to its products. Lotus also is committed to supporting OLE in its products. Lotus Notes will prove to be a formidable competitor in the next few years.

Lotus is working to become compatible with standard languages, which will allow database programmers to leverage their existing programming skills.

(c)Summary

This chapter covered the process of choosing Visual C++ as a database development tool, using Visual C++ as a database front-end generator, migrating from the more traditional database programming languages, the MFC ODBC and DAO classes, and OLE. This chapter also gave you an overview of Visual C++'s capabilities as a database development platform and how Microsoft plans to use Visual C++, OLE Automation, and Visual Basic for Applications to cement the firm's leadership position in the Windows desktop database market. You don't need to be clairvoyant to conclude that the Macintosh version of Visual C++ (actually a cross compiler) will emerge as a major player in the Macintosh world in the future, together with an Access database engine designed to run as a Macintosh code resource. No matter what your opinions are relating to Microsoft's predominance in the Windows and Macintosh applications markets and the methods Microsoft has used to achieve their present market share, the Microsoft desktop database juggernaut is a fact. Developers of traditional character-based database applications in xBase or PAL who don't face this fact will find a rapidly diminishing market for their services in the mid- to late 1990s.