ODBC Driver

An application can be connected to any number of drivers and data sources. These can be a variety of drivers and data sources, the same driver and a variety of data sources, or even multiple connections to the same driver and data source.

Allocating the Environment Handle

The first task for any ODBC application is to load the Driver Manager; how this is done is operating-system dependent. For example, on a computer running Microsoft® Windows NT® Server/Windows 2000 Server, Windows NT Workstation/Windows 2000 Professional, or Microsoft Windows® 95/98, the application either links to the Driver Manager library or calls LoadLibrary to load the Driver Manager DLL.

The next task, which must be done before an application can call any other ODBC function, is to initialize the ODBC environment and allocate an environment handle, as follows:

1.      The application declares a variable of type SQLHENV. It then calls SQLAllocHandle and passes the address of this variable and the SQL_HANDLE_ENV option. For example:
Copy Code

SQLHENV henv1;

SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv1);

2.      The Driver Manager allocates a structure in which to store information about the environment, and returns the environment handle in the variable.

The Driver Manager does not call SQLAllocHandle in the driver at this time because it does not know which driver to call. It delays calling SQLAllocHandle in the driver until the application calls a function to connect to a data source. For more information, see Driver Manager’s Role in the Connection Process, later in this section.

When the application has finished using ODBC, it frees the environment handle with SQLFreeHandle. After freeing the environment, it is an application programming error to use the environment’s handle in a call to an ODBC function; doing so has undefined but probably fatal consequences.

When SQLFreeHandle is called, the driver releases the structure used to store information about the environment. Note that SQLFreeHandle cannot be called for an environment handle until after all connection handles on that environment handle have been freed.

Declaring the Application’s ODBC Version

Before an application allocates a connection, it must set the SQL_ATTR_ODBC_VERSION environment attribute. This attribute states that the application follows the ODBC 2.x or ODBC 3.x specification when using the following items:

*      SQLSTATEs. Many SQLSTATE values are different in ODBC 2.x and ODBC 3.x.
*      Date, Time, and Timestamp Type Identifiers. The following table shows the type identifiers for date, time, and timestamp data in ODBC 2.x and ODBC 3.x.

The ODBC 3.x Driver Manager and ODBC 3.x drivers check the version of the ODBC specification to which an application is written and respond accordingly. For example, if the application follows the ODBC 2.x specification and calls SQLExecute before calling SQLPrepare, the ODBC 3.x Driver Manager returns SQLSTATE S1010 (Function sequence error). If the application follows the ODBC 3.x specification, the Driver Manager returns SQLSTATE HY010 (Function sequence error).

Choosing a Data Source or Driver

The data source or driver used by an application is sometimes hard-coded in the application. For example, a custom application written by an MIS department to transfer data from one data source to another would contain the names of those data sources—the application simply would not work with any other data sources. Another example is a vertical application, such as one used for order entry. Such an application always uses the same data source, which has a predefined schema known by the application.

Other applications select the data source or driver at run time. Usually, these are generic applications that do ad hoc queries, such as a spreadsheet that uses ODBC to import data. Such applications usually list the available data sources or drivers and let users choose the ones they want to work with. Whether a generic application lists data sources, drivers, or both frequently depends on whether the application uses DBMS-based or file-based drivers.

DBMS-based drivers usually require a complex set of connection information, such as the network address, network protocol, database name, and so on. The purpose of a data source is to hide all of this information. Therefore, the data source paradigm lends itself to use with DBMS-based drivers. An application can display a list of data sources to the user in one of two ways. It can call SQLDriverConnect with the DSN (Data Source Name) keyword and no associated value; the Driver Manager will display a list of data source names. If the application wants control over the appearance of the list, it calls SQLDataSources to retrieve a list of available data sources and constructs its own dialog box. This function is implemented by the Driver Manager and can be called before any drivers are loaded. The application then calls a connection function and passes it the name of the chosen data source.

