Copyright: 1994 Microsoft Corporation. All rights reserved.


Knowledge Base Article Help Contents

Welcome to the ODBC SDK Knowledge Base Article Help. This file contains ODBC-specific articles drawn from the Microsoft Product Support Services Knowledge Base. The topics in the Help file include:

ODBC Transaction Isolation Levels

Getting Driver Descriptions Using SQLDataSources()

Using the SQL_DRIVER_HSTMT Option in SQLGetInfo

SqlDescribeCol Limitation on Meta Data

Cursor Behaviour in Autocommit Mode

Meaning of an Active ODBC Statement Handle

Unloading of ODBC Driver DLLs

Special Considerations for Driver Implementation on the Windows Platform

Building Drivers: Use of PROTMODE Line in .DEF Files

How to Differentiate Multiple SQLAllocEnv Calls to an ODBC Driver

Network Setup of ODBC Drivers

Sharing ODBC Handles Among Several Applications

How the Driver Manager Causes a Drivers Error Buffer to Be Cleared by SQLError

Binding SQL Results Using ODBC API from Visual Basic

Multithreaded ODBC Applications and Driver Concerns

Note   This information is provided as is. Microsoft Corporation does not guarantee it to be fully consistent with alternate sources of ODBC information.


Active Odbc Statement Handle
Meaning of an Active ODBC Statement Handle

An ODBC application can call SQLGetInfo with an fInfoType of SQL_ACTIVE_STATEMENTS to determine the number of active statement handles that an ODBC driver can support on a connection. This article explains how the concept of active statement is far removed from the states of statement handles in an ODBC State Transition table.

An ODBC application may be written such that one statement handle(hstmt) on a connection (hdbc) is used to fetch data from the backend, and a second  hstmt within the same hdbc is used simultaneously for other tasks like, for example, generating updates. But an application must not assume that the driver will support such behavior from the application. The application must first call SQLGetInfo with the fInfoType of SQL_ACTIVE_STATEMENTS to see if multiple active hstmts are supported. In ODBC, an hstmt is defined as active if it has results pending. The term results means rows from a select or the number of rows affected by an insert, update or delete operation. as in a row count.

If a driver supports only 1 active hstmt per hdbc, applications written as stated in the previous paragraph will not work. Then, applications must allocate a second hdbc, make a new connection, and then allocate another hstmt within this new hdbc to be able to concurrently use both hstmts.That is, concurrently having one hstmt with results pending as the rows are being fetched, and using the other hstmt for other application-specific tasks.

It must be noted, however, that even for a driver that supports only 1 active hstmt per hdbc, it may be possible to allocate more than 1 hstmt within the hdbc. Such  a driver may support multiple hstmts per hdbc but not multiple active hstmts per hdbc. In such cases, applications must be careful to not use other hstmts within the hdbc when results are pending on a hstmt within the hdbc.

Examples of an Active ODBC Statement Handle

The following examples assume a driver that supports only 1 active hstmt per hdbc, without limiting multiple hstmts per hdbc.

SQLAllocStmt(hdbc, &hstmt1)
SQLAllocStmt(hdbc, &hstmt2)

Example 1

SQLExecDirect (hstmt1, "select * from <a zero-row table>", SQL_NTS)
//Since the above select generated no results, it's OK to use hstmt2

SQLExecDirect(hstmt2, "select * from <table>", SQL_NTS)

Example 2

SQLExecDirect(hstmt1, "select * from <table that has rows>", SQL_NTS)
// The above statement generates a results set

SQLExecDirect(hstmt2, "some update statement", SQL_NTS)
// Error with SQLState 24000 "Invalid Cursor State"

Example 3

SQLExecDirect(hstmt1, "delete from <table that has rows>", SQL_NTS)
// The above delete generates a row count (the number of rows affected)

SQLExecDirect(hstmt2, "some update statement", SQL_NTS)
// Error with SQLState 24000 "Invalid Cursor State"

Since there are results pending on hstmt1, the last SQLExecDirect on hstmt2 in Examples 2 and 3 results in SQLState 24000 "Invalid Cursor state". Note that in example 3, if SQLRowCount had been called after the first SQLExecDirect to determine the number of rows affected, the second SQLExecDirect would have been valid.


Cursor Behaviour in Autocommit Mode

This article discusses the behaviour of cursors when autocommit mode is ON. Specifically it discusses the limitations of using multiple statement handles in this mode.

ODBC drivers which are transaction capable support two modes for transactions - manual commit and autocommit. In manual commit mode, the driver begins a transaction when an application submits a SQL statement and no transaction is open. It commits or rolls back the transaction on a call to SQLTransact. In autocommit mode, each SQL statement is a single, complete transaction; the driver commits one transaction for each statement.

All the statement handles belonging to a connection handle share the same transaction space. Thus, calling SQLTransact commits or rolls back all the inserts, deletes and updates that are in progress on all statement handles associated with the conenction.

Every statement handle corresponds to a cursor which is automatically opened when a statement is executed. When a statement is committed or rolled back, the open cursor may exhibit 3 types of behaviour:

The cursor may be closed and deleted - so any prepared statements will be lost.

The cursor may be closed, but not deleted - so, the application can re-execute an already prepared statement.

Cursor position is preserved - the application may continue to fetch from where it left off.

