Stewart P. MacLeod
Program Manager
SQL Server Development
April 20, 1996
Now that Microsoft® Windows® 95 and Windows NT® version 4.0 are available, everyone wants to take advantage of all the cool new features like preemptive multitasking, multiple threads of execution, and structured exception handling in their applications. Windows 95 and Windows NT are both 32-bit operating systems that implement the Win32® API and free programmers from the restrictions of Intel's segment-offset architecture. Under Windows NT, Win32 applications automatically support symmetric multiprocessing (SMP) and can be easily recompiled for high-speed RISC processors like DEC Alpha, MIPS R4400, and PowerPC.
Figure 1. DB-Library and ODBC
Microsoft SQL Server is designed not only to manage information, but also to make that information easy to access and easy to use. Hundreds of leading applications support SQL Server today via DB-Library and ODBC (Open Database Connectivity) application program interfaces (API). Both DB-Library and ODBC provide everything you need to build powerful client-server applications. ODBC allows you to create portable DBMS applications. ODBC is an industry-standard, database-independent API for creating applications that support multiple datasources on Windows 95, Windows NT, and Windows 3.x platforms. ODBC drivers are also available for OS/2® and leading UNIX environments from Visigenic Software ([800] 632-2464). DB-Library, on the other hand, is SQL Server's native API. DB-Library provides the same basic functionality as ODBC, but it also includes some special functions for high-speed data loading (bulk copy), two-phase commit, image, and text operations. You can build great 32-bit applications with either DB-Library or ODBC. Figure 1 illustrates their relationship to each other and SQL Server. Both DB-Library and ODBC communicate with SQL Server via the Tabular Data Stream (TDS). TDS is the self-defining protocol used by Microsoft SQL Server to transfer requests and responses between the client and the server. The Net-Library provides a transport independent method of sending TDS across a physical network connection. As you can see from Figure 1, ODBC is not layered on top of DB-Library and therefore provides the same performance as native DB-Library applications.
Figure 2. DB-Library Overview
DB-Library consists of over 180 functions that allow your application to communicate with Microsoft SQL Server. Figure 2 shows the basic structure of every DB-Library program. Most DB-Library programs use only between 10 and 15 functions. Communication between the client applications and SQL Server are managed via a DBPROCESS structure. dbopen() allocates and initializes the DBPROCESS structure. The user name, password, application name, and other "per connection" parameters are stored in the login record. Login records are created by calling dblogin(). Since we now have a connection to SQL Server, we can start sending SQL statements. dbcmd() copies the SQL statement into the command buffer. The statement is not actually sent across the network to the server until dbsqlexec() is called. At this point, the SQL statement is compiled, optimized, and executed. SQL Server can return multiple result sets if multiple SQL statements were sent in a single batch or stored procedure. To retrieve the first result set, call dbresults(). Each row in the current result set is fetched by calling dbnextrow() until dbnextrow() returns NO_MORE_ROWS. This process is repeated by calling dbresults() until it returns NO_MORE_RESULTS. NO_MORE_RESULTS means that that we have processed every SQL statement in the command batch or stored procedure. When the application is finished, we can close our connection by calling dbclose(). This will deallocate the DBPROCESS structure on the server and release the resources it held on the server. The client application can now terminate by calling exit(). SQL Server calls a function in your application whenever it needs to send a message to the client or notify the client of an error. These functions are installed with dberrhandle() and dbmsghandle() and are not shown in the diagram.
Here is a simple DB-Library character-based (Win32 console) application program that connects to an SQL Server and requests a complete list of authors sorted by the authors last name from the infamous PUBS database. To run this demo, you need to have the PUBS sample database installed on your server.
/*
**********************************************************************
This simple program demonstrates how to create a character
based "console" application for Microsoft SQL Server 6.0
on Windows NT Server or Windows 95.
**********************************************************************
*/
#define DBNTWIN32 // Tells SQL Server It's Windows NT Or Windows 95
#include <windows.h> // Main Windows Header File
#include <sqlfront.h> // Every DBLIB program requires
#include <sqldb.h> // <sqlfront.h> and <sqldb.h>
#include <stdio.h> // C Standard Library
#define SQLSTATEMENT "select au_lname, au_fname from authors order by au_lname"
void main( int argc, char ** argv)
{
char szServerName[MAXNAME+1]; // SQL Server Name
char szDatabaseName[MAXNAME+1]; // DB Name
char szUserName[MAXNAME+1]; // User Name
char szUserPassword[MAXNAME+1]; // User Password
DBPROCESS *lpDbProc; // Connection To SQL Server
LOGINREC *login; // Login Record
INT iRetCode; // Return Code
STATUS iRowCode; // Row Fetch Status Code
unsigned char szFirstName[20+1]; // Author First Name
unsigned char szLastName[40+1]; // Author Last Name
/*
**********************************************************************
Set the Defaults
**********************************************************************
*/
if( argc < 3 || argc > 4){
printf("\nSyntax: %s SqlServerName UserName <Password>\n", argv[0]);
exit(1);
}
strcpy( szServerName, argv[1] ); // Get SQL Server Name
strcpy( szUserName, argv[2] ); // User Name
strcpy( szDatabaseName, "pubs" ); // User PUBS database
if(argc == 4)
strcpy( szUserPassword, argv[3] ); // and Password
else
*szUserPassword = '\0';
/*
**********************************************************************
Initialize DB-Library And Install Our Error And Message Handlers
**********************************************************************
*/
printf("\nVersion: %s\n", dbinit()); // This returns a pointer to
the version string
dberrhandle( ErrorHandler ); // Global Error Handler
dbmsghandle( MessageHandler ); // Global Message Handler
/*
**********************************************************************
Specify The UserName, Password, and Login Timeout
**********************************************************************
*/
login = dblogin(); // Allocate A Login Record
dbsetlogintime( 30 ); // Specify Login Timeout (30 sec)
DBSETLUSER( login, szUserName ); // Specify The User Name
DBSETLPWD( login, szUserPassword); // And Their Password
/*
**********************************************************************
Connect To The Specified Server And Open A DBPROCESS
**********************************************************************
*/
lpDbProc = dbopen( login, szServerName ); // Connect To SQL Server
if( !lpDbProc ) { // Unable To Connect?
printf("\nUnable To Connect To %s\n", szServerName );
exit(1); // Exit With Error
}
/*
**********************************************************************
Use The PUBS Database
**********************************************************************
*/
if( dbuse( lpDbProc, szDatabaseName ) == FAIL ){ // Unable To Use Database?
printf("Unable To Use %s Database On %s", szDatabaseName,szServerName );
exit(1); // Exit With Error
}
/*
**********************************************************************
Compile And Execute The SQL Statement
**********************************************************************
*/
iRetCode = dbcmd(lpDbProc, SQLSTATEMENT ); // Put SQL Statement In
The Buffer
if( iRetCode == FAIL ) {
printf("\nError: Unable To Add SQL Statement To Command Buffer\n");
exit(1); // Exit With Error
}
iRetCode = dbsqlexec(lpDbProc); // Send To Server and Execute
if( iRetCode == FAIL ) {
printf("\nError: Compiling And Executing SQL Statement \n");
exit(1); // Exit With Error
}
/*
**********************************************************************
Fetch Any And All Results
**********************************************************************
*/
while ((iRetCode = dbresults(lpDbProc)) != NO_MORE_RESULTS)
{
if (iRetCode == FAIL ) break;
/*
******************************************************************
Bind The Author First Name And Last Name To Our Variables
******************************************************************
*/
dbbind( lpDbProc, 1, NTBSTRINGBIND, 0, szLastName );
dbbind( lpDbProc, 2, NTBSTRINGBIND, 0, szFirstName );
printf("\n%s\n", SQLSTATEMENT );
printf("\n%-40s %-20s", "Last Name", "First Name" );
printf("\n%-40s %-20s", "---------", "----------" );
/*
******************************************************************
Fetch Each Row And Print
******************************************************************
*/
while ( (iRowCode = dbnextrow(lpDbProc)) != NO_MORE_ROWS)
{
if( iRowCode == FAIL ) break;
printf("\n%-40s %-20s", szLastName, szFirstName);
}
if( iRetCode == FAIL || iRowCode == FAIL ) {
printf("\nError: Fetching Data\n");
exit(1); // Exit With Error
}
}
/*
*****************************************************************
Close The Connection To SQL Server And Exit
*****************************************************************
*/
dbclose( lpDbProc ); // Close The Connection
exit(0); // Exit The Program
}
/*
*********************************************************************
ErrorHandler() - Notifies The User If Any SQL Server Error Messages
Are Received.
*********************************************************************
*/
int SQLAPI ErrorHandler(DBPROCESS *lpDbProc, int iSeverity,int iDbError,
int iOsError, const char
*lpszDbErrorMsg,
const char *lpszOsErrorMsg)
{
printf( "Error:%d\nSeverity: %d\n\n%s\n%s\n",
iDbError, iSeverity, lpszDbErrorMsg, lpszOsErrorMsg );
if ((lpDbProc == NULL) || (DBDEAD(lpDbProc))) // Test for a broken
connection
return(INT_EXIT); // Terminate if connection
broken
return(INT_CANCEL); // Return FAIL to DBLIB
}
/*
**********************************************************************
MessageHandler() - Notifies The User If Any SQL Server Messages
Are Received.
**********************************************************************
*/
int SQLAPI MessageHandler(DBPROCESS *lpDbProc, DBINT iMsgNumber,
int iMsgState, int iSeverity,
const char *lpszMessageText,
const char *szServerName,
const char *szProcName,
unsigned short iLineNumber){
/*
**********************************************************************
Filter Out 5701 Database Context Changed ("Use DB")
5703 Database Language Changed
**********************************************************************
*/
if( iMsgNumber == 5701 || iMsgNumber == 5703 ) {
return( 0 );
}
printf( "Message: %ld\nState: %d\nSeverity: %d\n\n%s\n",
iMsgNumber, iMsgState, iSeverity, lpszMessageText);
if ((lpDbProc == NULL) || (DBDEAD(lpDbProc))) { // Check For
Broken Connection
printf("\nDead or NULL Database Connection");
}
return(0);
}
Dynamic Link Libraries work very differently under Win32. In Windows 3.x, loading a DLL installs it as part of the operating system. Each 16-bit DLL has its own data segment where all static and global variables are stored. This single data segment is shared across all processes using the DLL. Any memory allocated in the DLL is allocated from the DLL's data segment and is available to all calling processes. This design makes it very easy to share data between multiple processes. Our 16-bit DB-Library, (W3DBLIB.DLL for Windows 3.x) maintained the DB-Library connections as a linked list within this single, shared data segment. This required programmers to call dbinit() before any other DB-Library function calls so that we could initialize our data structures and to call dbwinexit() so that we could clean it up when the application was done.
The real mode of Windows 3.0 imposed additional restrictions. Programmers were required to surround each DB-Library call with DBLOCKLIB() and DBUNLOCKLIB() to prevent Windows from moving the DB-Library data segment and invalidating a far pointer. This is not necessary in standard or enhanced mode. When Windows runs in protected mode, the value stored in the segment register is not a physical memory address. It is an offset into a descriptor table and contains a 24-bit base address. The offset address is then added to this base address to create the 24-bit physical address. This indirect method of segment addressing allows the segment to be moved around by the Windows without fear of invalidating any far pointers. Windows can simply update the descriptor table with the new location of the segment. You would be surprised how many applications still make these calls. If you see DBLOCKLIB() and DBUNLOCKLIB() still lingering in your code, just delete them.
In Win32, DLLs don't become part of the operating system—they are mapped into the address space of the calling process when the DLL is loaded by the process. This means that each process has a separate copy of the DLL's global and static data. Each Win32 process enjoys a roomy 4-GB virtual address space. 2 GB are reserved for the system and 2 GB are available for your application. The 32-bit "flat" memory model frees you from the annoying limitations of Intel's 64-KB physical segment size that you always manage to bang your head into. The flat memory model also eliminates the need to call MakeProcInstance() and FreeProcInstance() to create an address "thunk" for callback functions. Edit controls now can hold almost 2 GB of text, and list boxes can have more entries than your users are willing to scroll through. Win32 supports dbprhead() and dbprrow(). These functions allowed DOS programmers to display the output of any query to STDOUT, but they were not available on Windows 3.x because 16-bit Windows does not support the concept of STDOUT. Since Win32 supports both graphical and character (console) based applications, they are now available and you can take advantage of them in your applications.
Windows 3.x could run multiple applications at the same time as long as all of the programs were "well behaved." Windows 3.x is a cooperative multitasking environment. Each application must tell the system that it has finished processing, before any other application can start, by calling GetMessage(). If an application does not "yield" control, then the user will not be able to switch to another program.
How do you prevent an application from hogging the system? The two basic approaches are to use PeekMessage() or a Timer. PeekMessage() allows the application to continue processing until another program needs to process a message. After performing a small part of the job, your application must be a "nice guy" and yield to Windows via WaitMessage() in order to give other programs a chance to use the system. When the system is available again, you can process another small part of your job. The other approach is to use a timer. The application executes a chunk of the job whenever you receive a message from the timer. Obviously, both of these approaches add unnecessary complexity to the application. It is not always easy to decompose real-world applications into a series of discrete tasks. And even when you could break your application up this way, you must still keep track of what work had been completed and deal with any errors.
Cooperative multitasking can sometimes make it more difficult to develop client-server applications. If an application makes a synchronous call to some function that performs a lengthy process, then Windows 3.x appears to "freeze" until that function finally returns and can yield control back to Windows 3.x. Since synchronous functions like dbsqlexec() do not return until the specified query has been compiled and executed, complex queries against very large databases might take some time to complete. Microsoft SQL Server provides functions that allow any application to communicate asynchronously. dbsqlsend() sends a command batch to SQL Server but does not wait for the database processing to complete before it returns control to the caller. It returns immediately. You can call dbdataready() periodically to see when your command has finished and then call dbsqlok() before processing the results set. This makes it much easier to create very responsive applications on the Windows 3.x platform.
Windows 95 and Windows NT provide preemptive multitasking. This means that the operating system can preempt your program at any time, give the CPU to another process, and then return control back to your program's execution without your knowledge or permission. Preemptive multitasking takes the burden off the application developer and puts it on the operating system—where it belongs. Win32 applications do not monopolize the system. The scheduler will preempt them whenever a higher priority process is waiting.
Multitasking means that you can run multiple programs at the same time. A program, or process, is just a set of instructions and the data that those instructions operate on. Processes can "own " resources like CPU, files, I/O devices, memory, and so on. In Win32, every process contains at least one thread of execution. A thread is sometimes referred to as a "unit of execution" because the scheduler does not schedule processes—it only schedules threads. Windows 3.x applications never have more than one thread.
Threads are function calls that execute asynchronously with respect to their creator. Each thread is scheduled in a round-robin fashion based on its priority. It will be run for a predetermined period of time, known as a time quantum, or until a higher priority thread is available. New threads are created by calling CreateThread(). The priority of any thread can be changed with SetThreadPriority(). Threads can be terminated by calling ExitThread() or TerminateThread(). If your application uses any C run-time library functions, then you must use _beginthread() and _endthread(), and then link with the multithreaded library. This allows the C run-time library to properly initialize and maintain its state information on a per-thread basis. If you link with the single-threaded library, you will introduce some very nasty and hard to find bugs into your program.
Creating new threads is fast and easy to do. How they are executed depends on the hardware and operating system you are using. On a Windows NT SMP system, each thread could execute simultaneously on different processors. Under Windows 95, or on any single processor system, the operating system will preemptively "time slice" your threads and rotate in a round-robin fashion with all the other threads waiting to be scheduled.
Threads offer a general purpose divide-and-conquer strategy for you to use in your applications. No longer do your programs have to start at the top and sequential process each step before finishing at the bottom. You can create as many threads as it needs and attack any problem in parallel. Creating threads is easy. The hard part is preventing multiple threads from interfering with each other.
Figure 3. Synchronization Objects
How do you prevent chaos? Win32 provides powerful synchronization objects to coordinate the work of multiple threads. Each synchronization object is in a signaled or nonsignaled state. Signaled means that the object is available. Nonsignaled means that it is unavailable or in use by another process/thread. The most common use for synchronization objects is to protect shared resources like global variables. Synchronization objects let you serialize access and ensure that one thread does not start reading the value of a character array, for example, until the other thread has finished writing to it. The simplest form of mutual exclusion is a Critical Section. Critical Sections provide mutual exclusion between multiple threads within a single process. Only one thread can get exclusive access to the Critical Section via EnterCriticalSection(). All of the other threads are put to sleep in a very efficient wait state (not scheduled by the OS) until they are able to get access to the Critical Section. When you are finished, call LeaveCriticalSection() and let someone else get in. Critical Sections are global variables whose value is set by the operating system (Kernel) rather than by the user. Mutexes are similar to Critical Sections, but mutexes provide mutual exclusion across processes on the same machine. Semaphores allow you to "throttle" the number of users accessing a particular resource. When the semaphore is created, the maximum number of simultaneous users is specified. When the user calls WaitForSingleObject() and the usage count is less than the maximum, then the system decrements the usage count and wakes up the thread. Critical Sections, Mutexes, and Semaphores are usually used to control access to data. Events are used to signal that some task has been completed or a task is ready to be started. Now that we know a little bit about synchronizing multiple threads, let's see how to use multiple threads in a DB-Library application.
Preemptive multitasking makes it much easier to implement asynchronous query processing. If you have multiple threads sharing a connection, then you will have to serialize access to the DBPROCESS structure because most of the DB-Library function calls are not reentrant. If multiple threads had multiple SQL statement results pending on a particular DBPROCESS structure, how would you know which result set belonged to which thread? Most multithreaded applications open multiple connections to the server. Opening a connection to SQL Server is relatively fast and inexpensive from a resource standpoint. Depending on your max worker threads configuration, each DBPROCESS structure only requires about 50K of memory.
Example 2 demonstrates how to create a multithreaded Microsoft SQL Server application. Before you can run the demo, you must run ADDUSER.SQL from ISQLW or SQL Server Enterprise Manager. This script will create 16 new users and grant them access to the various tables in the PUBS database. This multithreaded application will create up to 15 threads. Each thread will open a connection to SQL Server. None of the threads begin executing SQL statements until a manual reset event is signaled. In this case, the Win32 event acts like a traffic signal. When the traffic light is "green," or signaled, each user starts sending SQL statements to the server. Even with 15 users pounding on SQL Server, you can instantly (and gracefully) terminate all connections by just clicking on the "Stop" button. This illustrates another important technique for multithreaded applications. By running your user interface on a separate thread with a higher priority, your users will perceive that your application is very fast and responsive. Clicking on the "Stop" button resets the Win32 event object to the nonsignaled state and each thread disconnects from SQL Server and terminates by calling _endthreadex(). Figure 4 shows the new SQL Server Enterprise Manager. The Enterprise Manager "Current Activity" dialog box makes it easy to see who is using the server. You can even watch the SQL statements change, while the demo runs, by clicking on the refresh button.
So, how do you get started developing great 32-bit applications for SQL Server? The Microsoft SQL Server development libraries and documentation for ODBC, DB-Library, and Open Data Services are available via the SQL Workstation product (which also includes a complete single-user SQL Server) or MSDN Professional Subscription. For more detailed information, please see the DB-Library Programmer's Reference.