ODBC Driver Basics

What is ODBC?

The Open Database Connectivity (ODBC) interface by Microsoft allows applications to access data in database management systems (DBMS) using SQL as a standard for accessing the data. ODBC permits maximum interoperability, which means a single application can access different DBMS. Application end users can then add ODBC database drivers to link the application to their choice of DBMS.

The ODBC interface defines:

  • A library of ODBC function calls of two types:
    • Core functions that are based on the X/Open and SQL Access Group Call
    • Level Interface specification
    • Extended functions that support additional functionality, including scrollable cursors
  • SQL syntax based on the X/Open and SQL Access Group SQL CAE specification (1992)
  • A standard set of error codes
  • A standard way to connect and logon to a DBMS
  • A standard representation for data types

The ODBC solution for accessing data led to ODBC database drivers, which are dynamic-link libraries on Windows and shared objects on Linux/UNIX. These drivers allow an application to gain access to one or more data sources. ODBC provides a standard interface to allow application developers and vendors of database drivers to exchange data between applications and data sources.

How Does ODBC Work?

The ODBC architecture has four components:

  • Application, which processes and calls ODBC functions to submit SQL statements and retrieve results
  • Driver Manager, which loads drivers for the application
  • Driver, which processes ODBC function calls, submits SQL requests to a specific data source, and returns results to the application
  • Data source, which consists of the data to access and its associated operating system, DBMS, and network platform (if any) used to access the DBMS

The following figure shows the relationship among the four components:

Read about Windows ODBC Drivers

Why Do Application Developers Need ODBC?

Using ODBC, an application developer can develop, compile, and ship an application without targeting a specific DBMS. In this scenario, the application developer does not need to use embedded SQL; therefore, he does not need to recompile the application for each new environment.

What's the Difference Between DataDirect Technologies Implementation of this Technology and Other Alternatives?

You have two choices when deciding how to implement data access:

  • Custom code to the database's native interface, such as Oracle's OCI (Oracle Call Interface)
  • Use third-party ODBC drivers such as DataDirect Technologies wire protocol ODBC drivers

Custom coding data access to the native proprietary interface of each database results in non-portable code. This approach requires you to re-program your application every time you change databases and even database versions. This makes it difficult to maintain the application and is not very cost-effective. And, compared to using wire protocol ODBC drivers, there is not even a performance advantage. See Ken North's report, which benchmarks the performance of SQL queries using wire protocol ODBC and Oracle's OCI.

DataDirect Technologies ODBC drivers provide the following advantages over other ODBC drivers:

  • DataDirect Technologies offers a Wire Protocol ODBC driver for every major database - Oracle, DB2, Microsoft SQL Server, Sybase and Informix - on both 32-bit and 64-bit Windows and Linux/UNIX platforms. Wire Protocol technology eliminates the need for client libraries, simplifying development and eliminating configuration and support issues.
  • Microsoft selected DataDirect Technologies to be the exclusive licensee of their ODBC Driver Manager to port ODBC to non-Windows platforms. Therefore, DataDirect Technologies has a UNIX ODBC Driver Manager that has equivalent functionality of the Microsoft Windows ODBC Driver Manager.
  • All major database vendors, including Oracle, IBM, Microsoft, Sybase and Progress, embed DataDirect Technologies ODBC drivers as part of their strategic product offerings.
  • DataDirect Technologies offers award-winning 24x7 technical support to ensure rapid response to customer concerns (see the SupportLink website for more information.)

Which Version of the Specification Does DataDirect Technologies Support?

DataDirect Technologies supports version 3.52 of the Microsoft ODBC specification.

ODBC Performance

Take a look at Increasing Driver Performance for an explanation of which driver options you should set in the DataDirect Connect Series ODBC drivers to achieve maximum performance.

ODBC Diagnostics

ODBC tracing allows you to trace calls to ODBC drivers and create a log of the traces. The Windows ODBC Driver Manager by Microsoft has tracing capabilities, which you enable on the Tracing tab of the ODBC Administrator. You can also specify the name and location of the tracing log on this tab.

On Linux/UNIX, depending on the implementation of the ODBC Driver Manager, ODBC tracing may be available. The UNIX ODBC Driver Manager has tracing capabilities, and this support is enabled through the system information file. The [ODBC] section in the system information file must include the keywords Trace and TraceFile. For example:

Trace=1
TraceFile=odbctrace.out

Important Features of DataDirect Technologies ODBC Drivers

General

  1. Wire protocol architecture eliminates the need for client libraries, simplifying development and eliminating configuration and support issues.
  2. Consistently supports the latest, most complete implementation of the ODBC specification and the latest operating systems and database versions and features
  3. DataDirect Technologies is committed to quality assured by rigorous product testing and uses the largest in-house ODBC test suite in the industry.
  4. Strategic technology relationships with the top database vendors make DataDirect Technologies unique in the industry.
  5. DataDirect Technologies has played a strong leadership role in the ODBC standard from the beginning with leadership roles in early ODBC expert panels (ANSI, ISO SQL CLI X/Open SQL Access Group Open Group SQL CLI).
  6. DataDirect Connect64 is the industry's only complete suite of 64-bit wire protocol drivers for all major databases. DataDirect Connect64 enables customers to take full advantage of the processing power of 64-bit applications by providing high performance, highly scalable data connectivity.

