vcg20.htm

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

<P>SQL Server 6.0 has new features that simplify installation and aid in the administration of SQL Server servers and clients. Here are the most important new SQL Server features:

<BR>

<UL>

<LI>The capability to address up to 2GB of RAM. Increasing the amount of RAM to create large data caches greatly improves SQL Server's performance. 32M is the smallest amount of RAM you should consider for an SQL Server server. Installations with more than 10 users should use servers with 48M to 64M of RAM. SMP servers should start with 128M of RAM.

<BR>

<BR>

</UL>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>In addition to having sufficient RAM, it's important to have a very fast data path to the system's hard disks. Pentium systems with the PCI bus structure, using the Adaptec 2940w PCI SCSI controller, offer an incredible rate of transfer between the hard disks and the system.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<UL>

<LI>Graphical database management tools. SQL Server includes 16-bit Windows and 32-bit Windows NT versions of the SQL Administrator, SQL Security Manager, SQL Enterprise Manager, SQL Transfer Manager, and the ISQL command-line SQL processor that you can execute from Windows for Workgroups, Windows 95, or Windows NT workstations. Figure 20.1 shows the SQL Server for Windows NT application group that appears in the server computer's Program Manager window. Windows NT provides additional Setup, Service Manager, Tape Utility, Security Manager, and Performance monitoring tools. These management tools are discussed in the sections that follow.

<BR>

<BR>

</UL>

<P><B><A HREF="20vcg01j.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/20vcg01j.gif">Figure 20.1. The SQL Server application group on an SQL Server.</A></B>

<BR>

<UL>

<LI>Extended stored procedures. Extended stored procedures that carry the prefix xp_ instead of sp_ let you call functions in Windows DLLs or execute Windows NT command-line instructions directly from SQL Server rather than from your front-end application. As an example, you can use the command-line instruction execute master..xp_cmdshell &quot;net send <I>groupname</I> '<I>Message</I>'&quot; to transmit a message to members of a group based on a conditional expression in a Transact SQL trigger. (Transact SQL is the dialect of SQL used by both the Microsoft and Sybase flavors of ANSI SQL. SQL Server triggers are explained later in this chapter.)

<BR>

<BR>

<LI>Simplified installation and setup. As with Windows NT Server 3.5, installing SQL is a simple process. You can perform a basic installation of Windows NT Server 3.5 from a CD-ROM, add SQL Server (from its own CD-ROM or the distribution disks) as a Windows NT Server 3.5 process, and get one or two WfWg or Windows 95 workstations running in an hour or less. Installation time doesn't include the time required to fully configure SQL Server or Windows NT Server 3.5.

<BR>

<BR>

<LI>The capability to store the tempdb database in RAM. SQL Server uses the temporary database, tempdb, for workspace and temporary tables that are needed for some sorting and JOIN operations. If you have sufficient RAM for tempdb and the data cache, you can specify that the tempdb database be stored in memory. Storing tempdb in RAM speeds operations that use temporary tables when SQL Server needs to use tempdb.

<BR>

<BR>

<LI>Tape-backup procedures. Automatic timed backup of the database and transaction log is provided as a function of the SQL Object Manager application. You can perform a dump of more than one object to a single tape. This is an important consideration when you use certified tapes for DAT back-up drives. These tapes are expensive.

<BR>

<BR>

</UL>

<P>The following two sections demonstrate how to use the Microsoft Enterprise Manager to create a new database object, nwind, in the MASTER.DAT device, and how to add a sample table, test, to the nwind database.

<BR>

<BR>

<A NAME="E70E115"></A>

<H5 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Using the Microsoft Enterprise Manager to Create a New Database</B></FONT></CENTER></H5>

<BR>

<P>The Microsoft Enterprise Manager lets you create and manage the following types of objects:

<BR>

<UL>

<LI>Devices, such as new database (.DAT) files and data dump devices that save data to fixed disk and diskette files as well as to tape drives. When you install SQL Server, default master database file (MASTER.DAT), diskdump, diskettedumpa (drive A), and diskettedumpb (drive B) devices are created for you.

<BR>

<BR>

<LI>Databases that exist within database file devices. You can add new databases or drop databases from .DAT files. During installation, SQL Server creates the master, model, pubs, and tempdb databases within the MASTER.DAT device.

<BR>

<BR>

<LI>Segments that constitute a portion of one or more databases. You use segmented databases to spread a database over more than one disk drive. Segmentation improves the performance of large databases stored on RAID 0 or RAID 5 disk arrays. (Segments actually are pointers to the location of the segment.)

<BR>

<BR>

</UL>

<P>The advantage of the Microsoft Enterprise Manager is that you don't need to type Transact SQL statements at the ISQL command prompt or in the ISQL/w application's query pane to create or modify devices. The following example shows you how to use SQL Administrator to create an nwind database in the MASTER.DAT device:

<BR>

<OL>

<LI>Launch the Microsoft Enterprise Manager from the SQL Server application group.

<BR>

<BR>

<LI>The Register Server dialog box, shown in Figure 20.2, appears. If you have more than one server in your domain, go to the Server combo box and select the server to which you want to connect.

<BR>

<BR><B><A HREF="20vcg02j.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/20vcg02j.gif">Figure 20.2. The Register Server dialog box in the Microsoft Enterprise Manager.</A></B>

<BR>

<BR>

<LI>Type your server user ID and password in the Login ID and Password text boxes. If you haven't established an account for yourself, type sa (for &quot;system administrator&quot;) in the Login ID text box and leave the Password text box empty.

<BR>

<BR>

<LI>Click the Register button to create a connection to the server and activate SQL Administrator's main window.

<BR>

<BR>

<LI>Click the DB button on the toolbar or choose Manage | Database to display the Manage Databases MDI child window, shown in Figure 20.3.

