vcg07.htm

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

<BR>

<BR>

<LI>Three ODBC drivers installed with the Crystal Reports generator, which is supplied with Crystal Reports. These are installed automatically when you install the Crystal Reports module, which is included with Visual C++ 4.0:

<BR>

<BR>CR SQLBase (32-bit)

<BR>CR Oracle7 (32-bit)

<BR>CR Sybase System 10 (32-bit)

<BR>

<BR>

</UL>

<P>The datasources appear in the Add Data Source dialog box, which <A NAME="I2"></A>appears when you launch the ODBC Administrator application (either from Control Panel or by executing ODBCAD32.EXE) and then click the Drivers button in the initial Data Sources dialog box. Figure 7.2 shows the drivers listed in the [ODBC Drivers] section of the system registry.

<BR>

<P><B><A HREF="07vcg02.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/07vcg02.gif">Figure 7.2. The Add Data Source dialog box of the ODBC Administrator application.</A></B>

<BR>

<P>With the Windows 3.1x 16-bit ODBC drivers, SIMBA.DLL is the master ODBC driver for all Microsoft single-tier database drivers. (&quot;Simba&quot; is the Swahili word for &quot;lion.&quot;) With the exception of Excel worksheet and ASCII text files, the database file types supported by SIMBA.DLL are the same as those supported by the Access database engine: Access, dBASE, FoxPro, Paradox, and Btrieve.

<BR>

<P>Windows 95 (and Windows NT 3.5x) uses 32-bit ODBC drivers. ODBC no longer uses the original Simba driver model. Instead, all the ODBC drivers work at the same level, called directly by either ODBCJT32.DLL (for ODBC) or MSJT3032.DLL (for DAO). Examples of the use of Microsoft Query and the Access ODBC Desktop Database Drivers appear in the following sections.

<BR>

<BR>

<A NAME="E70E49"></A>

<H5 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>ODBC.INI</B></FONT></CENTER></H5>

<BR>

<P>Entries in the ODBC.INI initialization file designate the ODBC datasources that appear in the Data Sources dialog box of the ODBC Administrator application. As is the case for ODBCINST.INI, ODBC.INI has a two-tiered structure. A list of datasources appears in ODBC.INI's [ODBC Data Sources] section, followed by a section such as dbCMOLSC that provides additional information required by the ODBC driver for the datasource.

<BR>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>Windows 95 (and Windows NT 3.5x) use the system registry rather than .INI files to store information used by applications and subsystems. Most of the ODBC information is contained in the registry keys (Windows 95) [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC] and (usually) [HKEY_USERS\.Default\Software\ODBC]. The subkeys found under these keys are arranged in a manner similar to the 16-bit .INI files.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<P>For 32-bit ODBC drivers, this information is contained in the system registration database. The 16-bit ODBC drivers save information in the ODBC.INI file. The ODBC.INI file that is used in conjunction with the ODBCINST.INI file described in the preceding section appears as follows:

<BR>

<PRE>

<FONT COLOR="#000080">[ODBC Data Sources]

MS Access 2.0 Databases=Microsoft Access Driver (*.mdb)

Text CSV=Microsoft Text Driver (*.txt; *.csv)

STARmanager=Microsoft dBASE Driver (*.dbf)

Student Registration=Microsoft Access Driver (*.mdb)

Nafta=Access Data (*.mdb)

NorthWind V2=Access Data (*.mdb)

[MS Access 2.0 Databases]

Driver=C:\WINDOWS\SYSTEM\odbcjt16.dll

FIL=MS Access;

DefaultDir=C:\ACCESS

JetIniPath=MSACC20.INI

UID=Admin

DriverID=25

[Text CSV]

Driver=C:\WINDOWS\SYSTEM\odbcjt16.dll

DefaultDir=E:\STARBETA

Description=Text CSV files

DriverId=27

FIL=text;

JetIniPath=odbcddp.ini

[STARmanager]

Driver=C:\WINDOWS\SYSTEM\odbcjt16.dll

DefaultDir=E:\STARBETA

Description=STARmanger

DriverId=21

FIL=dBASE III;

JetIniPath=odbcddp.ini

[Student Registration]

Driver=C:\WINDOWS\SYSTEM\odbcjt16.dll

DriverId=25

JetIniPath=odbcddp.ini

UID=admin

Driver32=C:\WINDOWS\SYSTEM\odbcjt32.dll

[ODBC 32 bit Data Sources]

dBASE Files=Microsoft dBASE Driver (*.dbf) (32 bit)

