Microsoft Corporation
The Microsoft® SQL Server family of products is ideally suited to the development of client-server applications and solutions. On the server side, the powerful relational database management system (RDBMS) supports the development of industrial-strength, mission-critical applications. On the client side, Microsoft has promoted and supported an open-ended architecture that has resulted in over 125 front-ends for SQL Server.
Most client applications that work with SQL Server can be developed using powerful off-the-shelf application development tools provided by independent software vendors (ISVs). These fourth-generation tools, and some custom applications, are developed with a third-generation language (3GL), most commonly C or C++, using a call-level interface (CLI). You must consider two CLIs when developing 3GL-based client applications for SQL Server: the SQL Server native DB-Library™ and Microsoft Open Database Connectivity (ODBC).
The success of the Microsoft Windows™ graphical environment on the desktop has resulted in its becoming the premier platform on which to deliver client applications for client-server solutions. The Windows environment in general, and its cooperative multitasking nature in particular, demands that you use special care to write applications that are "well-behaved" and that coexist peacefully with other applications a user might run.
This technical article focuses on DB-Library programming techniques for Windows-based SQL Server client applications. DB-Library, a set of C functions and macros, allows your applications to interact with SQL Server. It includes functions that send Transact-SQL™ statements to SQL Server and functions that process the results of those statements. Other functions handle errors, convert data, and provide a variety of information about the interaction with SQL Server.
DB-Library is well-suited for the development of Windows-based applications, and this article highlights areas of DB-Library development that are unique to the Windows environment and often not well understood. This article assumes that you are familiar with the DB-Library application programming interface (API) and with Windows-based programming. For detailed information about DB-Library, see the DB-Library Programmer's Reference.
In general, you should use the following techniques when designing and developing a Microsoft® Windows™-based DB-Library™ application for SQL Server:
For more information, you can find complete sample applications that use the techniques discussed in this technical note in the Microsoft Online Software Library and on CompuServe® in the Microsoft Forum Software Library.
In an environment where only one application executes at a time (for example, with MS-DOS®), or in an environment that supports preemptive multitasking (for example, OS/2®), a DB-Library application does not need to lock up system resources when it executes a long-running Transact-SQL® query or retrieves a large data set.
Therefore, most DB-Library applications historically use the dbsqlexec function to send a command to SQL Server. This command is synchronous—it sends the query to SQL Server and then waits for SQL Server to process the query before returning. Similarly, most DB-Library applications use a while loop to process the results of a query.
In a cooperative multitasking Windows environment, if a query takes a long time to execute, a call to dbsqlexec can lock up the system while dbsqlexec waits for an acknowledgment that SQL Server is ready to return results. Similarly, if a large number of rows are to be returned, using a dedicated while loop to process the results of a query can tie up the system and violate the cooperative nature expected of Windows-based applications.
To address these issues, DB-Library provides the following APIs that enable applications to communicate with SQL Server in an asynchronous fashion:
There are two approaches that you can take in using these APIs in the Windows 3.x environment to allow Windows-based cooperative multitasking to operate: the PeekMessage approach and the timer approach.
The Windows environment is a cooperative, nonpreemptive, multitasking system. A Windows-based program should use background processing techniques to complete a long task such as processing a query. You can use the dbsqlsend, dbdataready, and dbsqlok DB-Library functions to accomplish this task.
Both dbsqlsend and dbsqlexec send a query to SQL Server for processing. The dbsqlexec function is a blocking function, and it maintains control of the CPU until the server completes its processing and returns results or until a timeout that was previously specified is reached. The Windows environment cannot interrupt this; all multitasking is halted while dbsqlexec is executing.
The dbsqlsend function is a nonblocking function; it sends the query and immediately returns control to the program. Then dbdataready can be used to determine when the results are available, followed by dbsqlok to verify the correctness of the query.
The following code fragment shows an MS-DOS–based or OS/2-based DB-Library program that uses dbsqlsend and dbdataready to retain control of the program while SQL Server processes a query:
dbsqlsend (dbproc);
while (!dbdataready(dbproc)
{
printf ("Waiting for results...\n");
// the program can do other work here ...
}
dbsqlok (dbproc);
A Windows-based DB-Library program could use this example method, but this would not allow the Windows environment to continue processing, and it would effectively suspend all other Windows-based programs during this time. Instead, one of two Windows-based background processing methods should be used. The first technique uses a PeekMessage loop instead of a GetMessage loop in the WinMain function. The second technique uses a Windows-based timer. Both methods require the creation of a function to do a small piece of the background task each time it is called. These techniques are illustrated in the following sections.
The PeekMessage approach replaces the standard Windows environment GetMessage loop in the WinMain function with a PeekMessage loop. This approach allows the application to process all input messages before performing background processing, allows you to do background processing in small parts, and yields control to the Windows environment after each small part is finished. When no background processing is needed, the PeekMessage loop yields control to the Windows program via WaitMessage, allowing the Windows-based system to idle.
The following example WinMain function shows how to create a PeekMessage loop for background processing. This loop has three desirable properties: First, it processes all input messages before performing background processing, thus providing good response to user input. Second, it performs background processing in small parts and yields control to the Windows environment after each small part is finished. This keeps the application from monopolizing the system. Third, it yields control to the Windows environment via WaitMessage when no background processing is necessary, allowing the Windows-based system to idle.
/*
** FUNCTION: WinMain()
**
** PURPOSE: Calls initialization function, processes message loop
**
*/
int PASCAL WinMain(HANDLE hInstance,
HANDLE hPrevInstce,
LPSTR lpCmdLine,
int nCmdShow)
{
MSG msg;
if (!hPrevInstance)
if (!InitApplication(hInstance))
return (FALSE);
/* Perform initializations that apply to a specific instance */
if (!InitInstance(hInstance, nCmdShow))
return (FALSE);
while (TRUE)
{
if (PeekMessage(&msg, NULL, 0, 0, PM_REMOVE))
{
/* PeekMessage() has found a message - process it */
if (msg.message != WM_QUIT)
{
/* Translate virtual key codes */
TranslateMessage(&msg);
/* Dispatch message to Windows */
DispatchMessage(&msg);
}
else
{
/* WM_QUIT message found, so break out of message loop */
break;
}
}
else
{
/*
* PeekMessage() has not found a message, so call
* DoBackgroundPart() to do a portion of the background
* processing. DoBackgroundPart() returns TRUE if
* it has more background work to do, or FALSE when all
* background work is completed.
*/
if (!DoBackgroundPart())
{
/*
* All background processing is now finished, so call
* WaitMessage() to yield control to Windows.
*/
WaitMessage();
}
}
}
/* Return value from PostQuitMessage */
return (msg.wParam);
}
The timer approach creates a Windows-based timer that sends a WM_TIMER message to your application at regular intervals. Each time a WM_TIMER message is received, dbdataready is checked. This approach does not require a PeekMessage loop.
The following code fragment illustrates the timer technique. This code fragment should be placed in the main window procedure. The SetTimer function creates a timer that sends a WM_TIMER message to your application every 1000 milliseconds. Be sure that the application checks dbdataready after each WM_TIMER message.
case WM_SENDQUERY:
dbsqlsend (pDbproc);
SetTimer (hWnd, 1, 1000, 0);
break;
/*
* Message: timer event occurred. Call DoBackgroundPart() to do a portion of
* the background processing. DoBackgroundPart() returns TRUE if it has more
* background work to do, or FALSE when all background work is completed.
*/
case WM_TIMER:
if (!DoBackgroundPart())
{
/*
* All background processing is now finished, so cancel the timer event.
*/
KillTimer (hWnd, 1);
}
break;
Both of the previous examples call a user-defined function (DoBackgroundPart) that does a small portion of the background processing each time it is called. In a DB-Library program, this function should do background query processing. This function should return true when it needs to continue processing the query, and false when the query processing is complete.
/*
** FUNCTION: DoBackgroundPart()
** PURPOSE: Does a small part of the background
** processing each time it is called.
**
** COMMENTS: This function assumes two global variables:
** DBPROCESS *pDbproc
** BOOL bQueryPending
**
** and that bQueryPending is TRUE only
** after another part of the program has
** sent a query to SQL Server using the
** dbsqlsend() function.
*/
BOOL DoBackgroundPart(void)
{
BOOL bContinue = FALSE;
/*
* bQueryPending is a global flag indicating if query processing is pending.
*/
if (bQueryPending)
{
if (dbdataready(pDbproc))
{
dbsqlok(pDbproc);
ProcessQuery();
bQueryPending = FALSE;
bContinue = FALSE;
}
else
{
bContinue = TRUE;
}
}
else
{
bContinue = FALSE;
}
return (bContinue);
}
The MS-DOS–based and OS/2-based implementations of DB-Library support the dbprhead and dbprrow functions. These functions allow you to display the results of a dynamic query with multiple result sets and varying datatypes and columns. They use DB-Library functions and macros to determine the column headings, number of columns, and datatypes of the columns, and then they send the output to STDOUT with rudimentary formatting. These functions are extremely useful for simple applications and during the prototyping of DB-Library applications. Because these functions send data to STDOUT, they are not available in the Windows environment.
It is not feasible to provide a generic Windows-based function to display results because there are many ways in which an application might display results (for example, in a parent or child window using TextOut or SetText, in a list box or combo box, as part of a WM_PAINT message, and so on). However, it is simple to use available DB-Library functions and macros to format the results of an ad hoc query or stored procedure. This can be implemented as a function that returns a text string, which can then be sent to the required Windows-based control. Generic functions that format a text string with the results of an ad hoc query and result column headings will be given.
The ConstructHeader function builds the string that contains the names of each column. It finds the print size of each column, allocates a buffer to hold all column names plus one space between each column name, and then copies that name into the appropriate location in the buffer. ConstructHeader should be called after dbresults has been called.
/*
** FUNCTION ConstructHeader()
** PURPOSE Builds the string that contains the names of each column.
*/
RETCODE ConstructHeader(DBPROCESS *dbproc, HANDLE *phHeader)
{
int i,iCols,iSize; /* counters */
LPSTR lpHeader; /* pointer for separator buffer */
LPSTR lpColName; /* pointer for column names buffer */
LPSTR lpPtr; /* scratch pointer */
/* Get row size and allocate memory for buffer */
iSize = DetermineRowSize(dbproc,0);
GlobalFree(*phHeader);
*phHeader = GlobalAlloc (GMEM_MOVEABLE | GMEM_ZEROINIT,
(DWORD) (iSize + 1));
if(*phHeader != NULL)
lpPtr = lpHeader = GlobalLock(*phHeader);
else
return(FAIL);
/* Initialize buffer to spaces */
_fmemset (lpPtr,' ',iSize);
/* Get number of columns */
iCols = dbnumcols(dbproc);
/* Loop on all columns, retrieving column size and name */
for(i=1;i<=iCols;i++)
{
iSize = DetermineRowSize(dbproc,i);
lpColName = dbcolname(dbproc,i);
_fstrncpy(lpPtr,lpColName,_fstrlen(lpColName));
lpPtr+= iSize;
}
/* Finished: null terminate buffer, unlock buffer and return SUCCEED */
*lpPtr = '\0';
GlobalUnlock(*phHeader);
return(SUCCEED);
}
The ConstructRow function constructs one row. Before calling this function, dbnextrow must be called to fetch the row. This routine could be used to print the current row as many times as necessary, because the current row data is always available until dbnextrow is called to retrieve the next row. This routine works like the ConstructHeader routine shown earlier, but each column's data is obtained (instead of a row name), converted to a string, and then set into the buffer.
/*
** FUNCTION: ConstructRow()
**
** PURPOSE: This function constructs one row - dbnextrow() must
** be called to fetch the row.
*/
RETCODE ConstructRow(DBPROCESS *dbproc, HANDLE *phDataVals)
{
int i,iCols,iSize,iDatasize; /* counters */
LPSTR lpDataVals; /* data buffer pointer */
LPSTR lpData; /* column data pointer */
LPSTR lpPtr; /* scratch pointer */
/* Get row size and allocate memory for buffer */
iSize = DetermineRowSize(dbproc,0);
GlobalFree(*phDataVals);
*phDataVals = GlobalAlloc (GMEM_MOVEABLE | GMEM_ZEROINIT,
(DWORD) (iSize + 3));
if(*phDataVals != NULL)
lpPtr = lpDataVals = GlobalLock(*phDataVals);
else
return(FAIL);
/* Get number of columns */
iCols = dbnumcols(dbproc);
/*
Loop through all columns, initialize to spaces and then retrieve values
*/
for(i=1;i<=iCols;i++)
{
iSize = DetermineRowSize(dbproc,i);
_fmemset(lpPtr,' ',iSize);
lpData = dbdata(dbproc,i);
if(lpData == (BYTE *)NULL) /* If NULL, use "NULL" */
{
_fstrncpy(lpPtr,(LPSTR) "NULL",4);
lpPtr += iSize;
}
else /* Else we have data, so convert to char */
{
iDatasize = dbconvert(dbproc,
dbcoltype(dbproc,i),
lpData,
dbdatlen(dbproc,i),
SQLCHAR,
lpPtr,
(DBINT)iSize-1);
lpPtr += iSize;
}
}
/* Finished: null terminate buffer, unlock buffer and return SUCCEED */
*lpPtr = '\0';
GlobalUnlock(*phDataVals);
return(SUCCEED);
}
The DetermineRowSize function returns either the size of all columns in the row, converted to character data (SQLCHAR) with one space between each column, or, if col is non-zero, the iLength of the input column converted to a string. This data is used to build the header strings and each row of data. It is also called to allocate the memory needed for each row, and to determine how much of that space is to be used for each column. If 0 is passed in as the second parameter, the size of all the rows combined is calculated.
/*
** FUNCTION: DetermineRowSize()
**
** PURPOSE: This function returns either the size of all columns
** in the row, converted to character data (SQLCHAR)
** with one space between each column, or if col is
** non-zero, the iLength of the input column converted
** to a string.
*/
int DetermineRowSize(DBPROCESS *dbproc,int iCol)
{
int i,iCols; /* counters */
int iLength=0; /* total length of column(row) */
DBINT ColLength; /* actual length of column */
int iNamelength; /* iLength of name of column */
int iPrLength; /* printable iLength */
LPSTR lpName; /* pointer to column name */
/* Get number of columns */
if(!iCol)
iCols = dbnumcols(dbproc);
/* count from 1 to numcols if col is 0, else i will equal col only */
for(i = ((iCol) ? iCol : 1);
i <= ((iCol) ? iCol : iCols);
i++)
{
/*
* Get column type & determine SQLCHAR converted iLength
* (Values below are found in SQLDB.H header file)
*/
switch(dbcoltype(dbproc,i))
{
case SQLBIT:
iPrLength = PRBIT;
break;
case SQLINT1:
iPrLength = PRINT1;
break;
case SQLINT2:
iPrLength = PRINT2;
break;
case SQLINT4:
iPrLength = PRINT4;
break;
case SQLFLT8:
iPrLength = PRFLT8;
break;
case SQLDATETIME:
iPrLength = PRDATETIME;
break;
case SQLMONEY:
iPrLength = PRMONEY;
break;
/* VARBINARY IMAGE, and BINARY ...convert to 2 times iLength */
case SQLVARBINARY :
case SQLBINARY:
case SQLIMAGE:
iPrLength = dbcollen(dbproc,i)*2;
break;
/* Other types are maximum of actual column iLength */
default :
iPrLength = dbcollen(dbproc,i);
break;
}
/* Names can be longer than column, so use name len if longer */
lpName = dbcolname(dbproc,i);
iNamelength = (lpName) ? _fstrlen(lpName) : 0;
/* Add one for space between columns */
if(iPrLength<iNamelength)
iLength+=iNamelength+1;
else
iLength+=iPrLength+1;
}
/* Return the length of the field */
return iLength;
}
The DBLOCKLIB and DBUNLOCKLIB macros are provided in the Windows-based versions of DB-Library. These macros allow an application to lock the DB-Library data segment to preserve the values of far pointers that references data in the DB-Library data segment, or they are used by DB-Library to reference application data. The DB-Library Programmer's Reference describes how to use these macros, but it only addresses how to develop applications for the lowest common denominator, real mode.
Note It is important to understand that DBLOCKLIB and DBUNLOCKLIB are needed only in DB-Library applications required to run in real mode. If your application will only run in protected mode (standard or enhanced mode), you do not need to use DBLOCKLIB and DBUNLOCKLIB.
The following sections briefly review Windows-based memory management techniques to show why DBLOCKLIB and DBUNLOCKLIB are required only for applications that must run in real mode. For detailed information on Windows-based memory management, see the Microsoft Windows SDK: Guide to Programming and Programming Windows, by Charles Petzold. The information in this technical note uses the concepts described in those references.
On computers based on the Intel® 8086 processor (or an 80286 or 80386 processor with less than 1 MB of memory), Microsoft Windows version 3.0 runs in real mode. This mode is basically compatible with Windows 2.1.
Note Beginning with the Windows operating system version 3.1, real mode will not be supported.
In real mode, a memory address consists of two parts: a 16-bit segment address and a 16-bit offset address. The 16-bit segment address is shifted 4 bits to the left and added to the offset address. The resulting 20-bit address can access 1 MB of data.
Internal registers hold segment addresses (CS, DS, SS, ES). Software for the 8086 family runs most efficiently when the segment addresses are held constant and all addressing is done by varying the offset addresses. Offset addresses are also held in internal registers (IP, SP, and so on).
An address that uses only the offset address with an implied segment address (the current segment address) is called a near pointer. An address that uses both the segment and offset address is called a far pointer. Every segment in the Windows environment's total memory is marked with certain attributes to tell the Windows environment how to manage the segment. Segments are primarily marked as fixed or movable. If necessary, the Windows system can move movable segments in memory to make room for other memory allocations. When Windows moves a segment in memory, all existing near pointers in that segment continue to be valid. However, far pointers become invalid when the segment they reference is moved.
An application that calls DB-Library uses far pointers to access data from DB-Library or to pass data to it, so the DB-Library data segment must be locked down to maintain the integrity of these pointers when they are in use.
If you are developing a DB-Library application that must run in real mode, you should follow the guidelines for DBLOCKLIB and DBUNLOCKLIB as outlined the DB-Library Programmer's Reference. The core of this advice is to nest all calls to DB-Library with calls to DBLOCKLIB and DBUNLOCKLIB. This ensures that the DB-Library data segment remains fixed when you are accessing far pointers that reference data in DB-Library or pass data to DB-Library. This approach is preferable to issuing DBLOCKLIB on startup and DBUNLOCKLIB on exit, because it defeats the purpose of the Windows-based memory management scheme by preventing the Windows environment from moving memory.
When Windows runs in protected mode, the value in a segment register does not refer to a physical memory address. Instead, this value is an offset into a descriptor table that provides a 24-bit base address in physical memory. The offset address is then added to this address to generate a 24-bit physical address that can address up to 16 MB of memory. This indirect method of segment addressing means that as long as the segment is in memory, it can be moved around by the Windows program without invalidating far pointers. The Windows program simply updates the descriptor table with the new location of the segment. It is not necessary to lock the DB-Library data segment down when accessing it or passing data using far pointers in protected mode.
It is not necessary to use DBLOCKLIB and DBLOCKLIB if your Windows-based DB-Library application will run only in protected mode. There are two ways to ensure that a Windows-based program is run only in protected mode:
Sample programs that implement the above techniques may be found in the Microsoft Online Software Library and in the Microsoft Forum Software Library on CompuServe.
The SQLWINBK sample application demonstrates the method of processing a SQL Server query in the background using a PeekMessage loop. It illustrates the method of retrieving results of an ad hoc query discussed earlier in this technical note.
SQLWINBK.ZIP contains the following files:
| Filename | Description |
| SQLWINBK.C | C-language source code |
| SQLWINBK.H | Source code header |
| SQLWINBK.RC | Windows resource script |
| SQLWINBK.DEF | Windows module-definition file |
| WINDLG.DLG | Dialog box resource script |
| WINDLG.H | Dialog box header |
| SQLWINBK.RES | QuickC® for Windows Dialog Editor resource |
| SQLWINBK.MAK | QuickC for Windows project |
| SQLWINBK. | C 6.0 NMAKE make file |
SQLWINBK.ZIP can be found in the Microsoft Online Software Library or in the Microsoft Forum Software Library on CompuServe by searching on the keyword SQLWINBK, the Q number of this article (Q80266), or S13269. SQLWINBK.ZIP was archived using the PKware file-compression utility.
The SQLWINTM sample application demonstrates the method of processing a SQL Server query in the background using a Windows-based timer. It illustrates the method of retrieving results of an ad hoc query discussed earlier in this article.
SQLWINTM.ZIP contains the following files:
| Filename | Description |
| SQLWINTM.C | C-language source code |
| SQLWINTM.H | Source code header |
| SQLWINTM.RC | Windows resource script |
| SQLWINTM.DEF | Windows module-definition file |
| WINDLG.DLG | Dialog box resource script |
| WINDLG.H | Dialog box header |
| SQLWINTM.RES | QuickC for Windows Dialog Editor resource |
| SQLWINTM.MAK | QuickC for Windows project |
| SQLWINTM. | C 6.0 NMAKE make file |
SQLWINTM.ZIP can be found in the Microsoft Online Software Library or in the Microsoft Forum Software Library on CompuServe by searching on the keyword SQLWINTM, the Q number of this article (Q80280), or S13268. SQLWINTM.ZIP was archived using the PKware file-compression utility.
This article has highlighted DB-Library programming techniques that you can use to develop SQL Server client applications that are optimized for the Microsoft Windows environment. These techniques support the development of powerful, user-friendly, front-end applications for SQL Server.
DB-Library Programmer's Reference
Microsoft Windows SDK: Guide to Programming
Petzold, C. Programming Windows (2nd Edition). Microsoft Press, 1990.
"Microsoft Open Data Services: Application Sourcebook" (Part number 098-32078)
"Query Optimization Techniques: Contrasting Various Optimizer Implementations with Microsoft SQL Server" (Part number 098-301990)
"Using Microsoft SQL Server on a Banyan VINES Network" (Part number 098-30193)
"Using Microsoft SQL Server on a Novell NetWare Network" (Part number 098-32655)
To receive more information about Windows-based DB-Library applications, contact Microsoft Inside Sales, Systems Software, at 1-800-227-4679.