Suppose the driver is in autocommit mode. The current hdbc (connection handle) has two open hstmts (statement handles). hstmt1 has a select statement executing on it which has returned 100 rows. The application has fetched 20 of them. Now hstmt2 executes an update statement which finishes immediately, before any more rows are fetched in hstmt1. Because of autocommit mode, the update is committed. If hstmt1 now tries to fetch the 21st row and the cursor commit behaviour is either (1) or (2) above, then this fetch will result in error S1010 - function sequence error - because the cursor has been closed as a result of the update commit.

The above discussion applies only if the driver is transaction capable - that is, the SQLGetInfo with fInfoType = SQL_TXN_CAPPABLE returns 1. If the driver is not transaction capable (like a 1-tier ISAM driver), then there is no concept of a transaction commit and so hstmt1 can continue fetching.


SqlDescribeCol Limitation on Meta Data

SQLDescribeCol in the ODBC API and SAG Call Level Interface Specification is intended to return meta data about a column in a results set. However, it does not return table names as part of this meta data. This article discusses how this limitation stems from SQL92, and also how applications can work around this limitation.

SQLDescribeCol returns the column names of the results set in szColname. However, when two columns in a results set have the same name (as by a join between 2 tables), it does not return the table name along with the column name to differentiate the two similarly named columns.

This limitation arises from SQL, more specifically SQL92, which addresses this issue in Subclause 7.4, Syntax Rule 1b) on the 'From Clause':

"If the <from clause> contains more than one <table reference> with no intervening <derived table> or <joined table>, then the descriptors of the columns of the result of the <from clause> are the descriptors of the columns of the tables identified by the <table reference>s, in the order in which the <table reference>s appear in the <from clause> and in the order in which the columns are defined within each table."

Given the above rule, the following SQL example should clarify this:

// Table1 has a column 'address', and so does Table2 select * from Table1, Table2

The 'address' column can be differentiated as being from Table1 or Table2 depending on its position in the results set. The 'address' column from Table1 will always come before the 'address' column in Table2.


Using the SQL_DRIVER_HSTMT Option in SQLGetInfo

An ODBC application uses handles in all its calls to the driver. These calls are typically made first to the Driver Manager which then routes them to the appropriate driver. However, the handles that are passed between the application and the Driver Manager are not the same as the handles passed between the Driver Manager and the driver. That is why, the SQLGetInfo() ODBC API function has 3 fInfoTypes, SQL_DRIVER_HENV, SQL_DRIVER_HDBC and SQL_DRIVER_HSTMT which enable an app to get the handles used by the driver. This article discusses how such handles are managed by the Driver Manager and how they can be used by an application to call a driver directly.

The role of the Driver Manager(DM) as discussed in the ODBC SDK Programmer's Reference is to load drivers, provide entry points to an application for eventual calls to the driver, to validate arguments passed by an application and to check state transitions. To deliver this functionality, the DM uses internal structures to manage all connections and statements used  by an application.

For instance, when an application calls SQLAllocStmt() with the address of a statement handle, the DM allocates memory to manage this new hstmt. It then stores the far pointer to the DM-allocated memory in the statement handle of the application. The DM then calls SQLAllocStmt() in the Driver and uses a different handle variable to pass to the Driver. The Driver allocates memory to manage the statement, and can store its 32-bit pointer in the handle address passed to it by the DM.

The following example demonstrates a simplified version of this process. It does not show how the DM associates an application's handle (hstmtApp) with the driver's handle(hstmtDrv ). Also note that a similar process applies to the hdbc and henv handles.

Application ---->	DriverManager ---->	Driver
SQLAllocStmt (hdbc, &hstmtApp)	*hstmtApp =        GlobalAlloc(memory) SQLAllocStmt(hdbc, &hstmtDrv)	*hstmtDrv= GlobalAlloc()
SQLExecDirect (hstmtApp, "select")	Map hstmtApp to hstmtDrv.SQLExecDirect (hstmtDrv, "select")	To Driver
SQLGetInfo (SQL_DRIVER_HSTMT)	<----DM returns hstmtDrv

// Windows API calls hLib = GetModuleHandle("driver.dll") procPvtDriverFunc = GetProcAddress(hLib, "PvtDriverFunc")

// Call to ODBC Driver *procPvtDriverFunc (hstmtDrv,"do something")---------->To Driver

An application, after connecting to a data source, may wish to bypass the DM and communicate directly with the Driver. This is not recommended because it assumes a good deal of familiarity between the application and the driver, and thus impedes interoperability.

In cases where this needs to be done, an application can first call the ODBC SQLGetInfo() function and pass one of the following values for the fInfoType argument depending on what driver handle it wishes to retrieve: SQL_DRIVER_HENV, SQL_DRIVER_HDBC, SQL_DRIVER_HSTMT. When using 1.0 ODBC drivers, the application can then make the GetModuleHandle() call (For drivers that comply with the ODBC 2.0 specification, the application can call SQLGetInfo (SQL_DRIVER_HLIB) instead of GetModuleHandle to get the handle of the Driver DLL) and the GetProcAddress() call to the Windows API if does not already have the handle to the loaded Driver DLL and the entry points into the driver respectively. Now, the application can call the driver directly with these ODBC driver handles and bypass the Driver Manager.


Getting Driver Descriptions Using SQLDataSources()