Oracle Wire Protocol ODBC Driver

  1. The DataDirect Connect for ODBC Oracle Wire Protocol driver connects to Oracle servers without the need for any Oracle networking software. The driver is optimized to communicate directly and efficiently with Oracle servers using the Oracle server wire protocol.
  2. The DataDirect Connect for ODBC Oracle Wire Protocol driver fully supports all Oracle systems including Oracle 8.1.5 and higher from a single wire protocol ODBC driver.
  3. The DataDirect Connect for ODBC Oracle Wire Protocol driver optimizes forward-only cursors to pre-fetch multiple records in compact formats that are not available using OCI. These optimizations reduce network traffic and improve performance when retrieving data from the database. Testing with industry and customer benchmarks shows that the DataDirect Technologies driver is the fastest Oracle driver in the marketplace when retrieving results from SELECT statements.
  4. The DataDirect Connect for ODBC Oracle Wire Protocol driver is highly optimized for applications that use LOB data. How much time an ODBC driver spends sending and retrieving packets over the network determines how fast the driver can be. LOB data is inherently very taxing on the network in that large amounts of data must be transferred between the driver and the Oracle server. The DataDirect Technologies driver uses special compression techniques unavailable to OCI applications when transferring LOB data to and from the Oracle server.
  5. The DataDirect Connect for ODBC Oracle Wire Protocol driver is highly optimized for applications that use PL/SQL stored procedures. For these types of applications, the DataDirect Technologies driver has performance advantages from 75% to 100% faster than competitive drivers in benchmarks run inside the DataDirect Technologies development labs.

Informix Wire Protocol ODBC Driver

  1. The DataDirect Connect for ODBC Informix Wire Protocol driver connects to Informix servers without the need for any Informix networking software. The driver is optimized to communicate directly and efficiently with Informix servers using the Informix server wire protocol.
  2. The DataDirect Connect for ODBC Informix Wire Protocol driver supports a robust set of the ODBC 3.5 API features, including keyset scrollable cursors, bulk operations, and use of Informix types such as CLOB and BLOB as ODBC-based data types.
  3. The DataDirect Connect for ODBC Informix Wire Protocol driver optimizes forward-only cursors to pre-fetch multiple records in compact formats that are not available using Informix CLI. These optimizations reduce network traffic and improve performance when retrieving data from the database. Testing with industry benchmarks and customer benchmarks shows that the DataDirect driver is the fastest Informix driver in the marketplace when retrieving results from SELECT statements.
  4. The DataDirect Connect for ODBC Informix Wire Protocol driver is highly optimized for applications that use LOB data. How much time an ODBC driver spends sending and retrieving packets over the network determines how fast the driver can be. LOB data is inherently very taxing on the network in that large amounts of data must be transferred between the driver and the Informix server. The DataDirect Technologies driver uses special compression techniques unavailable to CLI applications when transferring LOB data to and from the Informix server.

Sybase Wire Protocol ODBC Driver

  1. The DataDirect Connect for ODBC Sybase Wire Protocol driver was built from a version of the TDS specification that was provided to DataDirect from Sybase. With every new release of the Sybase engine, DataDirect is involved in the development of the TDS enhancements.
  2. The DataDirect Connect for ODBC Sybase Wire Protocol driver fully supports Sybase 11.x and higher.
  3. The DataDirect Connect for ODBC Sybase Wire Protocol driver is available on many different platforms including Windows, Linux, and most all mainstream UNIX systems.
  4. The DataDirect Connect for ODBC Sybase Wire Protocol driver was the first ODBC 3.0-compliant Sybase driver in the marketplace. It not only supports the minimum API required by the specification, but also implements the entire API.

SQL Server Wire Protocol ODBC Driver

  1. The DataDirect Connect for ODBC SQL Server Wire Protocol driver was built through an agreement with Microsoft to port their ODBC Windows source code for both ODBC core components and the SQL Server driver to non-Microsoft Windows platforms. It is built using a version of the TDS specification that Microsoft provided to DataDirect.
  2. The DataDirect Connect for ODBC SQL Server Wire Protocol driver fully supports SQL Server 7, SQL Server 2000, and SQL Server 2005.
  3. The DataDirect Connect for ODBC SQL Server Wire Protocol driver was the first ODBC 3.0-compliant SQL Server driver on non-Windows platforms in the marketplace. It not only supports the minimum API required by the specification, but also implements the entire API.

DB2 Wire Protocol ODBC Driver

  1. The DataDirect Connect for ODBC DB2 Wire Protocol driver does not require any DB2 client libraries. The DataDirect driver connects directly to the DB2 server and does not require any host-side installations.
  2. The DataDirect Connect for ODBC DB2 Wire Protocol driver connects to all varieties of DB2 on any of these platforms: Windows, UNIX, Linux, z/OS, and iSeries.
  3. The DataDirect Connect for ODBC DB2 Wire Protocol driver fully supports:
    • DB2 UDB v7.1, v7.2, v8.1, v8.2, v9.1
    • DB2 UDB v7x, v8.1 for z/OS
    • DB2 UDB V5R1, V5R2, V5R3, V5R4 for iSeries.

The complete 32-bit DataDirect Connect for ODBC support matrix is available here.
The complete 64-bit DataDirect Connect for ODBC support matrix is available here.

Where Can I Obtain ODBC Drivers?

ODBC drivers are available for many databases with differing levels of functionality. A list of vendors and sources can be found at Ken North's site.

For More Information

For information about the ODBC interface, see http://msdn.microsoft.com.

References Used in this Document

Microsoft® ODBC Programmer's Reference and SDK Guide, Microsoft Press.

Ready to try DataDirect Connect for ODBC?
| Learn More
Ready to try DataDirect Connect for ODBC?