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.

A transaction is a unit of work that is done as a single,atomic operation; that is, the operation succeeds or fails as a whole. For example, consider transferring money from one bank account to another. This involves two steps: withdrawing the money from the first account and depositing it in the second. It is important that both steps succeed; it is not acceptable for one step to succeed and the other to fail. A database that supports transactions is able to guarantee this.

Transactions can be completed by either being committed or being rolled back. When a transaction is committed, the changes made in that transaction are made permanent. When a transaction is rolled back, the affected rows are returned to the state they were in before the transaction was started. To extend the account transfer example, an application executes one SQL statement to debit the first account and a different SQL statement to credit the second account. If both statements succeed, the application then commits the transaction. But if either statement fails for any reason, the application rolls back the transaction. In either case, the application guarantees a consistent state at the end of the transaction.

A single transaction can encompass multiple database operations that occur at different times. If other transactions had complete access to the intermediate results, the transactions might interfere with one another. For example, suppose one transaction inserts a row, a second transaction reads that row, and the first transaction is rolled back. The second transaction now has data for a row that does not exist.

To solve this problem, there are various schemes to isolate transactions from one another. Transaction isolation is generally implemented by locking rows, which precludes more than one transaction from using the same row at the same time. In some databases, locking a row may also lock other rows.

Step 1: Connect to the Data Source

The first step in any application is to connect to the data source. This phase, including the functions it requires, is shown in the following illustration.

The first step in connecting to the data source is to load the Driver Manager and allocate the environment handle with SQLAllocHandle. For more information, see Allocating the Environment Handle.

The application then registers the version of ODBC to which it conforms by calling SQLSetEnvAttr with the SQL_ATTR_APP_ODBC_VER environment attribute. For more information, see Declaring the Application’s ODBC Version and Backward Compatibility and Standards Compliance.

Next, the application allocates a connection handle with SQLAllocHandle and connects to the data source with SQLConnect, SQLDriverConnect, or SQLBrowseConnect. For more information, see Allocating a Connection Handle and Establishing a Connection.

The application then sets any connection attributes, such as whether to manually commit transactions.

Step 2: Initialize the Application

The second step is to initialize the application, as shown in the following illustration. Exactly what is done here varies with the application.

At this point, it is common to use SQLGetInfo to discover the capabilities of the driver. For more information, see Considering Database Features to Use.

All applications need to allocate a statement handle with SQLAllocHandle, and many applications set statement attributes, such as the cursor type, with SQLSetStmtAttr.

Step 3: Build and Execute an SQL Statement

The third step is to build and execute an SQL statement, as shown in the following illustration. The methods used to perform this step are likely to vary tremendously. The application might prompt the user to enter an SQL statement, build an SQL statement based on user input, or use a hard-coded SQL statement.

If the SQL statement contains parameters, the application binds them to application variables by calling SQLBindParameter for each parameter. For more information, see Statement Parameters.

After the SQL statement is built and any parameters are bound, the statement is executed with SQLExecDirect. If the statement will be executed multiple times, it can be prepared with SQLPrepare and executed with SQLExecute. For more information, see Executing a Statement.

The application might also forgo executing an SQL statement altogether and instead call a function to return a result set containing catalog information, such as the available columns or tables. For more information, see Uses of Catalog Data.

The application’s next action depends on the type of SQL statement executed.

Step 4a: Fetch the Results

The next step is to fetch the results, as shown in the following illustration.

If the statement executed in “Step 3: Build and Execute an SQL Statement” was a SELECT statement or a catalog function, the application first calls SQLNumResultCols to determine the number of columns in the result set. This step is not necessary if the application already knows the number of result set columns, such as when the SQL statement is hard-coded in a vertical or custom application.

Next, the application retrieves the name, data type, precision, and scale of each result set column with SQLDescribeCol. Again, this is not necessary for applications such as vertical and custom applications that already know this information. The application passes this information to SQLBindCol, which binds an application variable to a column in the result set.

The application now calls SQLFetch to retrieve the first row of data and place the data from that row in the variables bound with SQLBindCol. If there is any long data in the row, it then calls SQLGetData to retrieve that data. The application continues to call SQLFetch and SQLGetData to retrieve additional data. After it has finished fetching data, it calls SQLCloseCursor to close the cursor.

For a complete description of retrieving results, see Retrieving Results (Basic) and Retrieving Results (Advanced).