<BR>

<BR><B><A HREF="20vcg03j.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/20vcg03j.gif">Figure 20.3. The default databases in MASTER.DAT.</A></B>

<BR>

<BR>

<LI>Click the New Database icon to display the New Database dialog box, shown in Figure 20.4.

<BR>

<BR><B><A HREF="20vcg04j.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/20vcg04j.gif">Figure 20.4. The New Database dialog box for adding a new database and log to the MASTER.DAT device.</A></B>

<BR>

<BR>

<LI>Type NorthWind in the Name text box and accept the default master as the Data Device and (none) as the Log Device. Type 2 in the Size (MB) text box to create a database object with a maximum size of 2M. Then click the OK button. (NorthWind.MDB is more than 1M in size, and you can specify database sizes only in 1M increments.)

<BR>

<BR>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>Most client-server RDBMSs require that you specify a maximum size for a database and its corresponding transaction log file. This is a sample database, so you don't need a transaction log file to restore the database in case of hardware failure. (In order to store the log file, you need to create a new device located on a disk drive other than the one on which the database file device is located.) The default size of an SQL Server database is the remaining space in the database file device.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<LI>After a few seconds, the New Database Dialog disappears and the Manage Databases dialog appears, showing the new database, NorthWind (see Figure 20.5).

<BR>

<BR><B><A HREF="20vcg05j.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/20vcg05j.gif">Figure 20.5. The Manage Databases dialog box, confirming the creation of a new database in the MASTER.DAT device.</A></B>

<BR>

<BR>

<LI>Close Enterprise Manager by double-clicking the application control menu box or by choosing File | Exit. Closing SQL Administrator also closes the connection to the server.

<BR>

<BR>

</OL>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>RDBMSs that have their roots in UNIX conventionally use lowercase letters to identify all database devices and objects, just as it is the norm (actually, a requirement) to use uppercase letters to identify database objects in mainframe databases such as IBM DB2.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<BR>

<A NAME="E70E116"></A>

<H5 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Using SQL Object Manager to Add a Test Table to NorthWind</B></FONT></CENTER></H5>

<BR>

<P>You can use Enterprise Manager to create or modify objects in database containers. Using Enterprise Manager isn't much different from creating a new table in Access's table-design mode or using the table-design function of the MSQuery sample application.

<BR>

<P>To add a sample table called &quot;test&quot; to the NorthWind database and define some sample fields, follow these steps:

<BR>

<OL>

<LI>Using the procedure described in steps 2, 3, and 4 in the preceding section, launch Enterprise Manager and connect to the server to display the Enterprise Server Manager window, shown in Figure 20.6.

<BR>

<BR><B><A HREF="20vcg06j.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/20vcg06j.gif">Figure 20.6. The Enterprise Server Manager Window.</A></B>

<BR>

<BR>

<LI>Select the desired server and expand its outline. From the expanded outline, choose the NorthWind database.

<BR>

<BR>

<LI>Choose Manage | Tables to open the Manage Tables window, shown in Figure 20.7. The default name for a new database table is, not surprisingly, &lt;New&gt;.

<BR>

<BR><B><A HREF="20vcg07j.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/20vcg07j.gif">Figure 20.7. Adding fields to a new table definition with SQL Object Manager.</A></B>

<BR>

<BR>

<LI>Add some fields to the table by entering a field name in the Column Name cell, and then choose a data type for the field by selecting the Datatype cell. Figure 20.7 shows typical field names and Transact SQL data types for the test table.

<BR>

<BR>

<LI>After you've added a representative collection of fields to the test table, click the Save icon. The Specify Table Name dialog, shown in Figure 20.8, appears.

<BR>

<BR><B><A HREF="20vcg08j.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/20vcg08j.gif">Figure 20.8. The Specify Table Name dialog box, in which you name a new table.</A></B>

<BR>

<BR>

<LI>Type test in the New table name text box and click the OK button to add the fields to the table. Notice that the outline under the NorthWind database has changed to include the new table (see Figure 20.9). Other database objects, Rules, Stored Procedures, and Views, can be manipulated in a similar way.

<BR>

<BR><B><A HREF="20vcg09j.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/20vcg09j.gif">Figure 20.9. The Server Manager window after you complete the design of the test table.</A></B>

<BR>

<BR>

<LI>Close the Enterprise Manager application.

<BR>

<BR>

</OL>

<P>The test table created in the preceding steps is used later in this chapter to demonstrate the correspondence between Transact SQL data types and the data types supported by the Access database engine.

<BR>

<BR>

<A NAME="E68E103"></A>

<H3 ALIGN=CENTER>

<CENTER>

<FONT SIZE=5 COLOR="#FF0000"><B>Creating ODBC Datasources from Client-Server Databases</B></FONT></CENTER></H3>

<BR>

<P>Examples in Chapter 7 explain the procedure for creating ODBC datasources from desktop databases using the ODBC database drivers included with Microsoft Office, Excel, and the Microsoft ODBC Desktop Database Drivers kit. The procedure described in the following section for adding the NorthWind database as an ODBC datasource is typical of the method of adding a datasource from the majority of popular client-server RDBMSs for which ODBC drivers are available. Each supplier has a slightly different approach to the design and collection of control objects in the dialog boxes used to set RDBMS-specific options for the datasource.

<BR>

<P>The following two sections describe how to add the NorthWind database as an SQL datasource and how to test the validity of your new ODBC datasource with the MS Query application.

<BR>

<BR>

<A NAME="E69E254"></A>

<H4 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Adding the nwind Database as an ODBC Datasource</B></FONT></CENTER></H4>

<BR>

<P>To use the SQL Server ODBC database driver to add the NorthWind database as an ODBC, follow these steps:

<BR>

<OL>