vcg22.htm

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

<B>     SQLFreeStmt(hstmt, SQL_CLOSE);</B>

<B>    SQLFreeStmt(hstmt, SQL_UNBIND);</B>

<B>//    END: Got the columns in the specified table:</B>

<B>    SQLDisconnect(hdbc);</B>

<B>    SQLFreeConnect(hdbc);</B>

<B>    SQLFreeEnv(henv);</B>

}

void CDocdbView::OnSelchangeColumn()

{

<B>int    nCurSel = (int)m_ColumnList.GetItemData(m_ColumnList.GetCurSel());</B>

<B>    m_Qualifier = (LPSTR)Columns[nCurSel].szQualifier;</B>

<B>    m_Owner = (LPSTR)Columns[nCurSel].szOwner;</B>

<B>    m_TableName = (LPSTR)Columns[nCurSel].szTableName;</B>

<B>    m_TypeName = (LPSTR)Columns[nCurSel].szTypeName;</B>

<B>    m_Remarks = (LPSTR)Columns[nCurSel].szRemarks;</B>

<B>    m_Precision = Columns[nCurSel].Precision;</B>

<B>    m_Length = Columns[nCurSel].Length;</B>

<B>    m_DataType = Columns[nCurSel].DataType;</B>

<B>    m_Scale = Columns[nCurSel].Scale;</B>

<B>    m_Radix = Columns[nCurSel].Radix;</B>

<B>    m_Nullable = Columns[nCurSel].Nullable;</B>

<B>    UpdateData(FALSE);</B>

}</FONT></PRE>

<P>In this listing you see how the ODBC helper routine GetODBC() has been called, which prompts the user for database, table, and (if applicable) directory names. The names will be stored in the locations specified in the call:

<BR>

<PRE>

<FONT COLOR="#000080"><B>szSource[0] = '\0';</B>

<B>szTable[0] = '\0';</B>

<B>szDirectory[0] = '\0';</B>

<B>GetODBC(</B>

<B>    szSource, sizeof(szSource),</B>

<B>    szTable, sizeof(szTable),</B>

<B>    szDirectory, sizeof(szDirectory));</B></FONT></PRE>

<P>After you have the name of the database and table, you can open the database and determine attributes. This is done using the SQL...() functions, which are described more fully in Chapter 3.

<BR>

<P>The process of using a database consists of the following steps:

<BR>

<OL>

<LI>Allocate the environment using SQLAllocEnv(). This function takes only one parameter, a pointer to an unused environment handle.

<BR>

<BR>

<LI>Allocate the connection using SQLAllocConnect(). This function takes two parameters: the environment handle (returned by the SQLAllocEnv() function) and a pointer to a database connection handle. The connection handle is then used by virtually all the other SQL...() functions.

<BR>

<BR>

<LI>Connect to the database using SQLConnect() (or SQLDriverConnect()) and the connection handle that was returned by the SQLAllocConnect() function, as well as the database connection information such as the database name, user name, and password.

<BR>

<BR>

<LI>After connecting to the database, you must allocate a statement. You do this with the SQLAllocStmt() function.

<BR>

<BR>

<LI>After the statement has been allocated, you can issue an SQL command. For example, you could issue a SELECT * FROM statement to get records from each column in the database. The actual fetching of the data from the columns would be done in a loop that has an SQLFetch() call.

<BR>

<BR>

<LI>When the application is done with the database, you must free the statement handle, disconnect from the database, free the connect handle, and free the environment handle. You do these steps in the opposite order of steps 1 through 4.

<BR>

<BR>

</OL>

<P>The following code fragment shows the function that gets information about the columns in a given table. All the lines that aren't directly related to the database process have been eliminated:

<BR>

<PRE>

<FONT COLOR="#000080"><B>SQLAllocEnv(&amp;henv);</B>

<B>SQLAllocConnect(henv, &amp;hdbc);</B>

<B>RC = SQLDriverConnect(hdbc, m_hWnd,</B>

<B>    (unsigned char far *)szDSN, SQL_NTS,  // strlen(szDSN),</B>

<B>    (unsigned char far *)szConStrOut, sizeof(szConStrOut),</B>

<B>    (short far *)&amp;nConStrOut,</B>

<B>    SQL_DRIVER_COMPLETE);</B>

<B>RC = SQLAllocStmt(hdbc, &amp;hstmt);</B>

<B>RC = SQLColumns(hstmt,</B>

<B>    NULL, 0,    // All Qualifiers</B>

<B>    NULL, 0,    // All owners</B>

<B>    (unsigned char __far *)szTable, SQL_NTS,  // The table!</B>

<B>    NULL, 0);   // All columns</B>

<B>//    Now bind variables to columns!</B>