SQLDataSources() enumerates ODBC data source names and is implemented by the ODBC Driver Manager. The 'description' it returns through szDescription is the driver description (as opposed to data source description) This article discusses how this 'description' can be used, and also why the 'description' is not a description of the data source, as the name of this function implies.

The driver description given to a driver by a driver developer is recorded in the ODBC drivers section in the odbcinst.ini file. For instance, the description for the dBase driver is "dBase Files (*.dbf)". When a data source called ISAMDATA is set up for this driver, an entry is made to the odbc.ini file under the ODBC data sources section thus:

[ODBC Data Sources] ISAMDATA = dbase Files (*.dbf)

SQLDataSources() returns "dbase Files  (*.dbf)" in szDescription for the ISAMDATA Data Source. How can an application make use of this? The application may allow the user to choose File->Open (using the GetOpenFileName() function in the Windows Common Dialog Library) for ISAM files.

Note   ODBC 2.0 driver writers should use the FileExtns keyword in the driver description section. For more information, see "Driver Keyword Sections" in Chapter 19, "Installing ODBC Software," in the Microsoft ODBC Programmer's Reference.

The application can call SQLDataSources() to get the driver descriptions for the ODBC data sources, and put the string "(dbase Files)" in the File Name edit control of the dialog box. It can then then parse the "*.dbf" part from szdescription so that the 'List Files of Type' listbox can then be used to limit the files displayed (OPENFILENAME.lpstrFilter for Windows programmers), in this case only *.dbf files. The application can then make the SQLDriverConnect() call to connect to the data source, and make the appropriate ODBC calls to get data from the ISAM file chosen by the user.

It is therefore recommended that driver developers use such descriptions for their drivers for ISAM-type files.


ODBC Transaction Isolation Levels

ODBC provides five levels of transaction isolation. This article discusses the concept of transaction isolation levels and the relation between ODBC and ANSI SQL2 isolation levels.

Transaction isolation level refers to the degree to which multiple interleaved transactions are prevented from interfering with each other in a multiuser database system. Ideally, one would like to have "serializable" transactions - that is, the interleaved execution of any set of concurrent transactions will produce the same effect as some (unspecified) serial execution of those same transactions. The ANSI SQL 2 standard defines three specific ways in which the serializability of a transaction may be violated (with the implication that these are the only permitted violations):

P1.	Dirty Read: Transaction T1 modifies a row. T2 then reads the row. Now T1 performs a rollback - so, T2 has seen a row that never really existed.

P2.	Non-repeatable Read: T1 retrieves a row; then T2 updates that row and T1 retrieves the "same" row again. T1 has now effectively retrieved the "same" row twice and has seen two different values for it.

P3.	Phantoms: T1 reads a set of rows that satisfy certain search conditions. T2 then insert one or more rows that satisfy the same search condition. If T1 repeats the read, it will see rows that did not exist previously - "phantoms".

These three phenomena are referred to as P1, P2 and P3, respectively. The various isolation levels are defined by SQL2 in terms of which of these three violations of serializability they permit. They are:

1.	READ_UNCOMMITTED - Permits P1, P2 and P3.

2.	READ_COMMITTED - Permits P2 and P3. Does not permit P1.

3.	REPEATABLE_READ - Permits P3. Does not permit P1 and P2.

4.	SERIALIZABLE - Does not permit any of P1, P2 and P3.

ODBC defines five isolation levels: SQL_TXN_READ_UNCOMMITTED, SQL_TXN_READ_COMMITTED, SQL_TXN_REPEATABLE_READ, SQL_TXN_SERIALIZABLE, and SQL_TXN_VERSIONING. The first four correspond to ANSI isolation levels 1-4 respectively. SQL_TXN_VERSIONING provides SERIALIZABLE transactions, but does so without a significant impact on concurrency.

Transaction isolation is achieved by locking protocols. The various tables are or parts thereof are locked so that two writers cannot access it at the same time, or preventing reader access when writing is being done, and so on. One of the side effects of this is to drastically reduce concurrency. Typically, isolation levels 3 and 4 are achieved by locking protocols which drastically reduce concurrency. SQL_TXN_VERSIONING refers a non-locking way of achieving levels 3 and 4, thereby increasing concurrency. An example of this is Oracle's Read Consistency isolation level.

It is typical of many database systems to provide a lower level of isolation by default and provide explicit concurrency control facilities to achieve serializable transactions. For example, Sybase/Microsoft SQL Server provides Level 2 locking (READ_COMMITTED) by default. But using the HOLDLOCK keyword within a transaction will guarantee serializability. Similarly, IBM'd DB/2 provides two isolation levels called CS (Cursor Stability) which corresponds to READ_COMMITTED and RR (repeatable read) which corresponds to SERIALIZABLE. However, it provides a LOCK TABLE statement which allows users operating at CS level to achieve serializability of they wanted to.

Because of these differences in implementations, an interoperable ODBC application must use SQLSetConnectOption to set the transaction isolation level, instead of using the various implementation defined locking levels.


Unloading of ODBC Driver DLLs

A driver gets unloaded by the ODBC Driver Manager when an application calls the ODBC SQLDisconnect() function by passing the handle to the database connection made by that driver. This article discusses how the driver is unloaded.

All ODBC calls by an application are intercepted and routed by the ODBC Driver Manager (DM). When an application calls the SQLDisconnect() function on a particular connection, the DM calls the driver's SQLDisconnect() function.