The application now returns to “Step 3: Build and Execute an SQL Statement” to execute another statement in the same transaction; or proceeds to “Step 5: Commit the Transaction” to commit or roll back the transaction.

Step 4b: Fetch the Row Count

The next step is to fetch the row count, as shown in the following illustration.

If the statement executed in Step 3 was an UPDATE, DELETE, or INSERT statement, the application retrieves the count of affected rows with SQLRowCount. For more information, see Determining the Number of Affected Rows.

The application now returns to step 3 to execute another statement in the same transaction or proceeds to step 5 to commit or roll back the transaction.

Step 5: Commit the Transaction

The next step is to commit the transaction, as shown in the following illustration.

The fifth step is to call SQLEndTran to commit or roll back the transaction. The application performs this step only if it set the transaction commit mode to manual-commit; if the transaction commit mode is auto-commit, which is the default, the transaction is automatically committed when the statement is executed. For more information, see Transactions.

To execute a statement in a new transaction, the application returns to step 3. To disconnect from the data source, the application proceeds to step 6.

Step 6: Disconnect from the Data Source

The final step is to disconnect from the data source, as shown in the following illustration. First, the application frees any statement handles by calling SQLFreeHandle.

Next, the application disconnects from the data source with SQLDisconnect and frees the connection handle with SQLFreeHandle. For more information, see Disconnecting from a Data Source or Driver.

Finally, the application frees the environment handle with SQLFreeHandle and unloads the Driver Manager.

On computers running Microsoft® Windows NT® Server/Windows 2000 Server, Microsoft Windows NT Workstation/Windows 2000 Professional, or Microsoft Windows® 95/98, machine data source information is stored in the registry. Depending on which registry key the information is stored under, the data source is known as a user data source or a system data source. User data sources are stored under the HKEY_CURRENT_USER key and are available only to the current user. System data sources are stored under the HKEY_LOCAL_MACHINE key and can be used by more than one user on one machine. They can also be used by systemwide services, which can then gain access to the data source even if no user is logged on to the machine. For more information about user and system data sources, see SQLManageDataSources.

Suppose a user has three user data sources: Personnel and Inventory, which use an Oracle DBMS; and Payroll, which uses a Microsoft SQL Server DBMS. The registry values for data sources might be:
Copy Code

HKEY_CURRENT_USER
SOFTWARE
ODBC
Odbc.ini
ODBC Data Sources
Personnel : REG_SZ : Oracle
Inventory : REG_SZ : Oracle
Payroll : REG_SZ : SQL Server

and the registry values for the Payroll data source might be:
Copy Code

HKEY_CURRENT_USER
SOFTWARE
ODBC
Odbc.ini
Payroll
Driver : REG_SZ : C:\WINDOWS\SYSTEM\Sqlsrvr.dll
Description : REG_SZ : Payroll database
Server : REG_SZ : PYRLL1
FastConnectOption : REG_SZ : No                          UseProcForPrepare : REG_SZ : Yes
OEMTOANSI : REG_SZ : No
LastUser : REG_SZ : smithjo
Database : REG_SZ : Payroll
Language : REG_SZ :

Data sources usually are created by the end user or a technician with a program called the ODBC Administrator. The ODBC Administrator prompts the user for the driver to use and then calls that driver. The driver displays a dialog box that requests the information it needs to connect to the data source. After the user enters the information, the driver stores it on the system.

Later, the application calls the Driver Manager and passes it the name of a machine data source or the path of a file containing a file data source. When passed a machine data source name, the Driver Manager searches the system to find the driver used by the data source. It then loads the driver and passes the data source name to it. The driver uses the data source name to find the information it needs to connect to the data source. Finally, it connects to the data source, typically prompting the user for a user ID and password, which generally are not stored.

When passed a file data source, the Driver Manager opens the file and loads the specified driver. If the file also contains a connection string, it passes this to the driver. Using the information in the connection string, the driver connects to the data source. If no connection string was passed, the driver generally prompts the user for the necessary information.

Machine data sources are stored on the system with a user-defined name. Associated with the data source name is all of the information the Driver Manager and driver need to connect to the data source. For an Xbase data source, this might be the name of the Xbase driver, the full path of the directory containing the Xbase files, and some options that tell the driver how to use those files, such as single-user mode or read-only. For an Oracle data source, this might be the name of the Oracle driver, the server where the Oracle DBMS resides, the SQL*Net connection string that identifies the SQL*Net driver to use, and the system ID of the database on the server.

