Up to this point, this book has used desktop database files in all the examples of Visual C++ database applications. Sample applications that use Access database files predominated in the preceding chapters because the .MDB file format is Microsoft's (and the authors') preferred database file structure for applications that don't need to maintain interoperability with existing DOS applications that share dBASE, FoxPro, or Paradox 3+ table files. Another reason for using the .MDB database file structure is that .MDB files resemble the file structures used by client-server databases.
This chapter introduces you to the principles employed to create Visual C++ database applications that serve as front ends for client-server RDBMS back ends. The RDBMS used for the majority of the examples in this chapter is Microsoft SQL Server for Windows NT 6.0 (SQL Server) running as a service of Windows NT Server 3.51 (NTS). Therefore, this chapter begins with a description of SQL Server and the use of the new SQL Administrator and SQL Object Manager database tools provided with SQL Server 6.0.
The examples in this chapter use the Microsoft ODBC API (version 2.0) and use either the MFC database classes or the SQL...() functions. The examples are equally applicable to most client-server RDBMSs for which ODBC drivers that support core-level SQL grammar and offer level-1 ODBC compliance are available. For example, a program that interacts with an Access .MDB file could easily interact with an xBase file as well. Chapter 7, "Using the Open Database Connectivity API," describes Visual C++ 's interface to the ODBC API in detail and explains how to use single-tier ODBC drivers. Therefore, this chapter discusses only those elements of the ODBC API, such as creating client-server ODBC datasources, that are specific to multiple-tier ODBC drivers and Visual C++ front ends for client-server RDBMSs. This chapter includes an example of exporting data from Access databases to client-server tables because Visual C++ developers often are called on to upgrade Visual C++ (and Access) applications from desktop RDBMSs to client-server database front ends.
Designing Visual C++ front ends for client-server databases follows the same methodology as designing applications that manipulate tables of conventional desktop databases, as described in earlier chapters. If you have an RDBMS table structure that duplicates the structure of the tables that compose your desktop database, you can simply change the open statements to open tables in the ODBC datasource instead of those in the desktop database. In most cases, however, using the SQLExecDirect() function instead of the SQLExecute() method greatly improves the performance of client-server front ends when the query is a one-time execution. Thus, this chapter ends with a discussion of how to use SQL pass-through with client-server RDBMSs.
Microsoft SQL Server for Windows NT is a major upgrade from Microsoft SQL Server version 4.21. In this chapter, the term SQL Server refers to SQL Server for Windows NT version 6.0; references to the Sybase version for UNIX computers are preceded by the word "Sybase." You can run SQL Server for Windows NT 6.0 either under Windows NT Workstation 3.5 or Windows NT Server 3.5. The SQL Server Setup program can install client software on SQL Server, MS-DOS, Windows, and Windows NT operating environments.
NOTE
Prices and configurations of products such as SQL Server change frequently. You should think of the prices and configurations described next as a quick reference only. Before you make any decisions about these products, contact your favorite reseller.
SQL Server is available in the following license versions (the prices are based on MSRP and were verified in early 1995):
NOTE
In mid-1995, Oracle began giving away its product to anyone who wanted to pay shipping and handling or was willing to spend the time downloading the product from Oracle's Internet site for a 30-day evaluation. Only time will tell whether this approach will be successful. However, it's difficult to compete against a free product, especially when that product is as high-quality as Oracle's.
The networking features of Windows NT Server 3.5 and SQL Server are designed for maximum user convenience. For example, you can set up SQL Server so that users are automatically logged on to it when they log on to Windows NT Server 3.5. Automatic logon to the SQL Server server also is provided across remote servers in Windows NT Server 3.5 domains that share a trust relationship.
SQL Server supports simultaneous NetBEUI, IPX/SPX, and TCP/IP connections.
A conversation between an application and SQL Server takes place through one or more connections established by the named-pipes service that originated in OS/2. A connection is similar in concept to that of the Windows Sockets for TCP/IP communication described in the preceding chapter. The default named-pipes protocol for communicating with SQL Server is provided by the DBNMP3.DLL library, which must be present in your \WINDOWS\SYSTEM or WINNT\SYSTEM directory or the current directory for the application. Named-pipes protocols for the other network operating systems are included on the SQL Server disks.
NOTE
The following information relating to the Access database engine's use of SQL Server connections is based on Microsoft's "Jet Database Engine ODBC Connectivity" white paper, dated July 19, 1993, and written by Neil Black of Microsoft's Jet Program Management group and Stephen Hecht of the Jet Development group. You can download this white paper in Word for Windows format as RJETWP.ZIP from the ODBC Library 11 of the MSACCESS forum on CompuServe. If you use or intend to use ODBC with Visual C++ database applications, you need a copy of this white paper to understand how the Access database engine interacts with the ODBC drivers for client-server databases.
When you use the Access database engine to connect to a server back end with the ODBC API and create a Dynaset object from a query, the operation usually requires at least two connections. (One connection obtains information about the query from the server, and the other passes the data to your application.) If your query is based on a complex SQL statement that involves multiple JOINs or GROUP BYs or creates a crosstab query, it's likely that your application will open several connections at once. Although the Access database engine attempts to share connections when it can, there are many situations in which your application might need several simultaneous connections.
TIP
In a multiuser environment, it's possible to run out of connections with substantially fewer simultaneous users than you've specified as the maximum number of users for SQL Server. It's a common practice to set the number of users as low as practical to conserve memory resources, which are required for each user, for data caching. If you run out of connections, as indicated by an error message returned when your Visual C++ application attempts to run a query, your only solution is to increase the number of users. To do this, you might need to upgrade your SQL Server user license.
With today's trend toward more and more memory in servers, the practice of limiting connection counts to conserve memory might not be meaningful. Also, in regard to licensing, SQL Server will count each workstation as a single connection regardless of how many actual connections there are between the workstation and the server.
OS/2, Windows 95, and Windows NT are multithreaded operating systems. A thread is a single task that is executed by an application or the operating system. Multithreaded operating systems enable multiple tasks to appear to run simultaneously; the operating system determines the priority of the task and enables tasks with the highest priority to execute first and most often. Tasks with higher priorities can suspend the processing of lower-priority tasks. Operating systems that let task priorities govern tasks' flow of execution are called preemptive multitasking operating systems.
Windows NT provides a thread for each connection from a worker pool of threads whose population is set by the sp_configure stored procedure of SQL Server. When a client workstation makes a request from SQL Server, one of the threads from the worker pool is assigned to the workstation's connection. When the request is fulfilled and the connection is terminated, the connection thread is returned to the pool. If a client workstation requests a thread and none is available in the pool, the first thread released by another connection is assigned to the request. When a fault occurs during the execution of a thread, only the connection associated with the thread is affected. Thus, a page fault on a single connection doesn't bring down the server.
The advantages of a multithreaded operation are particularly evident when you add more than one microprocessor unit to the server. SQL Server supports thread-level multiprocessing, usually called symmetric multiprocessing (SMP), and is referred to as a scalable operating system. These terms mean that multiple processors that share the same memory region can execute any thread. This process is also called symmetric load balancing. If a thread is executing on a processor that is running near capacity, the thread can be transferred so that it executes on another processor. Windows NT Server, and thus SQL Server, supports up to eight 80x86 processors. Multithreaded SMP operation and the capability to run on a variety of microprocessors, not just members of the Intel 80x86 series, are the two most distinguishing features of Windows NT.
NOTE
Finding an eight-CPU computer to run Windows NT Server might be an interesting task. When this book was written, four CPUs was the practical limit.
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:
NOTE
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.
Figure 20.1. The SQL Server application group on an SQL Server.
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.
The Microsoft Enterprise Manager lets you create and manage the following types of objects:
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:
NOTE
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
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.
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.
To add a sample table called "test" to the NorthWind database and define some sample fields, follow these steps:
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.
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.
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.
To use the SQL Server ODBC database driver to add the NorthWind database as an ODBC, follow these steps:
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.
[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=
NOTE
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, "Designing a Decision-Support Application." All the entries in the [NorthWind] section result from the entries in the ODBC SQL Setup dialog box described in the preceding section.
To test your SQL Server ODBC datasource, follow these steps:
NOTE
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.
For more information on type conversions, refer to the following topics found in the ODBC 2.0 Programmer's Reference:
Converting Data from SQL to C Data Types
Core C Data Types
Extended C Data Types
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.
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.
The syntax of the SQLConfigDataSource() instruction is as follows:
// Add the datasource to ODBC for the user:
sprintf(szBuffer,
"DESCRIPTION=STARmanager saved DB.|"
"DSN=StarDatabase files|"
"FileType=RedISAM|"
"SingleUser=True|"
"UseSystemDB=FALSE|"
"DataDirectory=%s|"
"DATABASE=%s||", szOutFileName, szOutFileName);
nLength = (int)strlen(szBuffer);
for (i = 0; i < nLength; i++)
{
if (szBuffer[i] == '|')
{
szBuffer[i] = '\0';
}
}
SQLConfigDataSource(NULL, ODBC_ADD_DSN, "Access Data (*.mdb)",
szBuffer);
SQLConfigDataSource()'s prototype is
SQLConfigDataSource(hwndParent, fRequest, lpszDriver, lpszAttributes);
The hWndParent argument is the parent window's handle. The fRequest argument is one of the following manifest constants: ODBC_ADD_DSN, ODBC_CONFIG_DSN, or ODBC_REMOVE_DSN. The lpszDriver 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 lpszAttributes argument points to a string containing a list of keyword-value pairs. A typical lpszAttributes string might contain this:
DSN=Personel Data\0OUID=Jones\0OPWD=Password\0DATABASE=Personel\0
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.
When an ODBC_CONFIG_DSN request is made, any of the values in the lpszAttributes argument that don't match current entries in the ODBC.INI topic for the datasource are updated. Attribute values aren't case-sensitive.
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.
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.
To export an Access table to SQL Server, follow these steps:
You can use the MS Query application to display the structure of the table you exported in the preceding section. MS Query, however, creates a Snapshot object of the entire contents of the table when you open your query. This can take an appreciable amount of time for a table with many records. Using Enterprise Manager to display the table structure is a faster approach.
Follow these steps to display the structure of the SQL Server version of one of NorthWind.MDB's tables you exported to the temp database and run a simple query to verify that its records are present:
NOTE
SQL Server, as well as other client-server RDBMSs, doesn't allow spaces in table or field names. The SQL Server ODBC driver substitutes underscores (_) for spaces and any other illegal punctuation in the names of Access tables or fields. Although field names in SQL Server databases traditionally use lowercase letters, capitalization of Access field names is preserved in the export operation.
NOTE
Table and field names are case-sensitive in SQL Server. Therefore, you need to type them exactly as they appear in the Column Name column of the Manage Tables window.
Figure 20.22. The result of the SELECT query shown in Figure 20.21.
TIP
Use the SQL Server's BCP utilities to speed the import of data from large Access tables to SQL Server databases. Create copies of the Access tables using the clipboard and then delete all but a few records in the cloned tables. Export the table structure and the few records to the SQL Server database to create the new table structure and then delete the records in the SQL Server tables. Export the data from the Access tables to a comma-delimited text file. Then use SQL Object Manager's BCP utility to run a bulk copy operation to each SQL Server table. Michael J. Smith, a software engineer and DBA, reported in the article "SQL Server for Windows NT: A Case Study" in the May/June 1993 issue of SQL Forum that he was able to achieve a bulk copy rate of 530 records per second over a busy network running the BCP utility from a workstation. (Running BCP from the server reduces the bulk copy rate by about 50 percent.)
When you export an Access table to a client-server database, Access doesn't create indexes on the client-server table. You need to use the SQL CREATE INDEX statement or the Enterprise Manager to manually add indexes to the client-server table. You can add one clustered or as many nonclustered indexes as you want to a table with Enterprise Manager. Clustered indexes, which physically place the pages of the table in the index order, greatly improve the performance of SQL Server databases. However, you gain a performance improvement with a clustered index only if you specify that the index must be unique. (Clustered indexes are not unique by default.)
To use Enterprise Manager to create a clustered primary key index on an imported table or any other table in an SQL Server database, follow these steps, which assume that the table you chose for the preceding example is open:
You can verify that the index is present and is recognized by the SQL Server ODBC driver by attaching the table to Access and then opening the table in design mode. Access assumes that a unique clustered index is the primary key index of an SQL Server table and indicates the key field(s) with the key symbol in the field selector button(s).
ODBCTEST is an application included with the Microsoft ODBC SDK that lets you inspect and use the low-level functions of the ODBC API. ODBCTEST is used primarily by developers of ODBC database drivers to debug and test the drivers' performance. If you intend to use different types of ODBC datasources or evaluate different drivers for a single data type, ODBCTEST lets you inspect and test each of the driver's functions.
NOTE
ODBCTEST replaces the GATOR.EXE program that was part of the ODBC version 1.x SDK. There are only minor differences between ODBCTEST and GATOR.
Figure 20.27 shows the ODBC Test window of ODBCTEST after you connect to the NorthWind database and then choose Connect | GetInfo All. The hdbc and hstmt combo boxes let you select the handle to the connected datasource(s) and statement(s) you execute either in the upper pane of the ODBC Test window or by choosing options from a menu.
Figure 20.27. The first few items returned by ODBCTEST's GetInfo All menu option.
The GetInfo All menu choice returns more than you might want to know about the ODBC driver used to create the connection to the ODBC datasource. One of the most important features of the display provided by the GetInfo All command is the capability to determine the functions and data type conversions that the driver supports. You can use only those functions in your SQL statements that the ODBC driver translates into the appropriate syntax for the RDBMS in use. The documentation for supported functions that is supplied with ODBC drivers often doesn't include this information, and other times the information provided is incomplete. Figure 20.28 shows a few of the functions supported by version 1.02.3109 of the SQL Server ODBC driver.
TIP
ODBCTEST's File menu has a Print option, but it's always disabled. You can copy the information in the lower pane of the ODBCTEST's info window to the clipboard. If you want, you can then paste the data into Notepad in order to format and print the information so that it's easier to read.
This chapter emphasized that creating Visual C++ front-end client-server applications doesn't differ substantially from designing similar applications that connect to conventional desktop databases with the Access database engine. The principal issues for the Visual C++ database developer are setting up the server database and the ODBC datasource(s) for the client-server back end. Thus, this chapter was devoted primarily to discussing the characteristics of a new, fast, and powerful client-server RDBMS, Microsoft SQL Server for Windows NT, and the Microsoft ODBC API.
The next chapter describes how to create mail-enabled Visual C++ applications that use the MAPI interface.