When the driver returns a status of SQL_SUCCESS or SQL_SUCCESS_WITH_INFO on this call, the DM then calls the driver's SQLFreeConnect() and SQLFreeEnv() in quick succession. Also, since the driver has made a successful disconnect on the call to SQLDisconnect(), the DM calls the FreeLibrary() function in the Windows API to reduce the reference count of the driver DLL by 1. When the reference count of the Driver DLL gets to 0 after repeated calls to SQLDisconnect() by the application for all connections made by that driver, Windows will unload the driver DLL.

Driver developers may note minor implications arising out of the above. Note that every time an application calls SQLDisconnect(), the DM, besides calling the driver's SQLDisconnect(), also calls the Driver's SQLFreeConnect() and SQLFreeEnv() immediately. Thus SQLFreeEnv() is called on every SQLDisconnect(). Interestingly enough, when the application calls SQLFreeConnect() and SQLFreeEnv(), the DM does its own cleanup and does not make the same calls to the Driver because these calls would have been made at the time of SQLDisconnect().

If an ODBC application terminates without calling SQLDisconnect on all open connections, Windows will unload the Driver Manager if its reference count is reduced to 0. However, the Driver Manager cannot unload the driver DLL because the WEP of the Driver Manager (or any Windows DLL for that matter) cannot call the FreeLibrary function.


Special Considerations for Driver Implementation on the Windows Platform

Since Windows is a non-preemptively multitasked operating system, an application needs to yield control back to windows -- otherwise, Windows has no way of preempting the application and scheduling another. When an application calls an ODBC API, it is possible that the API might take a long time to complete. For example, the driver may need to do a network operation; or it may need to send an SQL statement to the data source and wait for the data source to finish processing it. How should a driver behave when it is processing an ODBC API call that can take a long time?

On a preemptively multitasked system like Windows NT, this poses no problem. The driver need not do anything special -- the OS will preempt the application (or the thread) and schedule another application (or thread). However, on Windows the entire system will appear to hang if control is not yielded back to Windows either by the driver or by the application. Under these circumstances, it is possible for a driver to yield control back to Windows by calling the PeekMessage API. However, this is not a desirable thing to do because of the reasons discussed below:

The PeekMessage API provides 3 flags: PM_NOYIELD, PM_REMOVE and PM_NOREMOVE. For the purposes of this discussion, it is assumed that the PM_NOYIELD is turned off - causing the PeekMessage call to yield control to windows. The ODBC driver can call PeekMessage with PM_NOREMOVE without any side effects. However, it is also useless if the driver really wants to yield control back to Windows so that other applications can be scheduled. Although PeekMessage with PM_NOREMOVE does yield control back to Windows, it is almost guaranteed that the front of the message queue has a message for the application that called the ODBC driver -- which means that no other application can be scheduled until this message is removed. It follows that the only way that a DLL can yield to other applications via PeekMessage is by using the PM_REMOVE flag. Since PeekMessage with PM_REMOVE causes the message to be removed from the message queue, the ODBC driver must also dispatch the message by calling DispatchMessage. This PeekMessage/DispatchMessage is thus a message loop which appears in the ODBC driver DLL.

Many large Windows applications have a very complex message processing mechanism, including extensive preprocessing of messages. That is, there is a lot of code between the GetMessage (or PeekMessage) and the DispatchMessage calls in the message loop. All message loops that are entered on behalf of a Windows application must fall into one of two catagories:

1.	Modal message loop   These message loops are entered for things such as message boxes, dialog boxes and menus.  The main point here is that no messages that do anything (like mouse clicks and keystrokes) go to the application, they are all going to the modal window.

2.	Knowledgable sub-message loops   These message loops are entered for some reason such as to yield control to other applications at some point in a program.  But these message loops have full knowledge of how the application processes messages.  That is, if the application is translating accelerators, the sub-message loop calls the proper TranslateAccelerator function.  If the application is filtering some messages, the sub-message loop knows how to do that.  If the application is in some mode that effects how it processes messages, it knows how to deal with that.  The list goes on.

When an ODBC driver calls PeekMessage/DispatchMessage, it is essentially creating sub-message loops that have no knowledge about how the application that called them is processing messages.  It blindly dispatches everything it sees -- thus bypassing the applications message preprocessing. This can cause unexpected behavior in an application with sometimes drastic side effects. Another problem with the ODBC driver yielding via PeekMessage/Dispatch message is reentrancy. The sub-message loop created by the driver is not modal; so when there are messages resulting from events like mouse clicks and keystrokes, Windows can schedule the same application (which called the ODBC driver) again. In other words, the application has been reentered. Due to the complexity of the Windows message processing model, many applications do not adequately guard against reentrancy -- thus causing unexpected behavior.

Since there are undesirable side effects when an ODBC driver creates submessage loops, the yielding must be handled by the application when it calls an ODBC API which could potentially take a long time to execute. The way to do this is for the application to call the API in asynchronous mode. On receiving the call, the driver will immediately return to the application with a return value of SQL_STILL_EXECUTING. The application can now yield control to Windows via a knowledgeable sub message loop, and poll the driver every now and then to see if the API has finished executing. For more information on the asynchronous processing of ODBC API, please refer to Microsoft ODBC SDK Programmers Reference.

Many times, the underlying components that an ODBC driver uses create non-knowledgeable sub-message loops using PeekMessage/DispatchMessage, as described above -- leading to the same problems. Examples are: asynchronous DDEML libraries, certain communications DLLs and so on. Driver writers are advised to make sure that the DLLs that their drivers rely on do not exhibit this behavior.


