Christopher Moffatt
Microsoft Technical Resources Group
The Microsoft® Windows NT™ operating system is an ideal platform for building robust, feature-rich applications for Microsoft SQL Server. The SQL Server Programmer’s Toolkit for Windows NT contains a 32-bit (Win32™ API) version of DB-Library™, making it possible to develop DB-Library client applications for Microsoft SQL Server on the Windows NT operating system.
The purpose of this technical article is to introduce DB-Library developers to the Microsoft Windows NT platform, address issues involved in porting existing DB-Library applications for the Windows™ version 3.x and OS/2® operating systems to the Windows NT operating system, and outline some of the ways in which DB-Library applications can take advantage of the Win32 application programming interface (API).
This technical article assumes that you are familiar with the DB-Library API, C programming, and the following documentation:
The following sections describe uses relevant to building DB-Library™ applications using the Microsoft® Win32™ Application Programming Interface (API).
Internally, the Win32 version of the DB-Library dynamic-link library (DLL) is implemented similarly to the version for OS/2®. In Win32, as in OS/2, a separate operating system thread is spawned for each connection that DB-Library makes with SQL Server. Each instance of the DB-Library DLL that is loaded by a calling process gets a private data area, while sharing code.
The Win32 DB-Library architecture differs from the implementation in Microsoft Windows™ version 3.x. In Windows 3.x, the DB-Library DLL has a single data segment that is shared among all calling processes. W3DBLIB.DLL maintains DB-Library connections as a linked list of connections in a single data segment. This architecture is required because in Windows 3.x dynamic-link libraries have a single data segment that is shared among all calling processes. This necessitates the initialization and clean-up of the DB-Library DLL data structures through calls to the dbinit and dbwinexit functions.
The DB-Library functions for Win32 are located in NTWDBLIB.DLL, and the named pipe driver is located in DBNMPNTW.DLL. Set the PATH environment variable to include the directory where the dynamic-link libraries reside.
Another file, NTWDBLIB.LIB, contains import definitions that your applications for the Win32 API use. Set the LIB environment variable to include the directory where NTWDBLIB.LIB resides.
The SQL Server Programmer’s Toolkit for Windows NT™ contains the following include files:
| Include file | Contains |
| SQLFRONT.H | Error codes and severity levels, miscellaneous definitions, and type definitions. |
| SQLDB.H | Function prototypes for all DB-Library functions. |
You can append the path to the INCLUDE environment variable to include the directory where the include files reside. Or you can use the /I compile line switch to point to the include file directory.
Because your applications for the Win32 API must always include the SQLDB.H file, you don’t need to define the DB-Library functions you use. These functions and their proper declarations are already defined in the include file. You must define an application’s operating system before including the DB-Library include files. Include the following statements at the beginning of all DB-Library applications that are designed for the Win32 API:
#define DBNTWIN32
#include <sqlfront.h>
#include <sqldb.h>
You can also define the operating system by using the /D compile line switch (for example, /DDBNTWIN32 instead of a #define declaration).
The following example shows a simple build file that can be used with the NMAKE utility to compile and link debuggable DB-Library applications for the Win32 API:
NTLIB=\nt\sdk\lib\i386
DBLIB=\sql\dblib\lib
guilibs= $(NTLIB)\gdi32.lib $(NTLIB)\user32.lib $(NTLIB)\
userrtl.lib $(NTLIB)\kernel32.lib $(NTLIB)\ntdll.lib \
$(NTLIB)\libcmt.lib
all: sqltestw.exe
# Update the object file if necessary.
sqltestw.obj: sqltestw.c sqltestw.h sqltestw.rc makefile
cl386 -c -G3d -Di386=1 -DWIN32 sqltest.c
# Update the resource file if necessary.
sqltestw.res: sqltestw.rc sqltestw.h dilaogs.h dialogs.dlg makefile
rc -r -fo sqltestw.tmp sqltest.rc
cvtres -i386 sqltestw.tmp -O sqltestw.res
del sqltestw.tmp
# Update the exe file if necessary.
sqltestw.exe: sql.obj sqltestw.obj sqltestw.res makefile
link -machine:i386 -subsystem:windows -out:sqltestw.exe\
-entry:WinMainCRTStartup\
sqltestw.res sqltestw.obj $(guilibs)\
($DBLIB)\ntwdblib.lib
All DB-Library functions are completely portable between platforms. You should not have to modify any of your DB-Library calls. You simply need to define the application’s operating system at compile time (for example, #define DBNTWIN32 for Windows NT).
Because the Win32 API maintains compatibility with the Windows 3.x operating system, the Win32 DB-Library DLL supports all Windows 3.x-based DB-Library functions, even if they are not required. DB-Library functions present in Windows 3.x but not needed in Windows NT (for example, DBLOCKLIB and DBUNLOCKLIB) are accepted, but return without performing any actions.
Two DB-Library functions, dbprhead and dbprrow, not supported in Windows 3.x because they send output to STDIO, are supported in Win32 and can be used when developing applications for the CONSOLE subsystem. The dbprhead and dbprrow functions provide a convenient way to display the results of a query to the default output device.
If you have existing Windows-based DB-Library applications that you want to run with Windows NT, you can take three approaches:
This section explains how to port a DB-Library application for Windows 3.x to the Windows NT operating system while retaining compatibility with applications for Windows 3.x.
In general, you can change your source code to easily build 16-bit or 32-bit versions. For an in-depth discussion of writing applications for the Win32 API that retain compatibility with Windows 3.x, see the Microsoft Win32 API Programmer’s Reference, Volume 1.
The following guidelines apply to the development of applications for the Win32 API:
All DB-Library functions are completely portable between operating systems. You simply need to define the target operating system at compile time.
Many DB-Library functions return values of type int. Note that the int datatype is a 16-bit value in Windows 3.x and a 32-bit value in the Win32 API. Therefore, verify that your application uses the correct type of variable to receive return values from DB-Library functions that return an int datatype. For example, a variable defined as the datatype short receives a correct int value from DB-Library in Windows 3.x but receives an incorrect value in the Win32 API.
Apart from verifying that variables receiving return values from DB-Library are of the correct type, no changes are needed to DB-Library functions.
If you port an application for the Windows 3.x operating system to the Windows NT operating system and do not need to maintain compatibility with Windows 3.x, you can change your DB-Library code to remove redundant or unnecessary Windows 3.x functions.
The following Windows 3.x-specific DB-Library functions are redundant in the Win32 version of DB-Library:
dbinit
In the Win32 API, there is no need to call dbinit before issuing other DB-Library functions. As in the MS-DOS® and OS/2® operating systems, a Win32 call to dbinit returns a version string. In Windows 3.x, it is necessary to call dbinit to initialize DB-Library data structures.
Note It is generally recommended that you retrieve the DB-Library version string, by calling dbinit, and make it viewable by the user (for example, in an “About” dialog box).
dbwinexit
This function is unnecessary in Win32.
DBLOCKLIB/DBUNLOCKLIB
In Win32, each process has a unique 32-bit linear virtual address space that allows it to address up to 4 gigabytes of memory (the 2 gigabytes in low memory are available to the user). In effect, all data is in one large segment, and you are freed from the concerns of segments, far pointers, and so on. Therefore, you don’t have to call the DBLOCKLIB and DBUNLOCKLIB macros before accessing pointers to data in the DB-Library DLL or pointers that pass data to the DB-Library DLL.
The following changes apply to the registration of error and message handlers for Win32:
For Windows 3.x:
// Define variables
static FARPROC lpdbMsgHandler;
static FARPROC lpdbErrHandler;
// Get Procedure Instances
lpdbMsgHandler = MakeProcInstance((FARPROC)dbMsgHandler, hInst);
lpdbErrHandler = MakeProcInstance((FARPROC)dbErrHandler, hInst);
// Install the instances into dblib
dbmsghandle(lpdbMessageHandler);
dberrhandle(lpdbErrorHandler);
For Win32:
// Install the instances into dblib
dbmsghandle(dbMessageHandler);
dberrhandle(dbErrorHandler);
To support asynchronous processing in the cooperative multitasking environment in Windows 3.x, each application must behave well and yield to the CPU at regular intervals. Therefore, when you process SQL Server queries in the Windows operating system, you need to use Windows-based timers or the PeekMessage function in combination with the DB-Library function calls for asynchronous processing (dbsqlsend, dbdataready, and dbsqlok). For more information about implementing asynchronous query processing in Windows 3.x, see the Microsoft SQL Server Programmer’s Reference for C (page 43).
The support for preemptive multitasking in the Win32 API simplifies the implementation of asynchronous query processing. The preemptive multitasking nature of the Windows NT operating system ensures that other processes always obtain CPU cycles regardless of any processing that another application is doing. Within a single process, asynchronous processing can be implemented using Win32 threads or by using the DB-Library functions that support asynchronous processing in conjunction with the PostMessage Windows function. For more information about asynchronous processing, see “Taking Advantage of Win32 API Features in DB-Library Applications,” later in this article.
This section uses the sample program SQLTEST3, provided with the DB-Library Programmer’s Toolkit, as an example of how to port a DB-Library application from Windows 3.x to Windows NT. Although it uses a simple application, this example illustrates how easily you can port applications for the Windows 3.x operating system to the Windows NT operating system.
The following procedure shows how to modify the SQLTEST3 sample application to port it to the Win32 API while maintaining compatibility with Windows 3.x:
Change (Windows 3.x):
#define DBMSWIN
To (Win32):
#define DBNTWIN32
Change (Windows 3.x):
long FAR PASCAL SqlTestWndProc(HWND, unsigned, WORD, LONG);
BOOL FAR PASCAL AboutSQL(HWND, unsigned, WORD, LONG);
BOOL FAR PASCAL SelectSQL(HWND, unsigned, WORD, LONG);
BOOL FAR PASCAL ConnectSQL(HWND, unsigned, WORD, LONG);
int dbwinErrorHandler(DBPROCESS *, int, int, int, LPSTR, LPSTR);
int dbwinMessageHandler(DBPROCESS *,DBINT,DBSMALLINT,DBSMALLINT,LPSTR);
To (Win32):
LONG APIENTRY SqlTestWndProc(HWND, UINT, DWORD, LONG);
LONG APIENTRY AboutSQL(HWND, UINT, DWORD, LONG);
LONG APIENTRY SelectSQL(HWND, UINT, DWORD, LONG);
LONG APIENTRY ConnectSQL(HWND, UINT, DWORD, LONG);
int API dbwinErrorHandler(DBPROCESS *, int, int, int, LPSTR, LPSTR);
int API dbwinMessageHandler(DBPROCESS* DBINTDBSMALLINT, DBSMALLINT, LPSTR);
SqlTestWndProc
AboutSQL
SelectSQL
ConnectSQL
dbwinErrorHandler
dbwinMessageHandler
The following procedure shows how to modify the SQLTEST3 sample application to port it to the Win32 API without maintaining compatibility with Windows 3.x.
DBLOCKLIB or DBUNLOCKLIB (in three places)
dbwinexit
Change (Windows 3.x):
static FARPROC lpdbwinMessageHandler;
static FARPROC lpdbwinErrorHandler;
...
lpdbwinMessageHandler =
MakeProcInstance((FARPROC)dbwinMessageHandler, hInst);
lpdbwinErrorHandler =
MakeProcInstance((FARPROC)dbwinErrorHandler, hInst);
dbmsghandle(lpdbwinMessageHandler);
dberrhandle(lpdbwinErrorHandler);
...
FreeProcInstance(lpdbwinMessageHandler)
FreeProcInstance(lpdbwinErrorHandler);
To (Win32):
dbmsghandle(dbwinMessageHandler);
dberrhandle(dbwinErrorHandler);
Modify all DialogBox function calls to pass the address of the function instead of calling MakeProcInstance and passing the pointer returned from this function.
All DB-Library functions are completely portable across the OS/2 and Win32 APIs, and you should not need to modify to any of your DB-Library calls.
If you have existing OS/2-based DB-Library applications that you want to run with the Windows NT operating system, there are two basic approaches you can take:
As mentioned earlier, the topic of converting applications from OS/2 to the Win32 API is covered extensively in Converting OS/2 Applications to Windows. This section highlights two major areas of functionality that are found in a large number of OS/2-based DB-Library applications, namely thread management and synchronization mechanisms. (This information is taken directly from the Converting OS/2 Applications to Windows.)
The Win32 API provides equivalent functionality to OS/2 process and thread calls. You can consider their functionality to be conceptually equivalent, except in the areas outlined below:
There are two types of synchronization provided by both OS/2 and the Win32 API set: mutual exclusion and signaling.
Mutual exclusion
Mutual exclusion involves denying other threads of execution access to a critical section of code. Both OS/2 and Win32 have mechanisms designed to facilitate mutual exclusion within a process and mutual exclusion between processes.
Signaling
OS/2 semaphores can be used for signaling between processes. The OS/2 functions used for this purpose are DosSemSet, DosSemWait, DosSemSetWait, and DosMuxSemWait. The Win32 API provides the Event object, which implements a superset of this functionality. The Win32 functions used to manage the Event object include CreateEvent, OpenEvent, SetEvent, PulseEvent, and ResetEvent.
The following sections describe how to take advantage of the unique features of the Win32 API.
Preemptive multitasking in the Win32 API makes implementing asynchronous query processing easy. There are several ways to implement asynchronous query processing, depending on whether you want your application to exhibit asynchronous behavior between processes or within a single process.
case WM_SENDQUERY:
dbsqlsend (dbproc);
PostMessage(hWnd,WM_CHECKQUERY,0,0L);
break;
case WM_CHECKQUERY:
if ( dbdataready(dbproc) )
{
dbsqlok (dbproc);
PostMessage(hWnd,WM_GETRESULTS,0,0L);
}
else
{
PostMessage(hWnd,WM_CHECKQUERY,0,0L);
}
break;
The 32-bit linear virtual memory address space available to processes in the Win32 API makes memory management simpler and cleaner. This applies to DB-Library programming as well as to any other programming for the Win32 API.
Although the memory management functions (GlobalAlloc, LocalAlloc, and so on) in Windows 3.x are supported by the Win32 API, consider using the C run-time functions malloc and free to perform dynamic memory management when you develop applications to run only with Win32. The malloc and free functions are not safe to use with Windows 3.x and OS/2 because of the segmented architecture of those systems. However, with Win32 these functions are both safe and elegant. In addition to the standard memory management functions (Global and Local, C run-time support), Win32 also provides heap and virtual memory management functions for applications that require specialized memory management.
Two specific areas of DB-Library programming can take advantage of the ability to address greater than 64K of data:
The multithreaded process structure of Win32 opens up powerful and elegant solutions to high-end applications. Each thread within a process has its own stack and machine state. On multiprocessor systems with the Windows NT platform, multiple threads can execute at the same time but on different processors. Applications that need to perform intensive processing, I/O, and so on, can do this in the background using threads and still offer a responsive user interface.
The DB-Library API is basically non-reentrant, because all DB-Library functions that access a named pipe connection are not reentrant. You must serialize the DB-Library calls that access the named pipe connection in multithreaded applications.
Time-outs are also managed globally in DB-Library.
The Win32 API supports structured exception handling to aid the development of robust software and is made available to developers through compiler support. Consider using this functionality in your application. For more information about structured exception handling, see the Microsoft Win32 API Programmer’s Reference, Volume 1 (Chapter 11).
The Windows NT platform supports symmetric multiprocessing (SMP), thereby allowing applications with multiple threads of execution to run across multiple processors. If your DB-Library application uses Win32 threads, it can advantage of Windows NT SMP support without any additional work being done.
SQLSTRES is a sample Windows NT-based DB-Library application. It can be used to simulate a large number of SQL Server clients to help determine the effect of “scaling up” an application when more users are added.
When a SQL Server client application is developed, questions often arise as to how response time and throughput will be affected when the application is deployed and a large number of users use the application simultaneously. Standard benchmarks are useful for getting a idea of performance and scalability, but these factors vary tremendously and are highly dependent on the types of queries and updates done by a specific application. In the development phase of an application, it is often difficult to simulate realistic operating environments with large numbers of workstations. The SQLSTRES application allows you to simulate a large number of users executing operations of your particular application from a single workstation:
The sample code described here can be found in the Microsoft Online Software Library or in the Microsoft Forum Software Library on CompuServe® in a file called SQLSTRES.ZIP. It can be located by searching on the keyword SQLSTRES, the Q number of this article in the Microsoft KnowledgeBase (Q87953), or S13580. SQLSTRES.ZIP was archived using the PKware file-compression utility.
SQLSTRES.ZIP contains the following files:
| Filename | Description |
| SQLSTRES.C | Main source code file |
| SQLSTRES.H | Source code header file |
| SQLSTRES.RC | Windows-based resource script file |
| SQL.C | Source code file for DB-Library–specific functions |
| SQL.H | Header file for DB-Library–specific functions |
| DIALOGS.DLG | Dialog box resource script file |
| DIALOGS.H | Dialog box header file |
| DIALOGS.RES | Windows NT–based Dialog Editor resource |
| MAKEFILE | NMAKE File |
| TESTX.SQL | Sample query file |
Microsoft SQL Server Programmer’s Reference for C (Part number 27445)
Microsoft Win32 API Programmer’s Reference, Volumes 1 & 2 (Microsoft Press)
Converting OS/2 Applications to Windows (Part number 098-35176)
"Using Microsoft SQL Server on a Banyan VINES Network" (Part number 098-30193)
"Query Optimization Techniques: Contrasting Various Optimizer Implementations with Microsoft SQL Server" (Part number 098-30199)
"Microsoft Open Data Services: Application Sourcebook" (Part number 098-32078)
"Using Microsoft SQL Server on a Novell NetWare Network" (Part number 098-32655)
"Developing 32-Bit Open Data Services Applications for the Microsoft Windows NT Operating System" (Part number 098-36256)
To receive more information, contact Microsoft Inside Sales, Systems Software, at 1-800-227-4679.