Open Database Connectivity

In computing, Open Database Connectivity (ODBC) provides a standard software API method for using database management systems (DBMS). The designers of ODBC aimed to make it independent of programming languages, database systems, and operating systems.

Overview

The ODBC SpecificationFact|date=July 2008 offers a procedural API for using SQL queries to access data. An implementation of ODBC will contain one or more applications, a core ODBC "Driver Manager" library, and one or more "database drivers". The Driver Manager, independent of the applications and DBMS, acts as an "interpreter" between the applications and the database drivers, whereas the database drivers contain the DBMS-specific details. Thus a programmer can write applications that use standard types and features without concern for the specifics of each DBMS that the applications may encounter. Likewise, database driver implementors need only know how to attach to the core library. This makes ODBC modular.

To write ODBC code that exploits DBMS-specific features requires more advanced programming: an application must use introspection, calling ODBC metadata functions that return information about supported features, available types, syntax, limits, isolation levels, driver capabilities and more. Even when programmers use adaptive techniques, however, ODBC may not provide some advanced DBMS features. The ODBC 3.x API operates well with traditional SQL applications such as OLTP, but it has not evolved to support richer types introduced by and .

ODBC provides the standard of ubiquitous data access because hundreds of ODBC drivers exist for a large variety of data sources. ODBC operates with a variety of operating systems and drivers exist for non-relational data such as spreadsheets, text and XML files. Because ODBC dates back to 1992, it offers connectivity to a wider variety of data sources than other data-access APIs. More drivers exist for ODBC than drivers or providers exist for newer APIs such as OLE DB, JDBC, and ADO.NET.

Despite the benefits of ubiquitous connectivity and platform-independence, systems designers may perceive ODBC as having certain drawbacks. Administering a large number of client machines can involve a diversity of drivers and DLLs. This complexity can increase system-administration overhead. Large organizations with thousands of PCs have often turned to ODBC server technology (also known as "Multi-Tier ODBC Drivers") to simplify the administration problems.cite news | first=Clara H. | last=Parkes | title=Power to the People | date=April 1996 | publisher=Miller Freeman, Inc. | url =http://www.dbmsmag.com/9604pnet.html | work =DBMS Magazine | pages = | accessdate = 2008-02-15 ]

Differences between drivers and driver maturity can also raise important issues. Newer ODBC drivers do not always have the stability of drivers already deployed for years. Years of testing and deployment mean a driver may contain fewer bugs.

Developers needing features or types not accessible with ODBC can use other SQL APIs. When not aiming for platform-independence, developers can use proprietary APIs, whether DBMS-specific (such as TransactSQL) or language-specific (for example: JDBC for Java applications).

History

ODBC uses as its basis the various Call Level Interface (CLI) specifications from the SQL Access Group, X/Open (now part of The Open Group), and the ISO/IEC. Microsoft, in partnership with Simba Technologies, created ODBC by adapting the SQL Access Group CLI. It released ODBC 1.0 in September 1992. After ODBC 2.0, Microsoft decided to align ODBC 3.0 with the CLI specification making its way through X/Open and ISO. In 1995, SQL/CLI became part of the international SQL standard (ISO/IEC 9075-3 -- Information technology -- Database languages -- SQL -- Part 3: Call-Level Interface (SQL/CLI)).

Bridging configurations

JDBC-ODBC bridges

A JDBC-ODBC bridge consists of a JDBC driver which employs an ODBC driver to connect to a target database. This driver translates JDBC method calls into ODBC function calls. Programmers usually use such a bridge when a particular database lacks a JDBC driver. Sun Microsystems included one such bridge in the JVM, but viewed it as a stop-gap measure while few JDBC drivers existed. Sun never intended its bridge for production environments, and generally recommends against its use. Independent data-access vendors now deliver JDBC-ODBC bridges which support current standards for both mechanisms, and which far outperform the JVM built-in.Fact|date=July 2008

ODBC-JDBC bridges

An ODBC-JDBC bridge consists of an ODBC driver which uses the services of a JDBC driver to connect to a database. This driver translates ODBC function calls into JDBC method calls. Programmers usually use such a bridge when they lack an ODBC driver for a particular database but have access to a JDBC driver.

Implementations