Building Drivers: Use of PROTMODE Line in .DEF Files

The Driver Manager version 2.0 and later can resolve ODBC functions exported by a driver by ordinal value or by name. The Driver Manager makes this choice based on whether the driver exports a function with ordinal value 199 or not. This article discusses potential problems that can occur due to this method and how to overcome them.

To resolve the addresses of the ODBC functions exported by the driver, the Driver Manager checks if the driver exports a dummy function with the ordinal 199. If the driver does not export this function, the Driver Manager resolves the functions by name. If it does export this function, the Driver Manager resolves the functions by their ordinal value, which is much faster.

If a driver does not want the Driver Manager to load by ordinal value, it MUST NOT define ordinal 199 in its .DEF file. The following paragraphs describe a case where the C compiler can define ordinal 199 even though the driver has not exported it.

Exported functions in an executable file (.EXE or .DLL) contain two tables that are used to resolve exported entry points in the executable - the name table and the entry table. The name table is used to resolve function names to an ordinal value. For example, if your driver exports a function with the following .DEF file lines:

EXPORT

   SQLAllocStmt      @4

The driver will have an entry in the name table that can be used to resolve the string "SQLAllocStmt" to the value 4.

If you were to call GetProcAddress with the string "SQLAllocStmt", Windows would first scan through the name table looking for the string - this is why GetProcAddress is slow. To resolve the ordinal value into a function address, GetProcAddress uses the entry table.

The entry table contains a description of every exported function in the executable, ordered by ordinal value. If the executable was not explicitly built for protected mode only, the entry table also contains an entry for every far function in the executable. This is a leftover from real mode - in real mode, selectors were subject to change and hence Windows needed to track movable code segments. The far functions are added to the entry table as if they were exported functions, using any unused ordinals.

Consider the following .DEF file excerpt:

EXPORT

   SQLAllocEnv        @1

   SQLAllocConnect    @2

   .

   .

   .

   SQLTransact	      @49

If the driver was not built as a protected-mode-only DLL, there could potentially be slots in the name table corresponding to ordinal values beyond 49 and conceivably for 199 as well, if the driver had enough far functions.

When the Driver Manager loads this driver, it calls GetProcAddress with an ordinal value of 199 and gets back the address of a far function. Since it has no way of distinguishing whether this was a "true" export or not, it will proceed to load other exported functions by ordinal values as well - and if the driver doesn't define its functions in the ordinal order defined by ODBC, this will cause unexpected behavior.

To resolve this problem, bulid the driver as a protect-mode only DLL. Use the PROTMODE statement in the .DEF file to direct the linker to make the driver protected-mode-only.


How to Differentiate Multiple SQLAllocEnv Calls to an ODBC Driver

In ODBC 1.0, every time an application calls SQLConnect, the Driver Manager calls the driver's SQLAllocEnv, SQLAllocConnect, and SQLConnect functions in succession. This means that an ODBC driver can be called to allocate multiple environments for the same application.

Since a driver must allocate only one environment per application, it must be able to distinguish between the first call to SQLAllocEnv from an application and subsequent calls to SQLAllocEnv.

Since only one environment should be allocated for an ODBC application by the driver, even though multiple calls to SQLAllocEnv are made from the Driver Manager, a driver can use the Windows API function GetCurrentTask to determine whether an environment has already been allocated for the calling application (task).

Typically, a driver can build a list of tasks that currently have the environments allocated, and on every call to SQLAllocEnv, the driver can use this list to determine if the current task has an allocated environment. If the task is found in this list, the driver can return the previously allocated environment associated with that task, without having to allocate a new environment.

Drivers written to the ODBC version 2.0 specification need not use the above mechanism because the 2.0 Driver Manager does not call SQLAllocEnv in the driver on every SQLConnect. Thus, an ODBC 2.0 driver is assured that its SQLAllocEnv will be called only once on the first SQLConnect from the application. Subsequent SQLConnect calls in the application to that driver will not result in SQLAllocEnv calls into the driver.


Network Setup of ODBC Drivers

The ODBC Setup program cannot be used to install the ODBC components on a file server with just the .INI files being installed on the client machines, as is typical of network installations of other software products. The ODBC Setup program, however, can be used by administrators to simplify the installation of ODBC components on client machines. This article explains how to use a setup option to make ODBC installation on several client machines simple. Note that all the ODBC components will be installed on every client workstation.

The Setup program shipped with ODBC drivers in Microsoft products such as Access, Visual Basic, and so on, has an option called auto ,which, given a valid ODBC installation on a client workstation, makes it simple to duplicate the same ODBC setup on other client machines.

Here is the series of steps an administrator would use:

1.	Install the ODBC drivers and add the required data sources on a machine.

2.	Create a directory on a network file server and copy all the files on the ODBC setup diskette into this directory.

3.	Take the ODBC.INI and ODBCINST.INI files created by Setup in step 1 and copy them to this new directory.

4.	Modify the setup.lst file on the file server so that the following two lines are added to the [Files] section:

   odbc.ini     = odbc.ini

   odbcinst.ini = odbcinst.ini

5.	From the client machines, connect to this network share, and run setup/auto from the share.

