ODBC Driver

ODBC aligns with the following specifications and standards that deal with the Call-Level Interface (CLI). (The ODBC features are a superset of each of these standards.)

* The Open Group CAE Specification “Data Management: SQL Call-Level Interface (CLI)”
* ISO/IEC 9075-3:1995 (E) Call-Level Interface (SQL/CLI)

As a result of this alignment, the following are true:

* An application written to the Open Group and ISO CLI specifications will work with an ODBC 3.x driver or a standards-compliant driver when it is compiled with the ODBC 3.x header files and linked with ODBC 3.x libraries, and when it gains access to the driver through the ODBC 3.x Driver Manager.
* A driver written to the Open Group and ISO CLI specifications will work with an ODBC 3.x application or a standards-compliant application when it is compiled with the ODBC 3.x header files and linked with ODBC 3.x libraries, and when the application gains access to the driver through the ODBC 3.x Driver Manager. (For more information, see Standards-Compliant Applications and Drivers.

The Core interface conformance level encompasses all the features in the ISO CLI and all the nonoptional features in the Open Group CLI. Optional features of the Open Group CLI appear in higher interface conformance levels. Because all ODBC 3.x drivers are required to support the features in the Core interface conformance level, the following are true:

* An ODBC 3.x driver will support all the features used by a standards-compliant application.
* An ODBC 3.x application using only the features in ISO CLI and the nonoptional features of the Open Group CLI will work with any standards-compliant driver.

In addition to the call-level interface specifications contained in the ISO/IEC and Open Group CLI standards, ODBC implements the following features. (Some of these features existed in versions of ODBC prior to ODBC 3.x.)

* Multirow fetches by a single function call
* Binding to an array of parameters
* Bookmark support including fetching by bookmark, variable-length bookmarks, and bulk update and delete by bookmark operations on discontiguous rows
* Row-wise binding
* Binding offsets
* Support for batches of SQL statements, either in a stored procedure or as a sequence of SQL statements executed through SQLExecute or SQLExecDirect
* Exact or approximate cursor row counts
* Positioned update and delete operations and batched updates and deletes by function call (SQLSetPos)
* Catalog functions that extract information from the information schema without the need for supporting information schema views
* Escape sequences for outer joins, scalar functions, datetime literals, interval literals, and stored procedures
* Code-page translation libraries
* Reporting of a driver’s ANSI-conformance level and SQL support
* On-demand automatic population of implementation parameter descriptor
* Enhanced diagnostics and row and parameter status arrays
* Datetime, interval, numeric/decimal, and 64-bit integer application buffer types
* Asynchronous execution
* Stored procedure support, including escape sequences, output parameter binding mechanisms, and catalog functions
* Connection enhancements including support for connection attributes and attribute browsing

Many misconceptions about ODBC exist in the computing world. To the end user, it is an icon in the Microsoft® Windows® Control Panel. To the application programmer, it is a library containing data access routines. To many others, it is the answer to all database access problems ever imagined.

First and foremost, ODBC is a specification for a database API. This API is independent of any one DBMS or operating system; although this manual uses C, the ODBC API is language-independent. The ODBC API is based on the CLI specifications from Open Group and ISO/IEC. ODBC 3.x fully implements both of these specifications — earlier versions of ODBC were based on preliminary versions of these specifications but did not fully implement them — and adds features commonly needed by developers of screen-based database applications, such as scrollable cursors.

The functions in the ODBC API are implemented by developers of DBMS-specific drivers. Applications call the functions in these drivers to access data in a DBMS-independent manner. A Driver Manager manages communication between applications and drivers.

Although Microsoft provides a driver manager for computers running Microsoft Windows® 95 and later, has written several ODBC drivers, and calls ODBC functions from some of its applications, anyone can write ODBC applications and drivers. In fact, the vast majority of ODBC applications and drivers available today are written by companies other than Microsoft. Furthermore, ODBC drivers and applications exist on the Macintosh® and a variety of UNIX platforms.

To help application and driver developers, Microsoft offers an ODBC Software Development Kit (SDK) for computers running Windows 95 and later that provides the driver manager, installer DLL, test tools, and sample applications. Microsoft has teamed with Visigenic Software to port these SDKs to the Macintosh and a variety of UNIX platforms.

It is important to understand that ODBC is designed to expose database capabilities, not supplement them. Thus, application writers should not expect that using ODBC will suddenly transform a simple database into a fully featured relational database engine. Nor are driver writers expected to implement functionality not found in the underlying database. An exception to this is that developers who write drivers that directly access file data (such as data in an Xbase file) are required to write a database engine that supports at least minimal SQL functionality. Another exception is that the ODBC component of the Windows SDK, formerly included in the Microsoft Data Access Components (MDAC) SDK, provides a cursor library that simulates scrollable cursors for drivers that implement a certain level of functionality.

Applications that use ODBC are responsible for any cross-database functionality. For example, ODBC is not a heterogeneous join engine, nor is it a distributed transaction processor. However, because it is DBMS-independent, it can be used to build such cross-database tools.

Historically, companies used a single DBMS. All database access was done either through the front end of that system or through applications written to work exclusively with that system. However, as the use of computers grew and more computer hardware and software became available, companies started to acquire different DBMSs. The reasons were many: People bought what was cheapest, what was fastest, what they already knew, what was latest on the market, what worked best for a single application. Other reasons were reorganizations and mergers, where departments that previously had a single DBMS now had several.

The issue grew even more complex with the advent of personal computers. These computers brought in a host of tools for querying, analyzing, and displaying data, along with a number of inexpensive, easy-to-use databases. From then on, a single corporation often had data scattered across a myriad of desktops, servers, and minicomputers, stored in a variety of incompatible databases, and accessed by a vast number of different tools, few of which could get at all of the data.

The final challenge came with the advent of client/server computing, which seeks to make the most efficient use of computer resources. Inexpensive personal computers (the clients) sit on the desktop and provide both a graphical front end to the data and a number of inexpensive tools, such as spreadsheets, charting programs, and report builders. Minicomputers and mainframe computers (the servers) host the DBMSs, where they can use their computing power and central location to provide quick, coordinated data access. How then was the front-end software to be connected to the back-end databases?

A similar problem faced independent software vendors (ISVs). Vendors writing database software for minicomputers and mainframes were usually forced to write one version of an application for each DBMS or write DBMS-specific code for each DBMS they wanted to access. Vendors writing software for personal computers had to write data access routines for each different DBMS with which they wanted to work. This often meant a huge amount of resources were spent writing and maintaining data access routines rather than applications, and applications were often sold not on their quality but on whether they could access data in a given DBMS.

What both sets of developers needed was a way to access data in different DBMSs. The mainframe and minicomputer group needed a way to merge data from different DBMSs in a single application, while the personal computer group needed this ability as well as a way to write a single application that was independent of any one DBMS. In short, both groups needed an interoperable way to access data; they needed open database connectivity.

This tab displays information about the ODBC core components, including the Driver Manager, the cursor library, the installer .dll file, and other files that constitute the core components.

The table below describes the controls on the About tab.

Core component list Displays the description, version, file name, and location of each ODBC core component.

OK Closes the ODBC Data Source Administrator dialog box.

Cancel Closes the ODBC Data Source Administrator dialog box but does not undo changes made in other dialog boxes.

Help Displays this Help topic.

Connection pooling enables an ODBC application to reuse a connection from a pool of connections. Use this tab to alter the connection retry wait time and time-out period for a selected driver when using connection pooling. You can also enable and disable performance monitoring, which records a number of connection statistics.

The table below describes the controls on the Connection Pooling tab.

ODBC Drivers Lists the name of each installed ODBC driver with its connection pooling time-out option. To set the time-out option, double-click the ODBC driver name.

Connection Pooling Timeout Sets the connection pooling time-out, in seconds, for the selected driver. To set connection pooling attributes, double-click the driver name.

Enable Enables performance-monitoring counters for connection pooling. For more information about performance monitoring, go to Setting ODBC Connection Pooling Options on the Microsoft website.

Disable Disables performance-monitoring counters for connection pooling. For more information about performance monitoring, go to Setting ODBC Connection Pooling Options on the Microsoft website.

Retry Wait Time Specifies, in seconds and in fewer than six numerals, how long the ODBC Driver Manager waits before making connection retries to a database server.

OK Accepts changes to connection pooling settings and closes the ODBC Data Source Administrator dialog box.

Cancel Closes the ODBC Data Source Administrator dialog box without accepting changes to settings.

Apply Accepts changes to connection pooling settings and leaves the ODBC Data Source Administrator dialog box open.

Help Displays this Help topic.

This tab specifies how the ODBC Driver Manager traces calls to ODBC functions. Tracing is performed by a trace DLL that captures calls between the Driver Manager and either the application or the driver, and then records them in a log file. The Driver Manager can:
•    Trace calls continuously or for one connection only
•    Perform tracing dynamically
•    Allow tracing to be performed by a custom trace .dll file

The table below describes the controls on the Tracing tab.

Start Tracing Now/Stop Tracing Now (toggle)
Enables continuous dynamic tracing, whether or not a connection has been made, as long as the ODBC Data Source Administrator dialog box is displayed or until you click Stop Tracing Now.

Start Visual Studio Analyzer/Stop Visual Studio Analyzer (toggle)
Enables Visual Studio Analyzer, which remains enabled until you click Stop Visual Studio Analyzer. Visual Studio Analyzer is a tool that you can use for debugging and analyzing a distributed application. For more information about Visual Studio Analyzer, go to the MSDN Library on the Microsoft website.

Log File Path
Displays the path and file name where the tracing information will be stored. Use the default path and file name (\sql.log) or specify a new one.

Browse
Lets you select the path and file name for the log file by browsing folders.

Custom Trace DLL
Lets you select a .dll file other than Odbctrac.dll to perform tracing. Enter the path and file name of the custom .dll file, or click Select DLL to browse folders. The Odbctrac.dll file that is shipped with the Microsoft Data Access Components (MDAC) software development kit (SDK) can be replaced with a custom .dll file of your choice.

Select DLL
Lets you browse the folder structure for a custom trace .dll file. The path and file name of the chosen .dll file will appear in the Custom Trace DLL text box.

OK
Accepts changes to the Log file path and Custom Trace DLL settings and closes the ODBC Data Source Administrator dialog box.

Cancel
Closes the ODBC Data Source Administrator dialog box without accepting changes to settings.

Apply
Accepts changes to tracing settings and leaves the ODBC Data Source Administrator dialog box open.

Help
Displays this Help topic.

This tab displays information about the ODBC drivers installed on your computer. No action can be performed from this tab. You must use the specific driver setup program to add or remove ODBC drivers.

The table below describes the controls on the Drivers tab.

ODBC Drivers Displays the name, version, company, file name, and file creation date of each ODBC driver on the computer.

OK Closes the ODBC Data Source Administrator dialog box.

Cancel Closes the ODBC Data Source Administrator dialog box.

Help Displays this Help topic.

Use this tab to add, delete, or set up file-based data sources that can be shared among all users who have the same drivers installed. These data sources don’t need to be user-dedicated or local to a computer.

The table below describes the controls on the File DSN tab.

File Data Sources Displays all file data source names (DSNs) and subfolders of the folders indicated in the Look in box. Double-clicking a file DSN displays the driver-specific data source setup dialog box.

Add Adds a new file data source.

Remove Removes a selected file data source.

Configure Displays the driver-specific data source setup dialog box, where you can change the configuration of an existing file data source. You must select the name of a file data source from the list before clicking Configure.

Set Directory Sets the folder indicated in the Look in box as the default when the ODBC Administrator is opened.

Look in Displays the folder for which the subfolders and file DSNs are listed in the window below. The default folder that is initially displayed when the ODBC Administrator is first opened is contained in the system information, but you can change it with the Set Directory button.

Up One Level icon Replaces the folder shown in the Look in box with the folder located one level up.

OK Closes the ODBC Data Source Administrator dialog box. You do not have to click OK to accept changes to the File Data Sources list. Changes to the list are accepted when you click OK in the data source setup dialog box.

Cancel Closes the ODBC Data Source Administrator dialog box but does not undo changes made using other dialog box controls.

Help Displays this Help topic.

Use this tab to add, delete, or set up data sources with system data source names (DSNs). These data sources are local to a computer but not user-dedicated. Any user with the correct permissions can access a system DSN.

The table below describes the controls on the System DSN tab.

System Data Sources Lists all system DSNs, including the name and associated driver of each. Double-clicking a system DSN displays the driver-specific data source setup dialog box.

Add Adds a new system data source.

Remove Removes a selected system data source name from the System Data Sources list.

Configure Displays the driver-specific data source setup dialog box, where you can change the configuration of an existing system data source. You must select the name of a system data source from the list before clicking Configure.

OK Closes the ODBC Data Source Administrator dialog box. You do not have to click OK to accept changes to the System Data Sources list. Changes to the list are accepted when you click OK in the data source setup dialog box.

Cancel Closes the ODBC Data Source Administrator dialog box but does not undo changes made in other dialog boxes.

Help Displays this Help topic.

Use this tab to add, delete, or set up data sources with user data source names (DSNs). These data sources are local to a computer and accessible only by the current user.

Below describes the controls on the User DSN tab.

User Data Sources Lists all user DSNs, including the name and associated driver of each. Double-clicking a user DSN displays the driver-specific data source setup dialog box.

Add Adds new user data sources.

Remove Removes a selected user data source name from the User Data Sources list.

Configure Displays the driver-specific data source setup dialog box, where you can change the configuration of a selected user data source.

OK Closes the ODBC Data Source Administrator dialog box. You do not have to click OK to accept changes to the User Data Sources list. Changes to the list are accepted when you click OK in the data source setup dialog box.

Cancel Closes the ODBC Data Source Administrator dialog box but does not undo changes made in other dialog boxes.

Help Displays this Help topic.