ODBC (Open Database Connectivity)

ODBC (Open Data Base Connectivity) is an interface to access databases via SQL queries. ODBC can be used as an access tool to various databases such as MS-Access, dBase, DB2, Excel, and Text. Through these Call Level Interface (CLI) specifications of the SQL Access Group, the OBDC allows a neutral way of accessing the data stored in personal computers and various databases. It was first created by Microsoft and Simba Technologies. ODBC 1.0 was initially released in September 1992, and eventually became a part of the international SQL standard in 1995. Microsoft originally shipped the ODBCs as a set of DLLs and still today, ships the set with every copy of Microsoft Windows. Today, more and more operating systems such as Unix and Apple are adopting the ODBC.

The strength of ODBC is that by providing a universal data access interface, it allows independent software companies and parties to not have to learn multiple application programming interfaces. To simply put, with ODBC, applications can simultaneously access, view, and modify database from numerous and quite diverse databases. This is because the ODBC “re-codes” the SQL queries so that it would be readable by the various different databases.

However, the ODBC also has its drawbacks. As managing a huge number of ODBC clients can mean an immense amount of drivers and DLLs being run, this could lead to a system administration overhead. Nonetheless, this minor issue led to further uses of the ODBC server technology (or the “Multi-Tier ODBC Drivers”) to alleviate the load. Also, as drivers are a key in ODBCs, some have raised the issue of the newer drivers’ stability, as often many have shown to have bugs in them.

How ODBC is Processed

To use the ODBC, three components are needed: ODBC client, ODBC driver, and a DBMS server (ex. Microsoft Access, SQL Server, Oracle, and FoxPro). Firstly, the ODBC client will use a command (referred to as “ODBC”) to interact (requesting and/or sending data) with the DBMS server (back-end). However, the DBMS server will not understand the command by the ODBC client yet, as the command has yet to be processed through the ODBC driver (front-end). So then, the ODBC driver will decode the command that can be processed by the ODBC server and be sent there. The ODBC server will then respond back to the ODBC driver which will translate the final output to the ODBC client.

ODBC Drivers

ODBC drivers are specific to each type of database.

Where to Get an Oracle ODBC Driver

The Oracle ODBC Drivers Download Page is the source for official Oracle ODBC drivers.

The Easysoft ODBC Oracle Driver improves on the stock Oracle ODBC drivers by providing improved performance and easier maintenance.

OpenLink Software provides both Single-Tier and Multi-Tier Oracle ODBC drivers.

DataDirect Connect for ODBC is a replacement Oracle ODBC driver which provides improved performance and easier maintenance.

Attunity provides a data adapter which includes an Oracle ODBC driver.

Where to Get a MySQL ODBC Driver

MySQL Connector/ODBC is the official MySQL ODBC driver.

OpenLink Software provides both Single-Tier and Multi-Tier MySQL ODBC drivers.

Sources for Other ODBC Drivers

To search for an ODBC driver for another database, check Ken North’s extensive list of ODBC Drivers and Servers and the unixODBC list of ODBC drivers which work with unixODBC.

ODBC Driver Managers

The two main ODBC driver managers for Unix are iODBC and unixODBC.

iODBC (Independent Open DataBase Connectivity) is an Open Source platform independent implementation of both the ODBC and X/Open specifications. iODBC provides both an ODBC driver manager and an SDK that facilitates the development of database-independent applications. iODBC includes a GTK+-based administration tool.

iODBC has been ported to numerous platforms, including: Linux (x86, Itanium, Alpha, Mips, and StrongArm), Solaris (Sparc & x86), AIX, HP-UX (PA-RISC & Itanium), Digital UNIX, Dynix, Generic Unix 5.4, FreeBSD, MacOS 9, MacOS X, DG-UX, and OpenVMS.

unixODBC provides Unix applications with the same ODBC 3.51 API and facilities available under Windows. unixODBC provides a Driver Manager that supports the full ODBC API and performs the ODBC 3 to ODBC 2 translations with UNICODE to ANSI conversion. unixODBC also includes a set of graphical utilities that allow users to specify connections to DBMSes to be used by applications, a collection of ODBC drivers including a simple text based driver, an NNTP driver, a Postgres driver and others, and a selection of templates and libraries that to aid in the construction of ODBC drivers. unixODBC works with MySQL, Postgres, StarOffice/OpenOffice, Applixware, iHTML, PHP, Perl DBD::ODBC, and many other applications and drivers. Connection pooling is also provided to increase performance with applications such as PHP. unixODBC includes a QT-based administration GUI.