All the drivers and data source entries will be copied to the clients workstation. It must be noted that data sources existing  on the client workstation before running setup/auto will continue to remain in the clients odbc.ini file. If the client workstation did not have any ODBC components or .INI files, setup/auto will create the new .INI files and copy all the drivers from the network drive to the client machine.

Microsoft is currently considering an enhancement to ODBC Setup so that the drivers can reside on a network share thus requiring only the .INI files on the client workstations.


Sharing ODBC Handles Among Several Applications

When a Windows DLL is written such that it acts as an intermediary between several applications and ODBC drivers, it may be necessary to share connections among the applications. This article discusses the architectural blocks to sharing ODBC environment, connection, and statement handles among several applications through a user Windows DLL. It also discusses how a service provider DLL can be written so that it can access ODBC drivers and provide an interface to applications without sharing handles between applications.

ODBC environment, connection, and statement handles are unique to tasks and cannot be shared among different tasks because the ODBC Driver Manager and ODBC drivers, when allocating memory for the connection, do not use the GMEM_DDESHARE option on the GlobalAlloc. For instance, in response to a call from an application, when a user DLL calls SQLAllocConnect in the driver DLL, the memory is being allocated by the driver DLL in the context of the calling task, for example, the task that called the user DLL. In this scenario, another task that calls this same user DLL cannot then use this recently allocated connection handle to call ODBC functions.

If an ODBC connection needs to be shared among several applications, the following equivalent approach may be used. Instead of writing a DLL that shares various ODBC handles among applications, write an application that provides the same services as the user DLL would. Such an application would contain the calls to SQLAllocEnv, SQLAllocConnect, SQLAllocStmt, and various other ODBC calls, and the other applications that need ODBC access can communicate via DDE with this DDE Server application. All ODBC function calls would then be encapsulated in the DDE Server application.

When writing a user DLL (without sharing ODBC handles among applications), it is important to have a separate ODBC environment for every task/application. Therefore, each application must make its own calls to SQLAllocEnv, or have an initialization routine in the user DLL call SQLAllocEnv in the ODBC driver in the context of the calling application/task.


How the Driver Manager Causes a Drivers Error Buffer to Be Cleared by SQLError

This article discusses how the 1.0 Driver Manager calls a drivers SQLError function to flush the errors in the drivers error buffer. Chapter 16 of the ODBC SDK Programmers Reference documents the ODBC convention for error text, return codes and rules for handling error conditions. In the "Returning Error Messages" section in this chapter, it reads: Each time the application calls SQLError, the driver returns the next error message in the buffer. When the application calls a different function, the driver discards the current contents of the error message buffer. It's unclear from the last sentence whether the driver clears the current error buffer on entry into the next function, or  whether the Driver Manager calls the driver to clear its error buffer before entry into the next function. This article explains how when an application calls ODBC function B() after a previous call to ODBC function A(), the Driver Manager actually calls the driver's SQLError first and then calls the driver's function B(). The 2.0 Driver Manager will not flush the error buffers of drivers, and drivers must, in accordance with the 1.0 specification, flush the error buffers themselves.This article therefore describes only the 1.0 behavior.

The implication of the Driver Manager calling the drivers SQLError is that on entry to function B() in the driver, the driver has already cleared the error buffer. Developers need to note this when debugging their drivers. Also, when the Driver Manager calls the drivers SQLError, it passes the appropriate ODBC handles, but for other arguments to SQLError like szSQLState, pfNativeError, and so on, it passes a NULL. The SQLError function for drivers must be written such that when passed NULL arguments for everything except ODBC handles in SQLError, the errors in the drivers error buffer are cleared. Following is a diagram showing how this is process is accomplished by the 1.0 Driver Manager. It must be noted that the 2.0 Driver Manager will not behave like the 1.0 Driver Manager.

Application               Driver Manager             Driver

-----------               --------------             ------

    A()        =====>         A()        =====>     A() = SQL_ERROR

= SQL_ERROR    <=====       <=====       <=====     Returns SQL_ERROR

//Application does not call SQLError()

    B()        =====>         B()

                             SQLError()   =====>    SQLError()

                                    Loop for clearing errors

                                          <=====

                        DM calls Drivers B()

                             =====>       =====>    B() = SQL_SUCCESS

= SQL_SUCCESS  <=====        <=====       <=====    Returns SQL_SUCCESS


Binding SQL Results Using ODBC API from Visual Basic

Visual Basic developers can always call Windows DLLs, provided that Basic function declarations for the functions in the DLL are included in the Visual Basic Project. When calling ODBC drivers from Visual Basic, there are a few considerations to keep in mind. This article discusses issues surrounding the use of the SQLBindCol ODBC function from Visual Basic.

After submitting a SQL query, an ODBC application binds the results of that query into variables. Typically one variable is associated with each column in the results set. Following is the SQLBindCol declaration that must be used by a Visual Basic program:

Declare Function SQLBindCol Lib "odbc.dll" Alias "SqlBindCol"
(ByVal hstmt As Long, ByVal icol As Integer, ByVal fCType As Integer,
rgbValue As Any , ByVal cbValueMax As Long, pcbValue As Long)
As Integer

Note that the function must be declared on one line and not split into several lines, unlike the declaration above.

Following are some of the rules that you must follow for SQLBindCol in your Basic code:

1.	When binding to strings, bind to variable-length Visual Basic strings, not fixed-length strings.

