vcg20.htm

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

<LI>Launch the ODBC Administrator application. If you installed the ODBC Desktop Database Drivers kit, Microsoft Office, or Microsoft Excel (or almost any ODBC application, such as ReportSmith), the ODBC Administrator's icon is located in Control Panel's window to display the Data Sources dialog box.

<BR>

<BR>

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

<BR>

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

<BR>

<BR>

<LI>Choose SQL Server from the Installed ODBC Drivers list box and click the OK button to display the ODBC SQL Server Setup dialog box, shown in Figure 20.11.

<BR>

<BR><B><A HREF="20vcg11j.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/20vcg11j.gif">Figure 20.11. Creating a NorthWind ODBC datasource.</A></B>

<BR>

<BR>

<LI>Click the Options button to expand the ODBC SQL Server Setup dialog box and type NorthWind in the Data Source Name text box. Add an optional description in the Description text box.

<BR>

<BR>

<LI>Go to the Server combo box and select the server for the NorthWind database. If no entries appear, type in the server name. Accept the (Default) entries for the Network Address and Network Library if you're using NetBEUI (the default) as your network protocol.

<BR>

<BR>

<LI>Type NorthWind in the Database Name text box and select (Default) in the Language Name combo box.

<BR>

<BR>

<LI>Click the OK button to close the ODBC SQL Server Setup dialog box. Your new datasource appears in the Data Sources dialog box, as shown in Figure 20.12.

<BR>

<BR><B><A HREF="20vcg12j.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/20vcg12j.gif">Figure 20.12. The Data Sources dialog box with the new Northwind datasource added.</A></B>

<BR>

<BR>

<LI>Click the Close button to close the Data Sources dialog box.

<BR>

<BR>

</OL>

<BR>

<A NAME="E69E255"></A>

<H4 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Testing the ODBC Datasource with the MS Query Application</B></FONT></CENTER></H4>

<BR>

<P>The ODBC Administrator application doesn't test the validity of your ODBC datasource when you create the datasource within the ODBC Server Setup dialog box. Therefore, you need to verify that the entries created by the ODBC Administrator application in your ODBC.INI file are correct. The entries in the [ODBC Data Sources] and [NorthWind] sections of ODBC.INI in your \WINDOWS directory (assuming that you're using WfWg or Windows 95 and not Windows NT) are as follows. A more thorough discussion of ODBC and the Windows 95 registry is found in Chapter 7.

<BR>

<PRE>

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

Northwind=Access Data (*.mdb)

Bibliography=Access Data (*.mdb)

MSQueryDBF=dBase Files (*.dbf)

AccessCrosstab=Access Data (*.mdb)

nwind=SQL Server

[NorthWind]

Driver=D:\WINDOWS\SYSTEM\sqlsrvr.dll

Description=Northwind Traders on SQL Server

Server=oakleaf0

Database=nwind

OemToAnsi=No

LastUser=sa

34Cancel=</FONT></PRE>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>The first four entries in the [ODBC Data Sources] section of ODBC.INI result from the examples of using the desktop database drivers that are described in Chapter 9, &quot;Designing a Decision-Support Application.&quot; All the entries in the [NorthWind] section result from the entries in the ODBC SQL Setup dialog box described in the preceding section.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<P>To test your SQL Server ODBC datasource, follow these steps:

<BR>

<OL>

<LI>Launch MS Query using the MS Query icon or the Microsoft Office Toolbar button for MS Query.

<BR>

<BR>

<LI>From MS Query, choose File | New Query.

<BR>

<BR>

<LI>Choose NorthWind from the Select Data Source dialog box, shown in Figure 20.13, and click the Use button.

<BR>

<BR><B><A HREF="20vcg13j.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/20vcg13j.gif">Figure 20.13. The Select Data Source dialog box.</A></B>

<BR>

<BR>

<LI>A list of all the tables in the NorthWind database appears in MS Query's Select Table dialog.

<BR>

<BR>





<BR>

<BR>

<LI>Getting this far validates your connection to the NorthWind database. To display and modify the structure of the test table, click the Close button. Select File | Table Structure, choose the NorthWind database, and click the View button. The View Table Definition dialog box appears, as shown in Figure 20.14.

<BR>

<BR><B><A HREF="20vcg15j.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/20vcg15j.gif">Figure 20.14. The View Table Definition dialog box.</A></B>

<BR>

<BR>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>The Access database engine doesn't convert some of the Transact SQL field data types to the expected Access field data types. The primary problem is that the longbinary field, specified as Transact SQL's image data type, which is not supported by Access, converts to a binary field of 0 length instead of the expected longbinary field of variable length. Access also doesn't support fixed-length binary fields. These problems are expected to be resolved by future versions of the SQL Server ODBC driver and the Access database engine.

<BR>

<BR>For more information on type conversions, refer to the following topics found in the ODBC 2.0 <I>Programmer's </I><I>Reference:</I>

<BR>

<BR>Converting Data from SQL to C Data Types

<BR>

<BR>Core C Data Types

<BR>

<BR>Extended C Data Types</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<LI>Click the Cancel button in the View Table Definition dialog box to return to MS Query's main window, and then close MS Query.

