| Click here to support our advertisers

SOFTWARE FOR SALE
BOOKS FOR SALE
SEARCH CENTRAL
JOB BANK
CLASSIFIEDS
DIRECTORIES
REFERENCE Online Library
LEARNING CENTER
JOURNAL
NEWS CENTRAL
DOWNLOADS
COMMUNITY
CALENDAR
ABOUT US
Journal:
Get the weekly email highlights from the most popular journal for developers! Current issue
> developer.com developerdirect.com htmlgoodies.com javagoodies.com jars.com intranetjournal.com javascripts.com | All Categories : C/C++ vcgfm.htm Database Developer's Guide with Visual C++™ 4, Second Edition Copyright © 1996 by Sams Publishing Acknowledgments About the Authors Introduction What's New in Visual C++ for Database Developers New Features of Visual C++ A Database Developer's View of Visual C++ Visual C++ and Microsoft BackOffice Who Should Read This Book What You Need to Use This Book Effectively How This Book Is Organized Part I Part II Part III Part IV Part V Part VI Appendixes Conventions Used in This Book Key Combinations and Menu Options Visual C++ Code, SQL Statements, and Source Code in Other Languages Entries in Initialization and Registration Database Files Visual C++ Code Examples and Code Fragments Prefix Tags for Data or Object Type Identification A Visual C++ and Database Bibliography Introductions to Visual C++ Programming Visual C++ Books for Developers A Book on the Microsoft Jet Database Engine The Primary Guide to SQL-92 Publishers of Database Standards Keeping Up to Date on Visual C++ Periodicals The MSDN Support Product Microsoft Internet Services # What's New in This Edition Expanded coverage of ANSI and Access SQL queries via Visual C++ 4 code. Additional techniques for OLE automation. Information on programming with the new OLE containers. Coverage of OLE Custom Controls, which let you add new features to Visual C++ applications with minimal programming effort. Information on OLE Custom Controls for 32-bit environments. Techniques for using the new data access objects that Microsoft added to Visual C++ to position the product as a direct competitor to Visual Basic. These new techniques make Visual C++'s support of Access, FoxPro, and Paradox for Windows in the desktop database market even more complete. Detailed information on the redistributable 32-bit Microsoft Jet 3.0 database engine, which offers substantially improved performance compared to the 16-bit jet engine. Coverage of Visual C++'s built-in MFC classes, along with AppWizard, to let you quickly create a form to display database information with little or no Visual C++ code. Included is a sample program that actually has no programmer-written code at all. Information on the Microsoft ODBC Administrator application, included with Visual C++, which lets you connect to the Microsoft and Sybase versions of SQL Server and to Oracle client-server relational database management systems. Extensive coverage of building Visual C++ 4 front ends to interface with client-server RDBMSs. Clear examples that illustrate significant development topics, such as crosstab queries, action queries, transaction processing, and record locking. Programming examples and techniques for using Sockets and MAPI services. ### Database Developer's Guide with Visual C++™ 4, Second Edition Peter Hipson Roger Jennings 201 West 103rd Street, Indianapolis, Indiana 46290 This book is dedicated to Kathareeya "Katie" Tonyai, a brand new granddaughter. —Peter Hipson This book is dedicated to the memory of my father, George H. Jennings, Structural Engineer. —Roger Jennings ### Copyright © 1996 by Sams Publishing SECOND EDITION All rights reserved. No part of this book shall be reproduced, stored in a retrieval system, or transmitted by any means, electronic, mechanical, photocopying, recording, or otherwise, without written permission from the publisher. No patent liability is assumed with respect to the use of the information contained herein. Although every precaution has been taken in the preparation of this book, the publisher and author assume no responsibility for errors or omissions. Neither is any liability assumed for damages resulting from the use of the information contained herein. For information, address Sams Publishing, 201 W. 103rd St., Indianapolis, IN 46290. International Standard Book Number: 0-672-30913-0 Library of Congress Catalog Card Number: 95-72921 99 - 98 - 97 - 96 ----4 - 3 - 2 - 1 Interpretation of the printing code: the rightmost double-digit number is the year of the book's printing; the rightmost single-digit, the number of the book's printing. For example, a printing code of 96-1 shows that the first printing of the book occurred in 1996. Composed in AGaramond, Optima, Helvetica, and MCPdigital by Macmillan Computer Publishing Printed in the United States of America All terms mentioned in this book that are known to be trademarks or service marks have been appropriately capitalized. Sams Publishing cannot attest to the accuracy of this information. Use of a term in this book should not be regarded as affecting the validity of any trademark or service mark. ### Acknowledgments The authors are indebted to Neil Black of Microsoft's Jet Program Management Group and Stephen Hecht of Microsoft's Jet Development Group for their "Jet Database Engine 2.0 ODBC Connectivity" white paper. This white paper made a substantial contribution to the writing of Chapter 7, "Using the Open Database Connectivity API." You can find this white paper and a number of other works by these authors on the Microsoft Development Library CD. Search for the authors' names. Special thanks to Microsoft and Steve Serdy at Microsoft Developer Support for their excellent assistance. Steve's help with the Win32 Common Controls was most valuable. Thanks are also due to Robert Bogue and Jeff Perkins, the technical editors for this book. Jeff's help in getting our references to Microsoft SQL Server 6 up-to-date was most valuable. Thanks also to Grace Buechlein, our acquisitions editor. Grace's professionalism was most valuable when we had problems, and we hope to work with her again. Thanks to Michael Watson, our development editor, for his extra work in making sure that the book presented the very latest information available. Thanks also to Gayle Johnson, our production editor, and to Anne Owen, our copy editor. ### About the Authors Peter Hipson is a developer, consultant, and author. His work is mostly in the Windows arena, but he also pursues work in ODBC, database, and GIS applications. He was a member of the Microsoft beta-test team for QuickC for Windows; Visual C++ 1.0, 1.5, 2.0, and 4.0; Windows 3.0 and 3.1; Windows 95; Windows for Workgroups 3.1 and 3.11; and Windows NT. Peter is the author of Sams Publishing's Advanced C, What Every Visual C++ 2 Programmer Should Know, and Visual C++ Developer's Guide, as well as Que's Using QuickC for Windows. He was a coauthor of Database Developer's Guide with Visual C++ and Programming Windows 95 Unleashed, both from Sams Publishing. He also occasionally writes for the Windows Technical Journal. He will be writing a column on database programming in The Win'95 Magazine, which will publish its first issue in mid-1996. He also has a column in an Asian magazine on the Internet. Peter has been working with microcomputers since the mid-1970s. He also has many years of experience with IBM mainframes. He is the author of STARmanager, a GIS-type application that assists sales and marketing managers in managing their resources. You may contact Peter via CompuServe (70444,52) or the Internet (phipson@darkstar.mv.com). Roger Jennings is a consultant specializing in Windows database, multimedia, and video applications. He was a member of the Microsoft beta-test team for Visual C++ 2.0; Visual Basic 2.0, 3.0, and 4.0; the Professional Extensions for Visual Basic 1.0; Visual Basic for DOS; Microsoft Access 1.0, 1.1, 2.0, and 7; Word for Windows 2.0; the 32-bit versions of Word and Excel; Microsoft Project 4.0 and 4.1; Windows 3.1 and Windows 95; Windows for Workgroups 3.1 and 3.11; Windows NT 3.5, 3.51, and 4.0 Workgroup and Server; the Microsoft ODBC 2.0 and 2.5 drivers; Video for Windows 1.1; and Multimedia Viewer 2.0. Roger is the author of Sams Publishing's Database Developer's Guide with Visual Basic 4, upon which much of this book is based. He also wrote Access 2 Developer's Guide and Access 95 Developer's Guide, both from Sams Publishing, and two other books on Microsoft Access, as well as books devoted to Windows 95, Windows NT, and desktop video production with Windows 95 and Windows NT 3.5x. He also is a contributing editor for Fawcette Technical Publications' Visual Basic Programmer's Journal. Roger has more than 25 years of computer-related experience, beginning with his work on the Wang 700 desktop calculator/computer. He has presented technical papers on computer hardware and software to the Academy of Sciences of the former USSR, the Society of Automotive Engineers, the American Chemical Society, and a wide range of other scientific and technical organizations. He is a principal of OakLeaf Systems, a Northern California software consulting firm. You may contact him via CompuServe (70233,2161), the Internet (70233.2161@compuserve.com), or the Microsoft Network (Roger_Jennings). ### Introduction The release of Visual C++ clearly shows that Microsoft is taking the lead in creating C++ development platforms. Visual C++ 4 continues to build on the development platform that the C/C++ program has established. MFC, with versions 2.0, 2.5, 3.0, and 4, offers the C++ programmer an advanced object-oriented methodology to develop applications that are easy to develop and maintain. Microsoft's OLE and the Common Object Model (COM) are now firmly entrenched as the new compound document standard for Windows, and OLE Automation replaces DDE as the primary means of interapplication communication. Huge system resource consumption by OLE megaservers, typified by Excel and Word, limited the adoption of OLE Automation in commercial database front ends. Windows 95 and Windows NT 3.51 have overcome most resource limitations when running 32-bit OLE applications. Thus, 32-bit Visual C++ programs are likely to be the glue that binds industrial-strength solutions orchestrating members of 32-bit Microsoft Office with OLE Automation. Out-of-process OLE Automation servers, especially the big ones, are not renowned for their speed. Fortunately, there's a trend toward a Pentium on every power-user's desktop, so faster hardware comes to the rescue again. OLE Custom Controls (OLE controls), which are in-process OLE Automation servers, don't suffer from the performance hit associated with the Lightweight Remote Procedure Calls (LRPCs) required by out-of-process servers. Thus, OLE controls typically are as quick as VBXs, which OLE controls replace in the 32-bit versions of Windows. Microsoft's addition of the Microsoft Jet database engine's DAO interface to MFC makes Visual C++ a strong competitor to the principal players in the desktop database market: Access, Visual Basic, FoxPro for Windows, Lotus Approach, and Borland International's dBASE and Paradox for Windows. The Open Database Connectivity (ODBC) application programming interface (API), introduced in Visual C++, made Visual C++ a major factor in client-server front-end development throughout the world. DAO promises to lead Visual C++ 4 into areas that in the past have been the private domain of Visual Basic. Using 32-bit ODBC resolves the controversy in the computer press regarding the relative performance of DBLib and ODBC with Microsoft SQL Server. Tests show that 32-bit ODBC is as fast as or faster than equivalent calls to DBLib functions. The three major sections of this Introduction describe the new database connectivity features of Visual C++ and show how Visual C++ fits into the database front-end and back-end market. ### What's New in Visual C++ for Database Developers Visual C++'s new features of fall into two basic categories: Features that are of interest only to database developers Features that affect all Visual C++ developers, regardless of whether they use Visual C++'s Data Access Objects First, it is very important to realize that programmers today use many different versions of Visual C++. The first version of Visual C++, 1.0, was available in both a 16-bit and a 32-bit edition. Both of these products are now out-of-date and aren't used much by professional programmers. The next major release of Visual C++, 1.5, was made available only in a 16-bit version. This version included MFC 2.5 and other improvements. The 16-bit versions of Visual C++ that followed Visual C++ 1.5 include 1.51 through 1.52. Version 1.52c is distributed with Visual C++ 4. Microsoft distributes both versions together to give developers of 32-bit applications access to the 16-bit development tools. The 1.5x versions of the 16-bit Visual C++ products incorporate minor changes and additions, such as the inclusion of the OLE controls development platform. The second major release of the 32-bit version of Visual C++ was Visual C++ 2. This product, released in the fall of 1994, supports only 32-bit applications. A redesigned development environment, MFC 3, and other features made Visual C++ 2 a major player in the 32-bit C/C++ development arena. The next release of the 32-bit version of Visual C++ was Visual C++ 4. This product, released in the fall of 1995, supports only 32-bit applications. MFC 4, DAO, numerous improvements to the developer environment, and other features make Visual C++ 4 a must-have for any serious Windows developer. A subscription program was offered to Visual C++ programmers who needed regular updates to Visual C++. Originally slated to be delivered every quarter, releases have been coming out about twice a year. The current subscription program promises the "next two releases," which would represent about a year in time. Microsoft has announced that Visual C++ 4.1 should be available in the spring of 1996. The subscription version of Visual C++ is available from Microsoft directly and from most resellers. There will be more releases of Visual C++ as the years go by. However, it is beyond the scope of this book to speculate what the future holds for Visual C++. The following sections describe the categories of Visual C++ features. #### New Features of Visual C++ The most apparent change in Visual C++ 4 for database programmers is the adoption of MFC 4 and the addition of the MFC Data Access Object (DAO) classes. Additionally, Windows 95's Rich Text common control has been integrated into MFC. The following list briefly describes the most important differences between Visual C++ 4 and earlier versions of Visual C++: Visual C++ 1.5x develops 16-bit applications, whereas Visual C++ 4 is a 32-bit (only) development platform. Visual C++ 4 uses a much more advanced development environment. The integration of Visual C++ 4's components is much tighter than it was with Visual C++ 2. There have been major improvements to Visual C++ 4's ClassWizard, too. This lets the programmer have docked windows, more flexible toolbars, and other ease-of-use features. Visual C++ 4 offers MFC 4 and supports 32-bit OLE. Visual C++ 4 has integrated the functionality of the AppStudio program (which is used to edit program resources) into Visual C++'s development environment. There are improved toolbar editing and design tools, and the graphics editor is much better. For programmers who are just moving up to the 32-bit version of Visual C++, there is no need to switch between Visual C++ and AppStudio while developing applications. Visual C++ 4 can edit resources in applications. You can use this functionality, missing from Visual C++ 2, when running Visual C++ 4 under Windows NT. Visual C++ applications support toolbars, status bars, dialog bars, and floating palettes. Toolbars are dockable and have optional tooltip support built-in. Visual C++ supports 32-bit OLE Custom Controls, more commonly called OLE controls in this book. Because VBX controls can't be used in 32-bit environments, OLE controls are your only custom control choice when creating 32-bit Visual C++ 4 applications. Third-party VBX publishers have created a wide array of OLE controls, with functionality similar to their most popular VBX controls. Visual C++ 4 comes with a number of useful OLE controls, including the ever-popular Grid control, and a number of sample controls that can be rewritten if you like. Customization lets you alter Visual C++'s user interface by adding menu choices, modifying toolbars, and manipulating other elements of the design environment. Visual C++ 4 lets you configure the editor to emulate either the Brief or Epsilon editors. Visual C++ 4 supports remote debugging using either a network connection or serial ports. However, there is still no support for dual-monitor debugging. You can create out-of-process OLE miniservers (OLE applets, typified by Microsoft Graph 5.0) that you can use with any application that can act as an OLE Automation client, including Visual C++. Microsoft calls these miniservers LOBjects (line-of-business objects). You can run multiple instances of Visual C++ in order to test your newly created OLE applet. ### A Database Developer's View of Visual C++ Making the choice between Access, Visual Basic, Visual FoxPro, Paradox for Windows, dBASE for Windows, Delphi, or Visual C++ as your primary database development platform is not a simple matter, especially for seasoned developers of character-based applications. Most programmers don't consider Visual C++ to be a database development tool, so they adopt one or more of the popular Windows desktop database systems or Visual Basic. FoxPro for Windows offers xBase language compatibility and, without question, is the fastest of all the Windows desktop databases. Access has a unique user interface that lets you run simple to moderately complex database applications with a minimum of effort, and it also supports OLE controls (OCXs). Visual Basic offers more programming flexibility than Access, and you can incorporate a variety of commercial and shareware OLE controls to simplify database front-end development. The Paradox for Windows desktop RDBMS has a wider variety of field data types than any RDBMS, Windows or DOS, including client-server database systems. Each of these products has its own feature set, together with limitations that might not become apparent until you're embroiled in your first large-scale client-server database front-end application. Chapter 1, "Positioning Visual C++ in the Desktop Database Market," compares Visual C++ to your alternatives among the major combatants in the Windows database wars. If you've invested several years in developing xBase or PAL programming skills and are reluctant to abandon xBase RDBMSs or Paradox for a new object-oriented, event-driven Windows programming language, welcome to the crowd. COBOL programmers have the same problem when their employers downsize from mainframe "legacy" databases to client-server systems running on PCs. Unfortunately for PAL programmers, the ObjectPAL programming language of Paradox for Windows bears little resemblance to the character-based PAL of Paradox. xBase and PAL, however, all have their roots in the original Dartmouth BASIC. The C programming language has a structure similar to PL/I or PASCAL. Thus, you might find the structure of Visual C++ applications similar to the xBase or PAL programs you're now writing. If only C++ were as simple as C! C is, for the most part, easy to use, but when many programmers are exposed to C++ for the first time, they find it to be totally different than C. Nothing could be further from the truth, however. C++ is easy to learn and adapt to if you remember that the original C language is a subset of C++. The quickest conversion from C to C++ is to simply rename the file. Simply choosing Visual FoxPro, dBASE for Windows, or Paradox for Windows because you're accustomed to writing xBase or PAL code isn't likely to be a viable long-term solution to your Windows database development platform dilemma (notwithstanding John Maynard Keynes' observation that "in the long term, we are all dead"). If you create Windows applications for a living, either as an in-house or independent developer, you're expected to provide your firm or client with applications that incorporate today's new technologies. You need to prepare now for OLE, with its in-place activation and OLE Automation (OA), and Visual C++ OLE controls. Windows is where the action is. More than 60 million copies of Windows (a mixture of Windows 3.x, Windows 95, and Windows NT) give Microsoft the marketing clout to make OLE, ODBC, and OLE controls the "standards" of the desktop computers of the world (whether the "industry" agrees or not). The alternative vaporware standards proposed by groups of software vendors organized to combat the Microsoft behemoth are very unlikely to replace OLE, ODBC, and OLE controls in the foreseeable future. Windows desktop database applications present a challenge to developers accustomed to writing a thousand or more lines of code to create character-based RDBMS applications in Visual Basic, xBase, PAL, C, or other programming languages. You can create a very simple but usable Visual C++ database application with AppWizard and very little Visual C++ code. One example in this book contains no programmer-written code at all. Microsoft Access offers code-free capabilities—if you don't consider Access macros to be code. You'll need to write substantial amounts of code to create a usable database application with dBASE, Visual FoxPro's xBase, or Paradox's ObjectPAL. However, the reality is that you have to write a substantial amount of code to create a commercial-quality production database application with any of these products. The issue isn't how much code you have to write, but in which language you will write the code. Here are some of the language issues that will affect your career opportunities or the size of the numbers on your 1099s: xBase and PAL are yesterday's most prevalent desktop database programming languages. Will xBase and ObjectPAL ultimately survive the onslaught of Microsoft's present Object Basic dialects, such as Visual Basic and the Visual Basic for Applications (VBA) variants of Microsoft Word 7, Access 7, and Excel 7? Visual C++ lets you write incrementally compiled database applications that don't require helper libraries such as VBRUN300.DLL. Borland's Delphi offers fully compiled .EXEs but uses a variation of Pascal as its programming language. Does Delphi's Pascal really stand a chance of replacing C++ as the preferred language for writing commercial Windows applications or of making Object Basic/VBA extinct as an application programming (macro) language? Visual C++ 4, Access 7, and Visual Basic 4 act as containers for OLE Custom Controls. Excel, Word, and Project also support OLE controls. Will many of the new applications systems that will be released in the next few years also support OLE controls? Visual C++ 4 allows cross-platform development for a number of different platforms, including applications for Macintosh computers. Microsoft now has a Macintosh version of Visual C++ that enables Windows developers to easily port their Windows applications to the Macintosh. Most development done under Windows NT can be directly ported to Windows 95 with minimum modifications. This book doesn't purport to answer these questions directly, but you're likely to reach your own conclusions before you finish reading it. At the time this book was written, Visual C++, Visual Basic 4, and Access were the major database development platforms to fully support OLE, OLE Automation, and OLE controls. Visual C++ is the only development platform, other than Visual Basic, that lets you create your own OLE Automation miniservers. Chapter 16, "Creating OLE Controls with Visual C++ 4," and Chapter 17, "Using OLE Controls and Automation with Visual C++ Applications," describe how OLE, OLE Automation, and OLE controls fit into your decision-support database front ends. NOTE Access 2 was released before the OLE Custom Control specification was finalized and many months before the retail release of Microsoft Visual C++ 2.0's Control Development Kit (CDK), which developers need in order to implement OLE controls. Access 2's OC1016.DLL isn't compatible with the final version of commercial OLE controls designed for use with Visual C++, which use OC25.DLL. The Access 2 Service Pack updates Access 2.0 to accommodate 16-bit OLE controls based on Visual C++ 1.5's OC25.DLL. If you find that you need to work with Access 2, you should keep these restrictions in mind. Access 7 doesn't present these problems. Whatever language you ultimately choose, you must adapt to the event-driven approach to application design, inherited from the Windows graphical user interface (GUI). You also need to face the fact that Windows applications won't perform with the blazing speed of your Clipper or FoxPro applications running directly under DOS. Few, if any, Windows applications can match their DOS counterparts in a speed contest, but this situation is likely to change when you run your 32-bit database front end under Windows NT on a high-powered RISC workstation. Fortunately, most Windows users have grown accustomed to the sometimes sluggish response of Windows. It's possible, however, to design Visual C++ client-server front ends that rival the performance of their character-based counterparts. Chapter 15, "Designing Online Transaction-Processing Applications," and Chapter 20, "Creating Front Ends for Client-Server Databases," provide examples of "plain vanilla" front ends that deliver excellent performance. ### Visual C++ and Microsoft BackOffice When this book was written, Microsoft Office (both 4.2x and Office 95) had garnered more than 80 percent of the Windows productivity application suite (front-end) market. Microsoft Office 4.2 includes Excel 5.0, Word 6.0, PowerPoint 4.0, and a Microsoft Mail 3.2 client license. Microsoft Office 95 includes Excel 7, Word 7, and PowerPoint 7, all in 32-bit versions that run under both Windows 95 and Windows NT. The Professional Versions of Microsoft Office also include Microsoft Access (Office 4.3 contains Access 2.0, while Office 95 includes Access 7). Encouraged by the success of Office, Microsoft introduced its server (back-end) suite, BackOffice, in the fall of 1994. Microsoft BackOffice comprises a bundle of the following server products: Microsoft Windows NT 3.51 Server, the operating system on which the other components of BackOffice run as processes. Microsoft SQL Server 6 , a client-server RDBMS. Will be upgraded to Microsoft SQL Server 6.5 in the spring of 1996. Microsoft SNA Server 2.11, which provides connectivity to IBM mainframes and AS/400 series minicomputers via IBM's System Network Architecture. Microsoft Systems Management Server (SMS) 1.1, which helps you distribute software and track client hardware and software. Version 1.2 is scheduled to be released to beta sites sometime in 1996. Microsoft Exchange Server, which integrates e-mail, group scheduling, electronic forms, and groupware applications on a single platform that can be managed with a centralized, easy-to-use administration program. It's designed to make messaging easier, more reliable, and more scalable for businesses of all sizes. Microsoft Mail Server 3.5, a file-sharing e-mail system. Will be upgraded to Microsoft Exchange Server when it becomes available in the spring of 1996. Like Microsoft Office, you get a substantial discount (of about 40 percent) from the individual server license prices when you purchase the BackOffice bundle. Unlike earlier versions of Windows NT Server and SQL Server, which were available in "Enterprise" versions with unlimited client licenses, BackOffice doesn't include client licenses. The commercial success of BackOffice, is by no means assured. It's not likely that large numbers of major corporations, the target market for BackOffice, will adopt this bundle until final versions of all of its promised components are delivered, which might not happen until 1997. Microsoft SQL 6.0 and Exchange Server use OLE and OLE Automation pervasively. The Messaging API 1.0 (Extended MAPI), on which Exchange is based, uses Messaging OLE objects, and Schedule+ 7.0 has its OLE/Schedule+ object collections. The development tools for Microsoft SQL Server 6.0 RDBMS and Exchange e-mail system use VBA. (Exchange Server is a nonrelational database optimized for messaging services.) Ultimately, all of the members of the BackOffice suite are likely to offer VBA extensions for customization. Microsoft has positioned Visual C++ as a development platform for "building solutions" based on BackOffice servers. Visual C++ developers stand to gain a huge new revenue base writing database applications for Microsoft Exchange and SQL Server 6.0. ### Who Should Read This Book This book is intended primarily, but not exclusively, for the following categories of readers: Visual C++ developers who want to take maximum advantage of Visual C++'s database connectivity to create high-speed, production-grade graphic front ends for a variety of desktop and client-server databases. Access developers who have found that they need more control over their data display and editing forms than is afforded by the present version of Microsoft Access. Visual C++ database applications also consume far fewer Windows resources than equivalent Access applications. Visual Basic developers who want to take advantage of Visual C++'s automated access to the Windows APIs, gain function callback capability, and manipulate pointers. Developers of character-based DOS database applications whose clients or organizational superiors have decided to migrate from DOS to Windows applications. Users of xBase or Paradox products who need to create industrial-strength, 32-bit database front ends running under Windows 95 or Windows NT 3.51. (Windows NT 3.51 might not yet be a major player in the operating systems numbers game, but firms that adopt Windows NT 3.51 are major employers of database consultants.) Programmers who would like to develop database applications by expending less than 25 percent of the time and effort required to create equivalent applications with C and C++. Those addicted to C++ can quickly create prototype database applications with Visual C++. (It's amazing how many prototype Visual C++ applications become production database front ends.) Victims of the corporate downsizing revolution, principally COBOL, PL/I, or FORTRAN programmers who need to acquire C, C++, and Windows database development skills to remain gainfully employed. Users of proprietary GUI front-end development applications for client-server databases who are tired of forking over substantial per-seat licensing fees for each client workstation that is attached to the server. Chief information officers (CIOs) or management information services (MIS) executives who need to make an informed decision as to which Windows front-end generator their organization will adopt as a standard. Others who are interested in seeing examples of commercially useful Visual C++ database applications that earn developers a comfortable or better-than-comfortable living. This book assumes that you have experience with Visual C++ or one of the traditional PC programming languages for Windows, such as Microsoft or Borland C or C++, Turbo Pascal for Windows, or the Windows version of SmallTalk. This book doesn't contain an introduction to C/C++ programming techniques; many excellent tutorial and references books are available to fill this need. (The bibliography that appears later in this Introduction lists some of the better books and other sources of information for beginning-to-intermediate-level C/C++ programmers.) Instead, this book begins with an overview of how Visual C++ fits into the desktop and client-server database market and proceeds directly to dealing with data sources in Visual C++. The entire content of this book is devoted to creating useful Visual C++ database applications, and most of the examples of Visual C++ code involve one or more connections to database(s). All the code examples in this book, except for minor code fragments, are included on the accompanying CD. Sample databases in each of the formats supported by the Access 7 database engine are provided. Some of the sample databases are quite large, so you can use their tables for performance comparisons. Tips and notes based on the experience of database developers with Visual C++ and Access appear with regularity. ### What You Need to Use This Book Effectively You need Visual C++ 4 (or later) to re-create or modify the sample applications that appear in this book. Although you can use Visual C++ 2.x , Visual C++ 4 (or later) is a necessity for serious database development using the 32-bit environment provided by Windows 95 and Windows NT, mostly due to the fact that Visual C++ 4 supports DAO. Executable versions of selected sample applications are included on the accompanying CD, as are a number of programs, demos, and other items. I strongly recommend that you purchase a copy of Microsoft Access if you intend to use Access .MDB database files in commercial applications. You can't really create new databases and add tables using the ODBC drivers that are supplied with Visual C++; however, DAO does allow the creation of Access databases. You can use Access's query design window to create a query graphically, test the result, and then copy the Access SQL statement underlying the query to your Visual C++ code. Using Access's Relationships window to establish rules for enforcing referential integrity and creating business rules that maintain domain integrity is much simpler than Visual C++'s code-centric approach. An additional benefit of acquiring Access is the availability of database design and documentation tools for Access that are not yet available to Visual C++ database developers. Some of the new design and documentation tools for Access databases are described in Chapter 4, "Optimizing the Design of Relational Databases," and in Chapter 22, "Documenting Your Database Applications." If you develop Visual C++ database applications for a living, you'll save many times your investment if you buy a copy of Access. NOTE If you don't have Excel or Word for Windows, it might be well worth the investment to get the Microsoft Office Professional Edition, which includes Word for Windows, Excel, Access, and PowerPoint. Developers of commercial database applications with Visual C++ are likely to want the additional features offered by third-party, data-aware custom controls. As these controls become available (WinWidgets/32 is an example), they can save the programmer substantial effort in developing applications. Although Microsoft has co-opted the data-aware grid, combo box, and list box OLE control market by providing 16- and 32-bit OLE control versions of these controls with Visual C++ 4, many third-party publishers offer quite useful enhancements to Microsoft's set. Several third-party custom controls are used to create the sample applications in this book. Sources of these OLE controls are provided in Appendix A, "Resources for Developing Visual C++ Database Applications." Most Visual C++ database applications for Windows 3.1+ and Windows 95 will perform satisfactorily on 80386DX/33 or faster computers with 8M or more of RAM. If you plan to use Access, you should have a minimum of 16M of RAM. If you plan to take full advantage of OLE and OLE Automation, 12M to 16M of RAM is recommended, regardless of which applications you will be running. All of the 32-bit versions of the sample applications in this book run satisfactorily under Windows NT Workstation 3.51 with 16M of RAM, and on a Windows 95 80386DX/33 with 8M of RAM. All development work was done on a Pentium 90 with 32M of RAM, a machine of acceptable performance. The authors recommend running Visual C++ 4 with at least 16M of RAM on a fast 486 (or better) processor. ### How This Book Is Organized This book is divided into six parts that contain 24 chapters. Each part deals with related database application design subjects. The parts are ordered in a way that parallels a typical database application development program. The contents of each part and chapter are described in the sections that follow. #### Part I Part I, "Visual C++ Data Access," introduces you to Visual C++'s capabilities as a Windows database application development environment. Chapter 1, "Positioning Visual C++ in the Desktop Database Market," analyzes the features that Visual C++ offers database developers and shows how the language fits into Microsoft's strategy to dominate the desktop and client-server database development markets. Chapter 2, "Understanding MFC's ODBC Database Classes," provides a detailed description of how you create and manipulate Visual C++ MFC classes and collections using Access .MDB databases. Chapter 3, "Using Visual C++ Data Access Functions," covers the ODBC API-level SQL...() functions and presents some functions that use the ODBC API and that can be called from both C and C++ programs. #### Part II Part II, "Database and Query Design Concepts," deals with relational database design and shows you how to use SQL to create SELECT and action queries that employ the Access database engine and the ODBC API to process the queries. Chapter 4, "Optimizing the Design of Relational Databases," shows you how to normalize data in order to eliminate data redundancy in your application. Chapter 5, "Learning Structured Query Language," discusses ANSI SQL-89 and SQL-92 and tells how Access SQL differs from the "standard" SQL used by client-server and mainframe databases. Chapter 6, "The Microsoft Jet Database Engine," provides insight on the use of the Jet ODBC drivers with xBase, Paradox, and Btrieve tables. It also introduces you to Microsoft's Data Access Objects (DAO), a technology newly incorporated into Visual C++ 4.0. Chapter 7, "Using the Open Database Connectivity API," shows how Visual C++ applications interface with ODBC drivers. Chapter 8, "Running Crosstab and Action Queries," advances beyond simple SQL SELECT queries and shows you how to write queries that include TRANSFORM, PIVOT, INTO, and other less commonly used SQL reserved words that modify the data in your tables. #### Part III Part III, "An Introduction to Database Front-End Design," is devoted to creating commercial-quality decision-support front ends for databases. Chapter 9, "Designing a Decision-Support Application," describes the principles of converting raw data into easily comprehensible information that can be displayed on Visual C++ forms. Chapter 10, "Creating Your Own Data Access Controls," shows you how to take advantage of OLE Custom Controls. Chapter 11, "Using the New Win32 Common Controls," gives examples of using Visual C++ with the new Win32 Common Controls in programs that will work in both Windows 95 and Windows NT. Chapter 12, "Printing Reports with Report Generators," shows you how to design reports and how to seamlessly integrate report generation with your database applications. #### Part IV Part IV, "Advanced Programming with Visual C++," takes you deeper into the realm of commercial database application development. Chapter 13, "Understanding MFC's DAO Classes," shows you the new MFC 4 implementation of the Data Access Object interface to the Microsoft Jet database engine, providing a reference to the DAO classes. Chapter 14, "Using MFC's DAO Classes," presents a practical tutorial dealing with the DAO classes. Chapter 15, "Designing Online Transaction-Processing Applications," describes how to design forms for heads-down, high-speed data entry and how to use Visual C++'s transaction-processing reserved words to speed bulk updates to tables. Chapter 16, "Creating OLE Controls with Visual C++ 4," explains how to develop practical OLE controls. Chapter 17, "Using OLE Controls and Automation with Visual C++ Applications," describes how to add OLE controls to your applications using Visual C++ 4. Part IV concludes with Chapter 18, "Translating Visual Basic for Applications Code to Visual C++," for Access developers who are porting Access applications to Visual C++. #### Part V Up until Part V, "Multiuser Database Applications," this book is devoted to self-contained applications designed for a single user. Part V provides the background and examples you need to add networking and client-server database capabilities to your Visual C++ database applications. Examples employ Windows 95, Windows NT Server 3.51, and SQL Server 6 for Windows NT. Chapter 19, "Running Visual C++ Database Applications on a Network," describes how to use peer-to-peer and network servers to share databases among members of a workgroup or throughout an entire organization. Chapter 20, "Creating Front Ends for Client-Server Databases," describes how to use the ODBC API to set up and connect to client-server and mainframe data sources with your Visual C++ applications. Decision-support and online transaction processing examples that connect Microsoft SQL Server for Windows NT are included. Chapter 21, "Interacting with Microsoft Mail, MAPI, and TAPI," details the use of the MAPI custom control, the Schedule+ Access Library (SAL), and TAPI. Examples of using Microsoft's Electronic Forms Designer (EFD) to create mail-enabled Visual C++ applications are provided as well. Chapter 21 also gives you a brief glimpse of what you can expect when you begin to develop applications for Microsoft Exchange. #### Part VI Part VI, "Distributing Production Database Applications," shows you that no production database application is complete without full documentation and an online help system for users. Chapter 22, "Documenting Your Database Applications," shows you how to use Visual C++'s database object collections to create a data dictionary in the form of a text file that you can import into other applications, such as Word for Windows 7 or Excel 7. Chapter 23, "Creating Help Files for Database Applications," describes how to use Word for Windows and commercial WinHelp assistants, such as Doc-To-Help and RoboHelp, to speed the addition of context-sensitive help to your Visual C++ applications. Chapter 24, "Creating Distribution Disks for Visual C++ Applications," shows you how to create a professional installation application that uses either the Microsoft Setup application for Visual C++ or other mainstream Windows setup applications. #### Appendixes The appendixes provide useful reference data. Appendix A, "Resources for Developing Visual C++ Database Applications," lists add-in products that offer new features to your database applications. It also lists publishers of periodicals devoted to Visual C++ and databases in general. Suppliers and publishers are categorized by subject, and entries include addresses, telephone numbers, and fax numbers, as well as brief descriptions of the products listed. Appendix B, "Naming and Formatting Conventions for Visual C++ Objects and Variables," describes the prefix tags used in this book to identify the object or data type of variables. These naming conventions are based on a slightly modified form of Hungarian Notation that is commonly used in C and C++ programming. This notation was invented by Charles Simonyi, who at one time worked on the development of Access 1.0 at Microsoft. Appendix C, "Using the CD-ROM," describes the files included on the CD that comes with this book. ### Conventions Used in This Book This book uses several typesetting styles to distinguish between explanatory material, the entries you make in Windows dialog boxes, components of initialization (.INI) files, and the code you enter in Visual C++'s code editing window. The sections that follow describe the typographic conventions used in this book. #### Key Combinations and Menu Options Accelerator key combinations (Alt-key) and shortcut key combinations (Ctrl-key) that you use to substitute for mouse operations are designated by joining the key with a hyphen (-). Ctrl-C, for example, is the shortcut key for copying a selection to the Windows clipboard. Alt-H is a common accelerator key combination that takes the place of clicking the Help button in dialog boxes. Some applications, such as Microsoft Word 6, use multiple-key shortcuts, such as Ctrl-Shift-key, to activate macros. Menu options are separated by a vertical bar. For example, "File | Open" means "Choose the File menu and select the Open option." #### Visual C++ Code, SQL Statements, and Source Code in Other Languages Visual C++ code, Visual C++ reserved words, keywords (such as the names of collections), SQL statements, and source code fragments in other programming languages appear in monospace type. Reserved words and keywords in ANSI SQL and xBase programming languages appear in uppercase monospace. Here is an example of the formatting of an SQL statement:
>     SELECT Name, Address, City, Zip_Code FROM Customers WHERE Zip_Code >= 90000
> The equivalent of the preceding statement in xBase is formatted as
>     USE customers LIST name, address, city zip_code WHERE zip_code >= 90000
> The code line continuation character [ic:ccc] is used when the length of a code line exceeds the margins of this book. For example:
>     EXF=3.0;File;&Export Folder...;11;IMPEXP.DLL;0;;Exports folders to a