Excel Files=Microsoft Excel Driver (*.xls) (32 bit)

FoxPro Files=Microsoft FoxPro Driver (*.dbf) (32 bit)

Paradox Files=Microsoft Paradox Driver (*.db ) (32 bit)

CRSS=SQL Server (32 bit)

CRGUP=CR SQLBase (32 bit)

CROR7=CR Oracle7 (32 bit)

CRSYB=CR Sybase System 10 (32 bit)

Text Files=Microsoft Text Driver (*.txt; *.csv) (32 bit)

Student Registration=Microsoft Access Driver (*.mdb) (32 bit)

[dBASE Files]

Driver32=C:\WINDOWS\SYSTEM\odbcjt32.dll

[Excel Files]

Driver32=C:\WINDOWS\SYSTEM\odbcjt32.dll

[FoxPro Files]

Driver32=C:\WINDOWS\SYSTEM\odbcjt32.dll

[Paradox Files]

Driver32=C:\WINDOWS\SYSTEM\odbcjt32.dll

[Nafta]

Driver=C:\WINDOWS\SYSTEM\simba.dll

FileType=RedISAM

DataDirectory=T:\nafta.mdb

SingleUser=True

UseSystemDB=True

SystemDB=C:\MAPNGO\system.mda

[CRSS]

Driver32=C:\WINDOWS\SYSTEM\sqlsrv32.dll

[CRGUP]

Driver32=C:\WINDOWS\SYSTEM\crgup07.dll

[CROR7]

Driver32=C:\WINDOWS\SYSTEM\cror707.dll

[CRSYB]

Driver32=C:\WINDOWS\SYSTEM\crsyb07.dll

[NorthWind V2]

Driver=C:\WINDOWS\SYSTEM\simba.dll

Description=MS NorthWind Sample Database

FileType=RedISAM

DataDirectory=c:\access\sampapps\nwind.mdb

SingleUser=False

UseSystemDB=False

[Text Files]

Driver32=C:\WINDOWS\SYSTEM\odbcjt32.dll</FONT></PRE>

<P>Figure 7.3 shows the entries in the Data Sources dialog box of the ODBC Administrator that correspond to the entries in the preceding ODBC.INI file.

<BR>

<P><B><A HREF="07vcg03.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/07vcg03.gif">Figure 7.3. ODBC datasources for dBASE datasources.</A></B>

<BR>

<P>Figure 7.4 shows the registration database entries for the ODBC SQL Server 32-bit driver. The information in the registry typically matches the ODBC.INI file if both the 16-bit ODBC (which uses ODBC.INI) and the 32-bit ODBC (which is in the registry) are in sync.

<BR>

<P><B><A HREF="07vcg04.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/07vcg04.gif">Figure 7.4. ODBC entries in the system registration database.</A></B>

<BR>

<BR>

<A NAME="E68E46"></A>

<H3 ALIGN=CENTER>

<CENTER>

<FONT SIZE=5 COLOR="#FF0000"><B>Using Desktop ODBC Datasources with Visual C++</B></FONT></CENTER></H3>

<BR>

<P>If you have RED110.DLL, the Access ODBC driver that is supplied with the Microsoft ODBC Desktop Database Drivers kit and with Microsoft Query, you can compare the performance of queries executed against STDREG32.MDB directly by the Access database engine or by the single-tier ODBC driver for Access databases by adding an ODBC datasource created from STDREG32.MDB. The RED110.DLL file is a 16-bit driver. You could also compare the system's performance to a 32-bit driver/application combination.

<BR>

<P>The following sections show you how to create an ODBC datasource named Student Registration from the STDREG32.MDB Access database, run queries against the Student Registration datasource, and trace SQL statements and ODBC function calls.

<BR>

<BR>

<A NAME="E69E149"></A>

<H4 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Adding STDREG32.MDB as an ODBC Datasource</B></FONT></CENTER></H4>

<BR>

<P>To create the Student Registration ODBC datasource, follow these steps:

<BR>

<OL>

<LI>Launch the ODBC Administrator application (called either &quot;ODBC&quot; or &quot;32bit ODBC&quot;) from the Windows Control Panel. If you're using Windows 95, be sure to use the 32-bit version of ODBC.

<BR>

<BR>

<LI>Click the Add button in the Data Sources dialog box, shown in Figure 7.5, to display the Add Data Source dialog box.

<BR>

<BR>

<P><B><A HREF="07vcg05.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/07vcg05.gif">Figure 7.5. Choosing the Access database class for the ODBC datasource.</A></B><BR>