File data sources are stored in a file and allow connection information to be used repeatedly by a single user or shared among several users. When a file data source is used, the Driver Manager makes the connection to the data source using the information in a .dsn file. This file can be manipulated like any other file. A file data source does not have a data source name, as does a machine data source, and is not registered to any one user or machine.

A file data source streamlines the connection process, because the .dsn file contains the connection string that would otherwise have to be built for a call to the SQLDriverConnect function. Another advantage of the .dsn file is that it can be copied to any machine, so identical data sources can be used by many machines as long as they have the appropriate driver installed. A file data source can also be shared by applications. A shareable file data source can be placed on a network and used simultaneously by multiple applications.

A .dsn file can also be unshareable. An unshareable .dsn file resides on a single machine and points to a machine data source. Unshareable file data sources exist mainly to allow the easy conversion of machine data sources to file data sources so that an application can be designed to work solely with file data sources. When the Driver Manager is sent the information in an unshareable file data source, it connects as necessary to the machine data source that the .dsn file points to.

File Based Driver

File-based drivers are used with data sources such as dBASE that do not provide a stand-alone database engine for the driver to use. These drivers access the physical data directly and must implement a database engine to process SQL statements. As a standard practice, the database engines in file-based drivers implement the subset of ODBC SQL defined by the minimum SQL conformance level; for a list of the SQL statements in this conformance level, see Appendix C: SQL Grammar.

In comparing file-based and DBMS-based drivers, file-based drivers are harder to write because of the database engine component, less complicated to configure because there are no network pieces, and less powerful because few people have the time to write database engines as powerful as those produced by database companies.

The following illustration shows two different configurations of file-based drivers, one in which the data resides locally and the other in which it resides on a network file server.

DMBS-Based Drivers

DBMS-based drivers are used with data sources such as Oracle or SQL Server that provide a stand-alone database engine for the driver to use. These drivers access the physical data through the stand-alone engine; that is, they submit SQL statements to and retrieve results from the engine.

Because DBMS-based drivers use an existing database engine, they are usually easier to write than file-based drivers. Although a DBMS-based driver can be easily implemented by translating ODBC calls to native API calls, this results in a slower driver. A better way to implement a DBMS-based driver is to use the underlying data stream protocol, which is usually what the native API does. For example, a SQL Server driver should use TDS (the data stream protocol for SQL Server) rather than DB Library (the native API for SQL Server). An exception to this rule is when ODBC is the native API. For example, Watcom SQL is a stand-alone engine that resides on the same machine as the application and is loaded directly as the driver.

DBMS-based drivers act as the client in a client/server configuration where the data source acts as the server. In most cases, the client (driver) and server (data source) reside on different machines, although both could reside on the same machine running a multitasking operating system. A third possibility is a gateway, which sits between the driver and data source. A gateway is a piece of software that causes one DBMS to look like another. For example, applications written to use SQL Server can also access DB2 data through the Micro Decisionware DB2 Gateway; this product causes DB2 to look like SQL Server.

The following illustration shows three different configurations of DBMS-based drivers. In the first configuration, the driver and data source reside on the same machine. In the second, the driver and data source reside on different machines. In the third, the driver and data source reside on different machines and a gateway sits between them, residing on yet another machine.

Network Architecture ODBC drivers

Driver as a Middle Component

Heterogeneous Join Engines

Specific tasks performed by drivers include:

• Connecting to and disconnecting from the data source.
• Checking for function errors not checked by the Driver Manager.
• Initiating transactions; this is transparent to the application.
• Submitting SQL statements to the data source for execution. The driver must modify ODBC SQL to DBMS-specific SQL; this is often limited to replacing escape clauses defined by ODBC with DBMS-specific SQL.
• Sending data to and retrieving data from the data source, including converting data types as specified by the application.
• Mapping DBMS-specific errors to ODBC SQLSTATEs.

The Driver Manager is a library that manages communication between applications and drivers. For example, on Microsoft® Windows® platforms, the Driver Manager is a dynamic-link library (DLL) that is written by Microsoft and can be redistributed by users of the redistributable MDAC 2.8 SP1 SDK.

The Driver Manager exists mainly as a convenience to application writers and solves a number of problems common to all applications. These include determining which driver to load based on a data source name, loading and unloading drivers, and calling functions in drivers.