>     [ic:ccc]backup file;MSMAIL.HLP;2860
> Note that this separator character isn't a valid character when embedded in an SQL statement String variable, nor is it valid in C/C++ source code. Wherever possible, C/C++ code is broken so that the [ic:ccc] character isn't needed. Some listings created by Visual C++'s AppWizard contain lines that are too long to fit on one line of this book. These lines are printed on two lines. When you look at the code in an editor, however, you will see these lines as one long line. Special implementations of SQL that don't conform to ANSI SQL-92 standards, such as the {ts DateVariable} syntax that Microsoft Query uses to indicate the timestamp data type, appear as in the SQL dialog box of the application. The PIVOT and TRANSFORM statements of Access SQL that (unfortunately) weren't included in SQL-92, however, retain uppercase status. #### Entries in Initialization and Registration Database Files Entries in Windows, Visual C++, and Microsoft Access 2 initialization (.INI) files appear in monospace type. Sections of .INI files are identified by square brackets surrounding the section identifier, as in
>     [Options]

>     SystemDB=c:\vbapps\system.mda
Entries that you make in Windows 3.1+'s registration database using the registration database editor application, REGEDIT.EXE, in verbose mode also appear in monospace to preserve indentation that indicates the level of the entry in the tree (file-directory-like) structure of registration database entries. The full path to HKEY_CLASSES_ROOT and other entries in the Registry of Windows 95 and Windows NT is provided unless otherwise indicated in the accompanying text. #### Visual C++ Code Examples and Code Fragments As mentioned earlier, all examples of Visual C++ code, as well as code examples, appear in monospace. Monospace type also is used for code fragments. Styles and weights are applied to code examples and fragments according to the following rules: Names of symbolic constants, including the three constants TRUE, FALSE, and NULL, appear in uppercase. Additions to Visual C++ AppWizard-produced programs typically appear in bold monospace. This lets you see what has been added to create the application's functionality. Replaceable variable names, arguments, and parameters, also known as placeholders, appear in italic monospace. Data type identification tag prefixes that identify the data type of variables of Visual C++'s fundamental data types and the type of object for variables of object data types don't appear in italic, as in int nObjectVar. French braces ({}) indicate that you must select one of the optional elements separated by the pipe character (|) and enclosed in the braces. This doesn't apply to the unusual employment of French braces by Microsoft Query in SQL statements. An ellipsis (...) indicates that the remaining or intervening code required to complete a statement or code structure is assumed and doesn't appear in the example. #### Prefix Tags for Data or Object Type Identification The code examples in this book use two- or three-letter prefix tags to identify the data type of variables and symbolic constants of the fundamental data types of Visual C++ and other Object Basic dialects, as well as object variables. Examples of Hungarian variable names of the fundamental data types are szStringVar, nIntegerVar, lLongVar, dDouble, and pPointer. Microsoft and this book use the term fundamental data type to distinguish conventional variables, which have names that are Visual C++ reserved words, from variables of object data types, which might have names that are either reserved or keywords. Prefix tags also are used to identify the type of object when you declare variables of the various object data types supported by Visual C++. The most common object prefix tags in this book are wsWorkSpace, dbDatabase, and qdfQueryDef. Appendix B provides detailed information on the derivation and use of type identifier prefix tags. ### A Visual C++ and Database Bibliography As I mentioned earlier, this book is intended for readers who are familiar with writing Visual C++, Visual Basic for Applications, and/or Access Basic code. If your first Visual C++ application is a full-fledged database front end, you might want to acquire one or more tutorial or reference books on introductory or intermediate-level Visual C++ programming. Access Basic programmers who are porting Access 2 or Access 7 applications to Visual C++ will benefit from developer-level Visual C++ guides. You also might want more details on the 1992 version of ANSI SQL, SQL-92, and the background of the ODBC API. The following sections provide recommendations of up-to-date books that fulfill these needs. #### Introductions to Visual C++ Programming The following books are designed to introduce you to Visual C++'s event-driven graphical programming environment: Essential Visual C++, by Mickey Williams (Indianapolis: Sams Publishing, 1995, ISBN: 0-672-30787-1) Teach Yourself Visual C++ 4 in 21 Days, by Nathan and Ori Gurewich (Indianapolis: Sams Publishing, 1994, ISBN: 0-672-30795-2) Visual C++ in 12 Easy Lessons, by Greg Perry and Ian Spencer (Indianapolis: Sams Publishing, 1995, 0-672-30637-9) What Every Visual C++ 2 Programmer Should Know, by Peter Hipson (Indianapolis: Sams Publishing, 1994, ISBN: 0-672-30493-7) #### Visual C++ Books for Developers The following books cover intermediate-to-advanced Visual C++ programming topics: Develop a Professional Visual C++ Application in 21 Days, by Mickey Williams (Indianapolis: Sams Publishing, 1995, ISBN: 0-672-30593-3) Master Visual C++, Third Edition, by Nathan and Ori Gurewich (Indianapolis: Sams Publishing, 1995, ISBN: 0-672-30790-1) Visual C++ 2 Developer's Guide, Second Edition, by Nabajyoti Barkakati (Indianapolis: Sams Publishing, 1995, ISBN: 0-672-30663-8) Visual C++ 4 Unleashed, by Viktor Toth (Indianapolis: Sams Publishing, 1996, ISBN: 0-672-30874-6) #### A Book on the Microsoft Jet Database Engine The following book offers an excellent reference to the Microsoft Jet database engine: Microsoft Jet Database Engine Programmer's Guide, by Dan Haught and Jim Ferguson (Redmond: Microsoft Press, 1996, ISBN: 1-55615-877-7) #### The Primary Guide to SQL-92 If you want to fully understand the history and implementation of the American National Standards Institute's X3.135.1-1992 standard for SQL-92, you need a copy of Jim Melton and Alan R. Simpson's Understanding the New SQL: A Complete Guide (San Mateo: Morgan Kaufmann Publishers, 1993, ISBN: 1-55860-245-3). Jim Melton of Digital Equipment Corp. was the editor of the ANSI SQL-92 standard, which comprises more than 500 pages of fine print. #### Publishers of Database Standards The syntax of SQL is the subject of a standard published by the American National Standards Institute (ANSI). At the time this book was written, the current standard, X3.135.1-1992 or SQL-92, was available from The American National Standards Institute 11 West 42nd Street New York, NY 10036 (212) 642-4900 (Sales Department) The SQL Access Group (SAG) consists of users and vendors of SQL database management systems. SAG publishes standards that supplement ANSI X3.135.1-1989, such as the Call-Level Interface (CLI) standard used by Microsoft's ODBC API. You can obtain SAG documents from The SQL Access Group 1010 El Camino Real, Suite 380 Menlo Park, CA 94025 (415) 323-7992 (extension 221) ### Keeping Up to Date on Visual C++ A variety of sources of up-to-date information are available to Visual C++ developers in print and electronic formats. Both print periodicals and online sources address management and development issues that are applicable to database development as a whole. Several forums on CompuServe offer product support services for Access and Windows. The sections that follow describe some of the sources you can use to expand your Visual C++ horizons. #### Periodicals The following are a few magazines and newsletters that cover Visual C++ or Access, or in which articles on either appear on a regular basis: Data Based Advisor is published by Data Based Solutions, Inc., a firm related to the publishers of Access Advisor. Data Based Advisor covers the gamut of desktop databases, with emphasis on xBase products, but Visual C++ receives its share of coverage, too. DBMS magazine, published by M&T, a Miller-Freeman company, is devoted to database technology as a whole, but it concentrates on the growing field of client-server RDBMSs. DBMS covers subjects, such as SQL and relational database design, that are of interest to all developers, not just those who use Visual C++. Smart Access is a monthly newsletter from Pinnacle Publishing, Inc., which publishes other database-related newsletters and monographs. Smart Access is directed primarily at developers and Access power users. This newsletter tends toward advanced topics, such as creating libraries and using the Windows API with Access and Visual C++. A diskette is included with each issue. Windows Watcher, Jesse Berst's (now Ziff-Davis') monthly newsletter, analyzes the market for Windows applications, reviews new products for Windows, and provides valuable insight into Microsoft's future plans for Windows 95, Windows NT, and Windows applications, including Visual C++. The majority of these magazines are available from newsstands and bookstores. Names and addresses of the publishers are listed in Appendix A. #### The MSDN Support Product Microsoft sells three sets of subscription services available on CD-ROM called MSDN (Microsoft Developer Network) Level I, MSDN Level II, and MSDN Level III. Level I support consists of quarterly disks that contain a vast amount of information and documentation. These CDs are very valuable to all programmers. Level II support consists of Level I and all the development tools (excluding compilers) such as SDKs and platforms (including versions of Windows, Windows 95, and Windows NT) that Microsoft offers, excluding the BackOffice suite. These CDs (the count varies from quarter to quarter, but usually there are about 20 CDs per release) provide a firm foundation for all professional developers at a very affordable price. Level III support includes Level II plus the Microsoft BackOffice development system. #### Microsoft Internet Services Microsoft and other firms sponsor services on the Internet. Some of the best support can be obtained, without charge, by using the Microsoft Knowledge Base product, which can be accessed from the Internet. This allows you to query the database that the Program Support Services (PSS) people use and obtain answers to technical questions. Contact reference@earthweb.com with questions or comments. Copyright 1998 EarthWeb Inc., All rights reserved. PLEASE READ THE ACCEPTABLE USAGE STATEMENT. Copyright 1998 Macmillan Computer Publishing. All rights reserved. |