<B>RC = SQLBindCol(hstmt, 1,  SQL_C_CHAR, szQualifier, STR_LEN,&amp;cbQualifier);</B>

<B>RC = SQLBindCol(hstmt, 2,  SQL_C_CHAR, szOwner, STR_LEN, &amp;cbOwner);</B>

<B>RC = SQLBindCol(hstmt, 3,  SQL_C_CHAR, szTableName, STR_LEN,&amp;cbTableName);</B>

<B>RC = SQLBindCol(hstmt, 4,  SQL_C_CHAR, szColName, STR_LEN, &amp;cbColName);</B>

<B>RC = SQLBindCol(hstmt, 5,  SQL_C_SSHORT,</B>

<B>     &amp;DataType, sizeof(DataType), &amp;cbDataType);</B>

<B>RC = SQLBindCol(hstmt, 6,  SQL_C_CHAR, szTypeName, STR_LEN, &amp;cbTypeName);</B>

<B>RC = SQLBindCol(hstmt, 7,  SQL_C_SLONG,</B>

<B>     &amp;Precision, sizeof(Precision), &amp;cbPrecision);</B>

<B>RC = SQLBindCol(hstmt, 8,  SQL_C_SLONG, &amp;Length, sizeof(Length), &amp;cbLength);</B>

<B>RC = SQLBindCol(hstmt, 9,  SQL_C_SSHORT, &amp;Scale, sizeof(Scale), &amp;cbScale);</B>

<B>RC = SQLBindCol(hstmt, 10, SQL_C_SSHORT, &amp;Radix, sizeof(Radix), &amp;cbRadix);</B>

<B>RC = SQLBindCol(hstmt, 11, SQL_C_SSHORT,</B>

<B>     &amp;Nullable, sizeof(Nullable), &amp;cbNullable);</B>

<B>RC = SQLBindCol(hstmt, 12, SQL_C_CHAR, szRemarks, REM_LEN, &amp;cbRemarks);</B>

<B>while()</B>

<B>{</B>

<B>     RC = SQLFetch(hstmt);</B>

<B>     // Save this column for the future!</B>

<B>}</B>

<B>SQLFreeStmt(hstmt, SQL_CLOSE);</B>

<B>SQLFreeStmt(hstmt, SQL_UNBIND);</B>

<B>SQLDisconnect(hdbc);</B>

<B>SQLFreeConnect(hdbc);</B>

<B>SQLFreeEnv(henv);</B></FONT></PRE>

<BR>

<A NAME="E70E119"></A>

<H5 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Adding CTL3DV2V2.DLL to Create Three-Dimensional, Shaded Dialog Boxes</B></FONT></CENTER></H5>

<BR>

<P>No accoutrements, such as progress-metering gauges, are added to DOCDB, because one of the purposes of this sample application is to demonstrate the ease with which databases created with Access can be accessed by Visual C++ applications. However, the CTL3DV2.DLL library is used to add a gray background and a 3-D effect to dialog boxes and message boxes under some versions of Windows.

<BR>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>At this stage in the life of Windows, only Windows 3.x and Windows NT really need to use CTL32V2.DLL. Windows 95 has this functionality built in. However, there is a version of CTL32V2.DLL for Windows 95 for applications that need it.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<P>CTL3DV2.DLL is a small (about 25K) library that is included with Visual C++, Microsoft Office, and several other Microsoft applications. CTL3DV2 can be found in the \REDIST directory, where the ODBC and SQL redistributable components reside.

<BR>

<P>Because CTL3DV2.DLL is included as part of ODBC's installation, you can generally assume that most users of Windows database applications have a copy of CTL3DV2.DLL in their \WINDOWS\SYSTEMS directory. Excel 5, Word 6, and other Microsoft products released in late 1993 use CTL3DV2.DLL rather than CTL3D.DLL because of problems caused by incompatible versions of CTL3D. Some software suppliers modified CTL3D for their applications without renaming the files. There are also several versions of CTL3DV2.DLL, some of which might not be compatible with Visual C++ and the current releases of other Microsoft applications. The version used in DOCDB is dated 9/16/95, has a file size of 27,136 bytes, and is for Windows NT. For Windows 95, this file is 26,624 bytes long and is dated 9/16/95. CTL3DV2.DLL files with dates later than mid-1995 (when Windows 95 was released) that have approximately the same file size should work as well, because Microsoft often redates files using the release date of the product that the file is distributed with.

<BR>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>TIP</B>

<BR>