Variable-length strings are declared thus:

   Dim mystr as String

2.	Before binding variable-length strings, initialize them thus:

   mystr = String$(255,0)

This allocates a string.

3.	When calling SQLBindCol and binding to a string (that is, if rgbValue is going to be a string), use the Byval keyword.

For example:

   SQLBindCol(hstmt&, 1, SQL_C_CHAR, Byval mystr, cbMax&, cbVal&)

4.	Use cbColDef appropriately. Refer to Appendix D of the Programmers Reference for the ODBC SDK.

5.	Use a LONG variable for pcbValue, and not a literal value. Remember that we are passing by reference.

If a Visual Basic application follows the rules mentioned above, SQLBindCol can be successfully used to bind data returned by SQLFetch.


Multithreaded ODBC Applications and Driver Concerns

Introduction

The purpose of this article is to discuss the usage of threads by applications using ODBC and effect of these threads on ODBC drivers.

The 32-bit ODBC SDK 2.0 contains components that are thread-safe. These include the Driver Manager and cursor library. Due to the nature of the code provided in the ODBC SDK for thread safety, 2.0 drivers are inherently thread-safe, except for the SQLCancel case described below. ODBC Spy isnt thread-safe, and it should not be used with threaded applications.

Implementation

The ODBC Driver Manager and cursor library use critical sections to provide synchronization on each object (HENV, HDBC, or HSTMT). In addition there are critical sections associated with actions on an object (error processing and canceling).

Critical sections are the most efficient synchronization objects available in Windows NT to synchronize operations between threads. When a critical section is entered, all other threads that attempt to enter that critical section are blocked until the holding thread leaves the critical section. A thread can reenter a critical section, which simply increments a usage count. Once that usage count has been decremented to zero, a waiting thread is allowed to enter the critical section.

The design of the ODBC Driver Manager critical sections is hierarchical. Each critical section has a parent critical section. When entering a critical section, the parent is first entered and then the desired critical section is entered. Then the parent critical section is exited. This provides the appropriate blocking for operations like freeing the object and prevents deadlocks from occurring. Once the operation is complete, the critical section is exited.

Global Critical Section owns:

     HENV Critical Section owns:

          HENV Error-Queue Critical Section

          HDBC Critical Section owns:

               HSTMT Critical Section owns:

                    HSTMT Cancel-in-Progress Critical Section

Global Tracing Critical Section

An action on an object (HENV, HDBC, HSTMT) causes the Driver Manager to enter the critical section for that object, per the rule described above. For an action on an HSTMT, such as SQLExecDirect, the Driver Manager enters the critical section for the HSTMTs HDBC, enters the critical section for the HSTMT, and then leaves the HDBCs critical section. The Driver Manager then performs error and state checking, calls the driver (if no errors were detected), makes any state transitions, and leaves the HSTMTs critical section just prior to returning to the application. If another thread tries to perform an action on that HSTMT, this thread will be blocked until the first threads action is complete. While blocked, the second thread holds the parents (HDBC) critical section, so additional threads are blocked at that point. Because the critical section is maintained while the driver is called, the driver is guaranteed that it wont be reentered by another thread with an action involving the same object. Of course, multiple threads may be performing actions on different objects simultaneously.

The ODBC specification allows for calling SQLCancel synchronously on an HSTMT using a different thread while another thread is busy with an action on that HSTMT. This typically occurs when a lengthy operation is executing and the user wants to terminate that operation. The ODBC SDK Cursor Demo application, when compiled for threading, provides this capability. When SQLCancel is called by the application, the Driver Manager enters the HSTMT cancel-in-progress critical section. This prevents SQLCancel from being reentered before the first SQLCancel is complete. The drivers SQLCancel is then called to perform the cancel operation. If the driver determines that there is no simultaneous action occurring on the HSTMT, the SQLCancel is treated as a SQLFreeStmt/SQL_CLOSE operation. The driver then returns SQL_SUCCESS_WITH_INFO with SQLSTATE 01S05. The Driver Manager then updates its state table appropriately, but returns SQL_SUCCESS to the application. If there was a simultaneous action, the return code from the original threads ODBC function will determine if the cancel was successful or not.

When an object is being freed (SQLFreeEnv, SQLFreeConnect, SQLFreeStmt, SQLDisconnect, or SQLCancel -- when processed as a SQLFreeStmt/SQL_CLOSE), the parents critical section is entered. Then the objects critical section is entered and each childs critical sections are entered. This makes sure that no actions are occurring on the object or any of its children while they are being freed. The driver is then called to perform the action. Once completed successfully, the Driver Manager frees the child objects, followed by freeing the object itself.

Errors in the Driver Manager are queued on the HSTMTs or HDBCs HENV. The HENV error-queue critical section synchronizes actions on that error queue. Applications and drivers arent affected by this.

The global tracing critical section is used by Driver Manager to synchronize logging to the trace file. Applications and drivers arent affected by this.

The cursor library relies mainly on the Driver Managers use of critical sections. However, there are some actions which the cursor library performs on a one-to-many basis. Some of these are done on other application HSTMTs, such as obtaining cursor names, or on internal HSTMTs allocated by the cursor library. In these cases, the cursor library will enter the appropriate critical sections prior to calling the driver.

Neither the Driver Manager nor the cursor library creates threads of its own. Neither the Driver Manager nor cursor library ever pauses or resumes any thread.

Applications and Threads