If a data source is not specified, the default data source indicated by the system information is used. (For more information, see Default Subkey.) If SQLConnect is called by using a ServerName argument that cannot be found, is a null pointer, or is “DEFAULT”, the Driver Manager connects to the default data source. The default data source is also used if the connection string that is used in a call to SQLDriverConnect or SQLBrowseConnect contains the DSN keyword set to “DEFAULT” or if the specified data source is not found. Additionally, the default data source is used if the connection string that is used in a call to SQLDriverConnect does not contain the DSN keyword.

With file-based drivers, it is possible to use a file paradigm. For data stored on the local computer, users frequently know that their data is in a particular file, such as Employee.dbf. Instead of selecting an unknown data source, it is easier for such users to select the file they know. To implement this, the application first calls SQLDrivers. This function is implemented by the Driver Manager and can be called before any drivers are loaded. SQLDrivers returns a list of available drivers; it also returns values for the FileUsage and FileExtns keywords. The FileUsage keyword explains whether file-based drivers treat files as tables, as does Xbase, or as databases, as does Microsoft® Access. The FileExtns keyword lists the file name extensions the driver recognizes, such as .dbf for an Xbase driver. Using this information, the application constructs a dialog box through which the user chooses a file. Based on the extension of the chosen file, the application then connects to the driver by calling SQLDriverConnect with the DRIVER keyword.

There is nothing to stop an application from using a data source with a file-based driver or calling SQLDriverConnect with the DRIVER keyword to connect to a DBMS-based driver. Here are several common uses of the DRIVER keyword for DBMS-based drivers:

*     Not creating data sources. For example, a custom application might use a particular driver and database. If the driver name and all information that is required to connect to the database is hard-coded in the application, users do not have to create a data source on their computer to run the application. All they must do is install the application and driver.

A disadvantage of this method is that the application must be recompiled and redistributed if the connection information changes. If a data source name is hard-coded in the application instead of complete connection information, each user must change only the information in the data source.
*     Accessing a particular DBMS a single time. For example, a spreadsheet that retrieves data by calling ODBC functions might contain the DRIVER keyword to identify a particular driver. Because the driver name is meaningful to any users who have that driver, the spreadsheet could be passed among those users. If the spreadsheet contained a data source name, each user would have to create the same data source to use the spreadsheet.
*    Browsing the system for all databases accessible to a particular driver.

Allocating a Connection Handle ODBC

Before the application can connect to a data source or driver, it must allocate a connection handle, as follows:

1.      The application declares a variable of type SQLHDBC. It then calls SQLAllocHandle and passes the address of this variable, the handle of the environment in which to allocate the connection, and the SQL_HANDLE_DBC option. For example:
Copy Code

SQLHDBC hdbc1;

SQLAllocHandle(SQL_HANDLE_DBC, henv1, &hdbc1);

2.      The Driver Manager allocates a structure in which to store information about the statement and returns the connection handle in the variable.

The Driver Manager does not call SQLAllocHandle in the driver at this time because it does not know which driver to call. It delays calling SQLAllocHandle in the driver until the application calls a function to connect to a data source. For more information, see Driver Manager’s Role in the Connection Process, later in this section.

It is important to note that allocating a connection handle is not the same as loading a driver. The driver is not loaded until a connection function is called. Thus, after allocating a connection handle and before connecting to the driver or data source, the only functions the application can call with the connection handle are SQLSetConnectAttr, SQLGetConnectAttr, or SQLGetInfo with the SQL_ODBC_VER option. Calling other functions with the connection handle, such as SQLEndTran, returns SQLSTATE 08003 (Connection not open).

After allocating environment and connection handles and setting any connection attributes, the application is ready to connect to the data source or driver. There are three different functions the application can use to do this: SQLConnect (Core interface conformance level), SQLDriverConnect (Core), and SQLBrowseConnect (Level 1). Each of the three is designed to be used in a different scenario. Before connecting, the application can determine which of these functions is supported with the ConnectFunctions keyword returned by SQLDrivers.