<BR>

<BR>

</OL>

<P>Regardless of the problems relating to conversion between Transact SQL and Access data types, you have verified the operability of your SQL Server ODBC datasource.

<BR>

<BR>

<A NAME="E69E256"></A>

<H4 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Using Visual C++'s <I>SQLConfigDataSource()</I> Function</B></FONT></CENTER></H4>

<BR>

<P>The SQLConfigDataSource() function lets you perform the same functionality as making entries in an ODBC setup dialog box (such as the ODBC SQL Server Setup dialog box, shown in Figure 20.11). You can use the SQLConfigDataSource() function to create, delete, or modify ODBC datasource entries in ODBC.INI, identical to those you create with the ODBC Administrator's setup procedure. Your code needs to execute the SQLConfigDataSource() function before executing the OpenDatabase() function that refers to the ODBC datasource that has been modified by the SQLConfigDataSource() function.

<BR>

<P>The syntax of the SQLConfigDataSource() instruction is as follows:

<BR>

<PRE>

<FONT COLOR="#000080">//      Add the datasource to ODBC for the user:

    sprintf(szBuffer,

        &quot;DESCRIPTION=STARmanager saved DB.|&quot;

        &quot;DSN=StarDatabase files|&quot;

        &quot;FileType=RedISAM|&quot;

        &quot;SingleUser=True|&quot;

        &quot;UseSystemDB=FALSE|&quot;

        &quot;DataDirectory=%s|&quot;

        &quot;DATABASE=%s||&quot;, szOutFileName, szOutFileName);

        nLength = (int)strlen(szBuffer);

        for (i = 0; i &lt; nLength; i++)

        {

            if (szBuffer[i] == '|')

            {

                 szBuffer[i] = '\0';

            }

        }

        SQLConfigDataSource(NULL, ODBC_ADD_DSN, &quot;Access Data (*.mdb)&quot;,

            szBuffer);</FONT></PRE>

<P>SQLConfigDataSource()'s prototype is

<BR>

<BR>

<PRE>

<FONT COLOR="#000080">SQLConfigDataSource(<I>hwndParent</I>, <I>fRequest</I>, <I>lpszDriver</I>, <I>lpszAttributes</I>);</FONT></PRE>

<P>The <I>hWndParent</I> argument is the parent window's handle. The <I>fRequest</I> argument is one of the following manifest constants: ODBC_ADD_DSN, ODBC_CONFIG_DSN, or ODBC_REMOVE_DSN. The <I>lpszDriver</I> argument points to a string that contains the driver description (typically the name of the associated DBMS) that is presented to the user in lieu of the physical driver's name. The <I>lpszAttributes</I> argument points to a string containing a list of keyword-value pairs. A typical <I>lpszAttributes</I> string might contain this:

<BR>

<BR>

<PRE>

<FONT COLOR="#000080">DSN=Personel Data\0OUID=Jones\0OPWD=Password\0DATABASE=Personel\0</FONT></PRE>

<P>This code fragment shows the keyword-value pairs for an Access database. These keyword-value pairs are described further in the SQLDriverConnect() function's description and in the documentation for each specific ODBC driver.

<BR>

<P>When an ODBC_CONFIG_DSN request is made, any of the values in the <I>lpszAttributes</I> argument that don't match current entries in the ODBC.INI topic for the datasource are updated. Attribute values aren't case-sensitive.

<BR>

<BR>

<A NAME="E68E104"></A>

<H3 ALIGN=CENTER>

<CENTER>

<FONT SIZE=5 COLOR="#FF0000"><B>Creating Tables in ODBC Datasources</B></FONT></CENTER></H3>

<BR>

<P>You can create new tables in an ODBC database by exporting the structure and data of Access tables to the ODBC datasource using the retail version of Access. The most common situation in which you need to export tables is when you upgrade a Visual C++ database application that uses the Access .MDB file structure to a client-server RDBMS front end. Exporting Access tables is often a more convenient method of creating RDBMS tables than using SQL Object Manager or writing SQL CREATE TABLE statements to define the table's structure. Exporting table data is simpler but slower than using SQL Server's bulk copy program, BCP, or equivalent applications of other RDBMSs to add records to the table. The following sections describe how to use Access to export tables from NorthWind.MDB to the NorthWind database on SQL Server.

<BR>

<BR>

<A NAME="E69E257"></A>

<H4 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Exporting Tables in NorthWind.MDB to the NorthWind Database on SQL Server</B></FONT></CENTER></H4>

<BR>

<P>The Northwind Traders sample database supplied with Access provides a variety of tables that you can use to test the capabilities of both Access and the ODBC API to create tables in client-server RDBMSs. However, using Access's export feature with the ODBC API and the latest version of the SQL Server ODBC driver isn't a rapid process when it involves tables that contain a large number of records. Be prepared for a very long wait if you export NorthWind.MDB's Order Details table to SQL Server. If you have less than 8M of RAM on the workstation running Access, don't even think about trying the following example; the wait becomes interminable.

<BR>

<P>To export an Access table to SQL Server, follow these steps:

<BR>

<OL>

<LI>Open Access and NorthWind.MDB.

<BR>