<BR>You can obtain a copy of CTL3DV2.DLL by copying it from the \MSDEV\REDIST directory from your Visual C++ CD. For applications that will be created to run solely under Windows 95, you don't need to include CTL3DV2, because this functionality is built into Windows 95. Windows NT users should use the file CTL3D32.DLL (or copy it and rename it CTL32NT.DLL), which is in the \MSDEV\REDIST directory on the Visual C++ 4 CD.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>The CTL3DV2.DLL and CTL3D32.DLL libraries are copyrighted by Microsoft, and the license to distribute Microsoft's copyrighted files in conjunction with your Visual C++ applications does include the right to distribute CTL3DV2.DLL as part of the OLE and ODBC distribution kit you include with your applications. You need to determine whether prospective users of your applications currently have access to CTL3DV2.DLL from \WINDOWS\SYSTEM on their local computers. If it isn't installed, you must install it. If it's already installed, and it's an older version than the one your application is supplying, you should replace it.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<BR>

<A NAME="E70E120"></A>

<H5 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Running DOCDB</B></FONT></CENTER></H5>

<BR>

<P>To run DOCDB, follow these steps:

<BR>

<OL>

<LI>Create and compile the DOCDB project. The source is on the source code disk. Alternatively, you can create your own project using the listings from this chapter. If you're not comfortable using Visual C++, practice by creating your own version of DOCDB.

<BR>

<BR>

<LI>Start the DOCDB program, shown in Figure 22.3. You can run DOCDB either from the Visual C++ workbench or from Windows.

<BR>

<BR><B><A HREF="22vcg03.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/22vcg03.gif">Figure 22.3. DOCDB's dialog box to open the source database file.</A></B>

<BR>

<BR>

<LI>Select the from the list the database you want to open. If your database hasn't been installed in the ODBC system, install it and rerun DOCDB. Click the New Database button to display the SQL Data Sources dialog, shown in Figure 22.4. Figure 22.5 shows the NorthWind example opened in DOCDB.

<BR>

</OL>

<P><B><A HREF="22vcg04.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/22vcg04.gif">Figure 22.4. DOCDB and the dialog allowing a database file to be opened.</A></B>

<BR>

<P><B><A HREF="22vcg05.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/22vcg05.gif">Figure 22.5. The DOCDB program with the NorthWind sample database open.</A></B>

<BR>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>If you receive an Error Opening File message, or if the Column Name combo box is blank, open the database in Access and make sure that the Admin user (or your user ID) has read permissions on all system tables in the database.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>TIP</B>

<BR>

<BR>You can compare the speed of operation of Access and Visual C++ database applications on your computer by timing the execution of both a Visual C++ application and an equivalent application that runs under Access.

<BR>

<BR>Also, if you're competent with Visual C++ 4's DAO, you might want to compare DAO with both ODBC and Access. The results might be enlightening!</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<BR>

<A NAME="E70E121"></A>

<H5 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Altering the Visual C++ Code in DOCDB</B></FONT></CENTER></H5>

<BR>

<P>You have the capability to print reports that contain the information that DOCDB displays in its main window. A CFormView application doesn't have printing enabled by default, and there is no code to print the window's contents. However, it's not difficult to take the information contained in the Columns[] array and format a simple report for your records. The SQLGetInfo() function will return a vast amount of information about drivers, SQL, and datasources.

<BR>

<BR>

<A NAME="E70E122"></A>

<H5 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Exporting Access's Database Documentor Output</B></FONT></CENTER></H5>

<BR>

<P>Access 2's Database Documentor lets you export a database document report; however, it won't export the subreports. This makes the exporting of database documents virtually useless. There is a solution, however, that requires only a minimal amount of work. You must install a generic, text-only printer in Windows, and then assign this printer to a file instead of a printer port. When the Database Documentor report is printed, it will be printed to a standard ASCII file. You can then import this file into Excel as desired. This problem has been fixed in Access 7, which exports in text formats and as an Excel spreadsheet.

<BR>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>Access 7 allows you to directly save your documentation in an Excel, RTF, or DOS text format. If you will be using this documentation in Excel, you should export it directly in the Excel format rather than trying to load the text-formatted file into Excel.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<P>Listing 22.2 shows the NorthWind database documentation saved using the technique just described. This file can be saved directly in an Excel or Word (RTF) format or in a DOS text file format. Of course, any formatting that the report originally contained will be lost if exported in a DOX text file, but the report's information will be complete. In Listing 22.2, I've included only one table for the sake of brevity. The full report as produced by Access is over 50 pages long. (You can find this listing on the CD that comes with this book. It's called rptObjects.txt.)

<BR>

<P>

<FONT COLOR="#000080"><B>Listing 22.2. NorthWind documented by Access's Database Documentor.</B></FONT>

<BR>

<PRE>

<FONT COLOR="#000080">                                                    Thursday, January 18, 1996

Table: Categories                                                      Page: 1

Properties

Date Created:        3/17/94 3:01:37 PM             Def. Updatable:        True

Last Updated:        3/17/94 3:02:46 PM             Record Count:          8

Columns