The driver may select a data source, called the default data source, in certain cases where the application does not explicitly specify one:

*     In a call to SQLConnect where the ServerName argument is a zero-length string, a null pointer, or DEFAULT.
*      In a call to SQLDriverConnect where InConnectionString either specifies DSN=DEFAULT or specifies with the DSN keyword a data source that is not contained in the system information.

It is driver-defined how the default data source is specified. This may involve administrative action and may depend on the user.

SQLConnect is the simplest connection function. It requires a data source name and accepts an optional user ID and password. It works well for applications that hard-code a data source name and do not require a user ID or password. It also works well for applications that want to control their own “look and feel” or that have no user interface. Such applications can build a list of data sources using SQLDataSources, prompt the user for data source, user ID, and password, and then call SQLConnect.

The following example connects to the Northwind database, using a DSN called Northwind, and retrieves all of the first and last name fields from all of the records in the Employees table.

A connection string contains information used for establishing a connection. A complete connection string contains all the information needed to establish a connection. The connection string is a series of keyword/value pairs separated by semicolons. (For the complete syntax of a connection string, see the SQLDriverConnect function description.) The connection string is used by:

*      SQLDriverConnect, which completes the connection string by interaction with the user.
*      SQLBrowseConnect, which completes the connection string iteratively with the data source.

SQLConnect does not use a connection string; using SQLConnect is analogous to connecting using a connection string with exactly three keyword/value pairs (for data source name and, optionally, user ID and password).

Connecting with SQLBrowseConnect

SQLBrowseConnect, like SQLDriverConnect, uses a connection string. However, by using SQLBrowseConnect, an application can construct a complete connection string at run time. This allows the application to do two things:

*      Build its own dialog boxes to prompt for this information, thereby retaining control over its “look and feel.”
*      Browse the system for data sources that can be used by a particular driver, possibly in several steps. For example, the user might first browse the network for servers and, after choosing a server, browse the server for databases accessible by the driver.

The application calls SQLBrowseConnect and passes a connection string, known as the browse request connection string, that specifies a driver or data source. The driver returns a connection string, known as the browse result connection string, that contains keywords, possible values (if the keyword accepts a discrete set of values), and user-friendly names. The application builds a dialog box with the user-friendly names and prompts the user for values. It then builds a new browse request connection string from these values and returns this to the driver with another call to SQLBrowseConnect.

Because connection strings are passed back and forth, the driver can provide several levels of browsing by returning a new connection string when the application returns the old one. For example, the first time an application calls SQLBrowseConnect, the driver might return keywords to prompt the user for a server name. When the application returns the server name, the driver might return keywords to prompt the user for a database. The browsing process would be complete after the application returned the database name.

Each time SQLBrowseConnect returns a new browse result connection string, it returns SQL_NEED_DATA as its return code. This tells the application that the connection process is not complete. Until SQLBrowseConnect returns SQL_SUCCESS, the connection is in a Need Data state and cannot be used for other purposes, such as to set a connection attribute. The application can terminate the connection browsing process by calling SQLDisconnect.

Connection Pooling

This section discusses how an application can use a connection from a pool of connections that do not need to be reestablished for each use.

Regardless of how an application performs connection pooling, if an application uses ODBC connection pooling, it should call CoInitializeEx and CoUninitialize when a thread connects and disconnects from a server.

Disconnecting from a Data Source or Driver

When an application has finished using a data source, it calls SQLDisconnect. SQLDisconnect frees any statements that are allocated on the connection and disconnects the driver from the data source. It returns an error if a transaction is in process.

After disconnecting, the application can call SQLFreeHandle to free the connection. After freeing the connection, it is an application programming error to use the connection’s handle in a call to an ODBC function; doing so has undefined but probably fatal consequences. When SQLFreeHandle is called, the driver releases the structure used to store information about the connection.