ODBC implementations run on many operating systems, including Microsoft Windows, Unix, Linux, OS/2, OS/400, IBM i5/OS, and Mac OS X. Hundreds of ODBC drivers exist, including drivers for Oracle, DB2, Microsoft SQL Server, Sybase, Pervasive SQL, IBM Lotus Domino, MySQL, PostgreSQL, and desktop database products such as FileMaker, and Microsoft Access.

Microsoft ODBC

Microsoft released the first ODBC product as a set of DLLs for Microsoft Windows. As of 2006, Microsoft ships its own ODBC with every supported version of Windows: compare Microsoft Data Access Components.

iODBC

iODBC (Independent Open DataBase Connectivity) offers an open source, platform-independent implementation of both the ODBC and X/Open specifications, generally used on platforms other than Microsoft Windows. OpenLink Software maintains and supports the iODBC project and distributes its software under the LGPL and/or BSD License. Apple chose to bundle iODBC into Mac OS X and Darwin, starting with Darwin 6.0 and Mac OS X v10.2. Programmers have ported iODBC to several other operating systems and hardware platforms, including Mac OS 9, Linux (x86, x86-64, IA-64, Alpha, MIPS, and ARM), Solaris (SPARC and x86), AIX, HP-UX (PA-RISC and Itanium), Compaq Tru64, Digital UNIX, Dynix, Generic UNIX 5.4, FreeBSD, DG-UX, and OpenVMS.

IBM i5/OS

In the IBM i5/OS (formerly OS/400), IBM's DB2 implementation supports ODBC. The vendor provides ODBC Drivers for Windows and JDBC as part of the Server and Client Packages.

UnixODBC

The unixODBC project — headed, maintained and supported by Easysoft Director Nick Gorham — has become the most common driver-manager for non-Microsoft Windows platforms (and for one Microsoft platform, Interix). It offered full ODBC3 support and Unicode in advance of its competitors. Most Linux distributions as of 2006 ship it, including Red Hat, Mandriva and Gentoo. Several vendors of commercial databases, including IBM (DB2, Informix), Oracle and SAP (Ingres) use it for their own drivers. It includes GUI support for both KDE and GNOME. Many open source projects — including OpenOffice.org and Glade — also make use of it. It builds on any platform that supports the GNU autoconf tools (in other words, most of them). For licensing, UnixODBC uses the LGPL and the GPL.

UDBC

UDBC, a cross-platform fusion of ODBC and the SAG CLI, enabled non-Windows-based DBMS-independent application development at a time when shared-library implementations on Unix occurred only sporadically. This effort laid the foundation for the iODBC open source project.

See also

* JDBC
* ODBC Administrator

External links

* [http://support.microsoft.com/kb/110093 Microsoft ODBC Overview]
* [http://publib.boulder.ibm.com/infocenter/iseries/v5r3/topic/rzaii/rzaiiodbcadm.htm OS400 and i5OS ODBC Admininstration]
* [http://www.roth.net/perl/odbc/conf/sld002.htm Presentation slides from www.roth.net]
* [http://www.dbmsmag.com/9403d13.html Understanding ODBC and multidatabase adaptive programming]
* [http://www.dbmsmag.com/9604d53.html Understanding ODBC 3.0 and OLE DB]
* [http://ourworld.compuserve.com/homepages/Ken_North/Odbcfact.HTM The ODBC Hall of Fame]
* [http://www.openlinksw.com/info/docs/odbcwhp/tableof.htm Early ODBC White Paper]
* [http://www.iodbc.org/index.php?page=languages/php/odbc-phpHOWTO ODBC & PHP Installation & Config Guide]
* [http://www.macsos.com.au/MacODBC/ Mac OS Platforms ODBC Guide]
* [http://www.easysoft.com/developer/interfaces/odbc/linux.html What is ODBC?]
* [http://blogs.msdn.com/data/archive/2006/12/05/data-access-api-of-the-day-part-i.aspx Microsoft ODBC & Data Access APIs History Article]
* [http://www.ibprovider.com/eng/documentation/odbc_escape_sequences_eng.html The Compete Guide to ODBC Escape Sequences in Firebird and Interbase]

Sources of ODBC drivers

* [http://www.SQLSummit.com/ODBCVend.htm Comprehensive catalog of ODBC Drivers at SQLSummit.com]
* [http://www.unixodbc.org/drivers.html List of ODBC Drivers at unixODBC.org]

References