The Microsoft ODBC Programmer's Reference suggests two methods for writing a multithreaded ODBC application. The first involves having multiple HSTMTs on a single HDBC, where a thread is used for the actions on each HSTMT. The second suggested usage is having a single HSTMT on multiple HDBCs, where a thread is used for the actions on each HDBC. The latter could be used for a driver that only supports one HSTMT per connection. Either of these allows continuing actions on other HSTMTs or HDBCs without a lengthy operation on one blocking the others.

The application needs to be aware of what operations are occurring on an object. For example, if two threads both perform a SQLFetch on the same HSTMT, the Driver Manager will synchronize these actions, but the data returned from the second SQLFetch will overlay the firsts for the bound columns. The timing on this could cause the first thread to process some of the first rows data and some second rows data. Furthermore, because errors are kept for an object until the next function on that object is called, the first function might return SQL_ERROR, but the second function could clear the error before the first thread was able to call SQLError. Because of these problems, applications should avoid using multiple threads to fetch data on the same HSTMT.

Freeing an HSTMT is particularly critical. The Driver Manager prevents the SQLFreeStmt/SQL_DROP from occurring until after any already started operation is complete on that HSTMT. However, if its possible for the SQLFreeStmt to start prior to the other operation, the second operation will be blocked until the SQLFreeStmt is complete. Then the second operation will cause a general-protection fault with an access violation, because the HSTMT no longer exists. The same can occur when calling SQLDisconnect, because that implicitly drops any of its HSTMTs. The application must synchronize these actions to make sure that the HSTMT or all of the HDBCs HSTMTs are quiesced prior to calling SQLFreeStmt/SQL_DROP or SQLDisconnect.

SQLCancel no longer clears any errors posted on the HSTMT. This prevents the case where the first threads function posts an error, but SQLCancel was called prior to that thread being able to call SQLError.

Drivers and Threads

Writers of 32-bit ODBC drivers must handle the synchronous SQLCancel case. All other aspects of supporting multithreaded applications are handled by the Driver Manager and cursor library. The Driver Manager allows a drivers SQLCancel to be called while another thread is working on the same HSTMT. The driver is responsible for detecting this and performing the necessary action to cancel the original action. If there is no simultaneous action occurring, the driver must treat the SQLCancel as a SQLFreeStmt/SQL_CLOSE and return SQL_SUCCESS_WITH_INFO with SQLSTATE 01S05 to the Driver Manager. Only the driver knows the exact state of the original thread.

The implementation for SQLCancel in a Microsoft two-tier driver was done as follows:

In the driver, each HSTMT has a critical section. This critical section is entered upon entry for each function that has an HSTMT argument (except for SQLCancel) and left upon exit. The drivers SQLCancel enters a HSTMT cancel critical section. The SQLCancel code then inspects the HSTMT critical section to determine if its been entered (OwningThread1 non-zero). This is the determining test for performing a SQLFreeStmt/SQL_CLOSE operation. If there is simultaneous action, the driver then determines if the server is performing some action. If so, it sends a cancel message to the server and sets a cancel acknowledgment expected flag. The server will then cancel the operation and acknowledge the cancel. The original thread will then read that acknowledgment and determine if the server cancel was successful or not and return the appropriate information to the application. If there isnt any server action occurring, a cancel requested flag is set on the HSTMT. The original thread will periodically status that flag and if set, terminate its operation and return the appropriate error. Upon exiting each function that has an HSTMT argument, the HSTMT cancel critical section is entered, the cancel requested flag is cleared and both the HSTMT critical section and HSTMT cancel critical section are left. This guarantees that a SQLCancel thats too late to actually cancel the operation doesnt leave the cancel requested flag set.

A single-tier driver would be similar, except that theres no server action to worry about.

ODBC doesnt place any limitation on the use of driver-created threads.

Testing of Thread-Safe Drivers

Testing of thread-safe drivers is complicated and uncertain. Most thread bugs are eliminated through good design, careful coding, and desk checking.

The most common way of debugging in a threaded environment is the brute force way; that is, create a threaded application that randomly on multiple threads performs the sequences desired. Then just run this application for long periods of time. This will find some threading bugs, but typically they will be hard to reproduce and fix because of the randomness of the test. This is a good way to validate that the code is thread-safe.

The method that was used for the Microsoft two-tier driver described above was to create a special test application and instrument the driver. The driver had a delay (sleep) at some critical section of its code. The test application created a thread. This thread performed a certain set of interactions with the driver. The main thread blocked on a synchronization object. At the point in these actions just prior to the call to the driver function that had the sleep added, the synchronization object was reset. This woke up the main thread. It delayed long enough to make sure that the driver was called and was performing the sleep. The main thread performed the SQLCancel. Breakpoints can be set in the drivers SQLCancel code and just after the sleep code. Then the code can be walked to make sure that the desired actions are performed. The delay in the driver needs to be long enough to make sure that the SQLCancel can be performed prior to the sleep ending. The delay in the applications main thread needs to be long enough to make sure that the drivers sleep is reached prior to the SQLCancel being performed. By progressively stepping through each piece of critical code related to SQLCancel in the driver, its possible to debug each section individually in a reproducible fashion.

Good luck and happy debugging!

__________

1 The OwningThread is defined as a HANDLE in the critical section structure, but it actually contains the result of GetCurrentThreadId, not the GetCurrentThread.