The application also can reuse the connection, either to connect to a different data source or reconnect to the same data source. The decision to remain connected, as opposed to disconnecting and reconnecting later, requires that the application writer consider the relative costs of each option; both connecting to a data source and remaining connected can be relatively costly depending on the connection medium. In making a correct tradeoff, the application must also make assumptions about the likelihood and timing of further operations on the same data source.

Disconnecting from a Data Source or Driver

When an application has finished using a data source, it calls SQLDisconnect. SQLDisconnect frees any statements that are allocated on the connection and disconnects the driver from the data source. It returns an error if a transaction is in process.

After disconnecting, the application can call SQLFreeHandle to free the connection. After freeing the connection, it is an application programming error to use the connection’s handle in a call to an ODBC function; doing so has undefined but probably fatal consequences. When SQLFreeHandle is called, the driver releases the structure used to store information about the connection.

The application also can reuse the connection, either to connect to a different data source or reconnect to the same data source. The decision to remain connected, as opposed to disconnecting and reconnecting later, requires that the application writer consider the relative costs of each option; both connecting to a data source and remaining connected can be relatively costly depending on the connection medium. In making a correct tradeoff, the application must also make assumptions about the likelihood and timing of further operations on the same data source.

Driver Manager’s Role in the Connection Process

Remember that applications do not call driver functions directly. Instead, they call Driver Manager functions with the same name and the Driver Manager calls the driver functions. Usually, this happens almost immediately. For example, the application calls SQLExecute in the Driver Manager and after a few error checks, the Driver Manager calls SQLExecute in the driver.

The connection process is different. When the application calls SQLAllocHandle with the SQL_HANDLE_ENV and SQL_HANDLE_DBC options, the function allocates handles only in the Driver Manager. The Driver Manager does not call this function in the driver because it does not know which driver to call. Similarly, if the application passes the handle of an unconnected connection to SQLSetConnectAttr or SQLGetConnectAttr, only the Driver Manager executes the function. It stores or gets the attribute value from its connection handle and returns SQLSTATE 08003 (Connection not open) when getting a value for an attribute that has not been set and for which ODBC does not define a default value.

When the application calls SQLConnect, SQLDriverConnect, or SQLBrowseConnect, the Driver Manager first determines which driver to use. It then checks to determine whether a driver is currently loaded on the connection:

*     If no driver is loaded on the connection, the Driver Manager checks whether the specified driver is loaded on another connection in the same environment. If not, the Driver Manager loads the driver on the connection and calls SQLAllocHandle in the driver with the SQL_HANDLE_ENV option.

The Driver Manager then calls SQLAllocHandle in the driver with the SQL_HANDLE_DBC option, whether or not it was just loaded. If the application set any connection attributes, the Driver Manager calls SQLSetConnectAttr in the driver; if an error occurs, the Driver Manager’s connection function returns SQLSTATE IM006 (Driver’s SQLSetConnectAttr failed). Finally, the Driver Manager calls the connection function in the driver.
*      If the specified driver is loaded on the connection, the Driver Manager calls only the connection function in the driver. In this case, the driver must make sure that all connection attributes on the connection maintain their current settings.
*      If a different driver is loaded on the connection, the Driver Manager calls SQLFreeHandle in the driver to free the connection. If there are no other connections that use the driver, the Driver Manager calls SQLFreeHandle in the driver to free the environment and unloads the driver. The Driver Manager then performs the same operations as when a driver is not loaded on the connection.

When the application calls SQLDisconnect, the Driver Manager calls SQLDisconnect in the driver. However, it leaves the driver loaded in case the application reconnects to the driver. When the application calls SQLFreeHandle with the SQL_HANDLE_DBC option, the Driver Manager calls SQLFreeHandle in the driver. If the driver is not used by any other connections, the Driver Manager then calls SQLFreeHandle in the driver with the SQL_HANDLE_ENV option and unloads the driver.