<BR>

<LI>Double-click MS Access 7.0 Database (Microsoft Access Driver (*.mdb) in the Installed ODBC Drivers list box (see Figure 7.2) to display the ODBC Microsoft Access 7.0 Setup dialog box, shown in Figure 7.6. Click the Options button to display the additional choices that are available.

<BR>

<BR>

<P><B><A HREF="07vcg06.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/07vcg06.gif">Figure 7.6. The opening dialog box that is used to create a new Access ODBC datasource.</A></B>

<BR>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>If you don't have MS Access 7.0 Database (Microsoft Access Driver (*.mdb)) displayed in your list of datasources, click the Add button and add the driver from there.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<LI>Enter Student Registration in the Data Source Name text box and a description of the STDREG32.MDB database in the Description text box (use Student Registration if you can't think of anything else). Click the Select button to open the Select Database dialog box.

<BR>

<BR>

<LI>If you're using Visual C++ 4.0, the STDREG32.MDB database is found in the directory V:\MSDEV\SAMPLES\MFC\DATABASE\STDREG, where V: is either the drive where you installed the sample applications or the Visual C++ 4.0 distribution CD. Users of other versions of Visual C++ should use the command dir /s for stdreg*.mdb to find the file. The non-Access 7 version of STDREG is usually called STDREG.MDB. Select the STDREG32.MDB database file as the datasource and close the Select Database dialog box.

<BR>

<BR>

<LI>Make sure that the None option button is selected, unless you have the correct version of Access installed and you want to use your SYSTEM.MDA library to demonstrate password-protected ODBC logon procedures. Your ODBC Microsoft Access 7.0 Setup dialog box appears as shown in Figure 7.7.

<BR>

<BR>

<P><B><A HREF="07vcg07.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/07vcg07.gif">Figure 7.7. The ODBC Microsoft Access 7.0 Setup dialog box that is used to create the Student Registration datasource.</A></B><BR>

<BR>

<LI>Click the OK button to close the dialog box, and then click the Close button of the Data Sources dialog box to exit the ODBC Administrator application.

<BR>

<BR>

</OL>

<P>You now have an ODBC datasource, Student Registration, that you can use with any application that supports connections to ODBC datasources, such as Visual C++, Access, and Excel with Microsoft Query.

<BR>

<BR>

<A NAME="E69E150"></A>

<H4 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Using the Student Registration Datasource in a Visual C++ Program</B></FONT></CENTER></H4>

<BR>

<P>To open the Student Registration datasource, follow these steps:

<BR>

<OL>

<LI>Open the Visual C++ sample application \MSDEV\SAMPLES\MFC\TUTORIAL\ENROLL\STEP4 and build the program if necessary. Start the program to automatically open the Student Registration datasource.

<BR>

<BR>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>When you're building the examples supplied with Visual C++ 4, they must be located on a drive that you have the ability to write to. If you haven't copied the example from the CD to a hard drive, you must do so before you try to build it. You can't write to the Visual C++ 4 distribution CD.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<LI>Try using each of the functions available from the ENROLL program. If you have MS Query or Access, you can compare these two applications with the ENROLL program. Because MS Query uses ODBC to access datasources, MS Query won't exhibit much better performance than ENROLL.

<BR>

<BR>

</OL>

<BR>

<A NAME="E69E151"></A>

<H4 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Tracing the Execution of ODBC Function Calls and SQL Statements</B></FONT></CENTER></H4>

<BR>

<P>To trace the behind-the-scenes activity that occurs when you execute a query from any application that uses ODBC to access a datasource, you need to enable ODBC's data logging option. You set this option using the ODBC Administrator (found in Control Panel). You must enable ODBC event logging by clicking the Options button in the ODBC Administrator Data Sources dialog box. The ODBC Options dialog box, shown in Figure 7.8, appears.

<BR>

<P><B><A HREF="07vcg08.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/07vcg08.gif">Figure 7.8. The ODBC Administrator's ODBC Options dialog box.</A></B>

<BR>

<P>In this dialog box, you can specify that all ODBC transactions be traced (that is, logged) to a file. You also can select the Stop Tracing Automatically check box to make the logging active for the current session.

<BR>

<P><B>WARNING</B>

<BR>

<BR>When ODBC tracing is enabled, ODBC's performance will be substantially affected. Enable tracing only when needed, and turn tracing off as soon as you're done using it.

<BR>