To see why the latter is a problem, consider what would happen if the application called functions in the driver directly. Unless the application was linked directly to a particular driver, it would have to build a table of pointers to the functions in that driver and call those functions by pointer. Using the same code for more than one driver at a time would add yet another level of complexity. The application would first have to set a function pointer to point to the correct function in the correct driver, and then call the function through that pointer.

The Driver Manager solves this problem by providing a single place to call each function. The application is linked to the Driver Manager and calls ODBC functions in the Driver Manager, not the driver. The application identifies the target driver and data source with a connection handle. When it loads a driver, the Driver Manager builds a table of pointers to the functions in that driver. It uses the connection handle passed by the application to find the address of the function in the target driver and calls that function by address.

For the most part, the Driver Manager just passes function calls from the application to the correct driver. However, it also implements some functions (SQLDataSources, SQLDrivers, and SQLGetFunctions) and performs basic error checking. For example, the Driver Manager checks that handles are not null pointers, that functions are called in the correct order, and that certain function arguments are valid. For a complete description of the errors checked by the Driver Manager, see the reference section for each function and Appendix B: ODBC State Transition Tables.

The final major role of the Driver Manager is loading and unloading drivers. The application loads and unloads only the Driver Manager. When it wants to use a particular driver, it calls a connection function (SQLConnect, SQLDriverConnect, or SQLBrowseConnect) in the Driver Manager and specifies the name of a particular data source or driver, such as “Accounting” or “SQL Server.” Using this name, the Driver Manager searches the data source information for the driver’s file name, such as Sqlsrvr.dll. It then loads the driver (assuming it is not already loaded), stores the address of each function in the driver, and calls the connection function in the driver, which then initializes itself and connects to the data source.

When the application is done using the driver, it calls SQLDisconnect in the Driver Manager. The Driver Manager calls this function in the driver, which disconnects from the data source. However, the Driver Manager keeps the driver in memory in case the application reconnects to it. It unloads the driver only when the application frees the connection used by the driver or uses the connection for a different driver, and no other connections use the driver. For a complete description of the Driver Manager’s role in loading and unloading drivers

An application is a program that calls the ODBC API to access data. Although many types of applications are possible, most fall into three categories, which are used as examples throughout this guide.

* Generic Applications These are also referred to as shrink-wrapped applications or off-the-shelf applications. Generic applications are designed to work with a variety of different DBMSs. Examples include a spreadsheet or statistics package that uses ODBC to import data for further analysis and a word processor that uses ODBC to get a mailing list from a database.
An important subcategory of generic applications is application development environments, such as PowerBuilder or Microsoft® Visual Basic®. Although the applications constructed with these environments will probably work only with a single DBMS, the environment itself needs to work with multiple DBMSs.
What all generic applications have in common is that they are highly interoperable among DBMSs and they need to use ODBC in a relatively generic manner. For more information about interoperability, see Choosing a Level of Interoperability.

* Vertical Applications Vertical applications perform a single type of task, such as order entry or tracking manufacturing data, and work with a database schema that is controlled by the developer of the application. For a particular customer, the application works with a single DBMS. For example, a small business might use the application with dBase, while a large business might use it with Oracle.
The application uses ODBC in such a manner that the application is not tied to any one DBMS, although it might be tied to a limited number of DBMSs that provide similar functionality. Thus, the application developer can sell the application independently from the DBMS. Vertical applications are interoperable when they are developed but are sometimes modified to include noninteroperable code once the customer has chosen a DBMS.

* Custom Applications Custom applications are used to perform a specific task in a single company. For example, an application in a large company might gather sales data from several divisions (each of which uses a different DBMS) and create a single report. ODBC is used because it is a common interface and saves programmers from having to learn multiple interfaces. Such applications are generally not interoperable and are written to specific DBMSs and drivers.
A number of tasks are common to all applications, no matter how they use ODBC. Taken together, they largely define the flow of any ODBC application. The tasks are:

* Selecting a data source and connecting to it.
* Submitting an SQL statement for execution.
* Retrieving results (if any).
* Processing errors.
* Committing or rolling back the transaction enclosing the SQL statement.
* Disconnecting from the data source.

Because most data access work is done with SQL, the primary task for which applications use ODBC is to submit SQL statements and retrieve the results (if any) generated by those statements. Other tasks for which applications use ODBC include determining and adjusting to driver capabilities and browsing the database catalog.