<BR>If you don't turn logging off, the resulting tracing files ultimately grow to mammoth proportions. Therefore, you must be careful not to fill all your disk's free space with the ODBC trace file, because any application that interacts with ODBC can write thousands of records to the file.

<BR>

<A NAME="E68E47"></A>

<H3 ALIGN=CENTER>

<CENTER>

<FONT SIZE=5 COLOR="#FF0000"><B>Using Microsoft Query and the Desktop Database Drivers</B></FONT></CENTER></H3>

<BR>

<P>One of the many advantages of upgrading to (or acquiring) Excel 7, Word for Windows 7, or Microsoft Office 95 is that Microsoft Query and the suite of Microsoft Desktop Database Drivers are bundled with these three mega-apps. Microsoft Query is very useful for learning how to write ANSI SQL statements and how to use the Desktop Database drivers that accompany MS Query. MS Query uses ANSI SQL, with modifications to accommodate ODBC data types, not Access SQL to execute SELECT queries. (MS Query doesn't use the Access database engine to process queries.) Thus, you can use MS Query to validate SQL SELECT query statements you write in the form of string variables in your Visual C++ database applications that use the SQL pass-through option.

<BR>

<P>Microsoft Query also is part of Visual C++ 1.5x. Since Visual C++ 2.x includes Visual C++ 1.5x, you should always be able to find a copy of Microsoft Query there.

<BR>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>TIP</B>

<BR>

<BR>If nothing else, using MS Query is a way to avoid typing a lengthy query string. Do this by designing your query interactively, and then use the Clipboard to cut and paste the resulting SQL string.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<P>The following sections describe how to create an ODBC datasource, MSQueryDBF, and how to use Microsoft Query as a stand-alone application to execute queries against the datasource.

<BR>

<BR>

<A NAME="E69E152"></A>

<H4 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Creating an ODBC Datasource from dBASE Files</B></FONT></CENTER></H4>

<BR>

<P>Microsoft Query comes with a sample dBASE III database whose .DBF files are located, along with the Microsoft Query executable files, in your \WINDOWS\MSAPPS\MSQUERY directory. To create a dBASE III ODBC datasource called MSQueryDBF from these files, follow these steps:

<BR>

<OL>

<LI>Launch Microsoft Query (you probably will have an icon in either your Excel or Word folder). Choose File | New Query or click the New Query button to open the Select Data Source dialog box. An empty Select Data Source dialog box appears, as shown in Figure 7.9, if you haven't previously added an MS Query datasource.

<BR>

<BR>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>There are two versions of MS Query: a 16-bit version in MSQUERY.EXE and a 32-bit version in MSQRY32.EXE. You should use the 32-bit version if possible because it's compatible with the 32-bit ODBC drivers used with Windows NT and Windows 95.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<P><B><A HREF="07vcg09.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/07vcg09.gif">Figure 7.9. Microsoft Query's Select Data Source dialog box.</A></B>

<BR><BR>

<LI>Click the Other button to open the ODBC Data Sources dialog box. ODBC datasources you have created previously, listed in ODBC.INI, appear in the ODBC Data Sources dialog box, shown in Figure 7.10.

<BR>

<BR>

<P><B><A HREF="07vcg10.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/07vcg10.gif">Figure 7.10. Microsoft Query's ODBC Data Sources dialog box.</A></B><BR>

<BR>

<LI>Click the New button to display the Add Data Source dialog box, shown in Figure 7.11.

<BR>

<BR>

<P><B><A HREF="07vcg11.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/07vcg11.gif">Figure 7.11. The ODBC Administrator's Add Data Source dialog box.</A></B><BR>

<BR>

<LI>Double-click the dBASE Files (*.dbf) entry to open the ODBC dBASE Setup dialog box, shown in Figure 7.12.

<BR>

<BR>

<P><B><A HREF="07vcg12.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/07vcg12.gif">Figure 7.12. The ODBC dBASE Setup dialog box.</A></B>

<BR><BR>

<LI>Click the Options button to display the expanded version of the dialog box. Click the dBASE III option button and the Select Directory command button to display the Select Directory dialog box.

<BR>

<BR>

<LI>Choose the \WINDOWS\MSAPPS\MSQUERY directory, where six .DBF files appear, and then click the OK button to close the Select Directory dialog box.

<BR>

<BR>

<LI>Complete the entries in the ODBC dBASE Setup dialog box as shown in Figure 7.13. Click the OK button to close the dialog box and display the ODBC Data Source dialog box.

<BR>

<BR>

<P><B><A HREF="07vcg13.gif" tppabs="http://2