Open Database Connectivity (ODBC)

What is Open Database Connectivity (ODBC)?

Open Database Connectivity (ODBC) is an open standard application programming interface (API) that allows application programmers to easily access data stored in a database.

The main proponent and supplier of ODBC programming support is Microsoft, but ODBC is based on and closely aligned with the Structured Query Language (SQL) Call Level Interface (CLI) standard from The Open Group. Sponsored by many major vendors, including Oracle, IBM and Hewlett Packard Enterprise, The Open Group is a consortium that develops and manufactures The Open Group Architecture Framework, or TOGAF. In addition to CLI specifications from The Open Group, ODBC also aligns with the International Organization for Standardization/International Electrotechnical Commission for database APIs.

How does ODBC work?

ODBC acts as a translator between an application and a data store. The application makes a simple standard call to the ODBC engine, which changes that to a form the database can handle. ODBC can save a lot of time and effort because the program doesn't need to know the details of each database, and the database doesn't need to be configured for every application that wants to access it.

ODBC allows programs to use SQL requests that access databases without knowing the proprietary interfaces to the databases. ODBC handles the SQL request and converts it into a request each database system understands.

ODBC consists of four components, working together to enable functions:

• Application. The end-user program that calls the ODBC functions and submits the SQL statements.
• Driver manager. Loads the correct driver for each application and database. It can also handle some other tasks that the application might not be fully programmed for, such as authentication and encryption.
• Driver. Handles ODBC function calls, and then submits each SQL request to the data source.
• Data source. The database being accessed and its database management system. Simple data sources include flat text and comma-separated value files. ODBC can also work with MySQL when its driver is called MyODBC. Sometimes, this is referred to as the MySQL Connector/ODBC.

Java Database Connectivity vs. Open Database Connectivity

The Java Database Connectivity (JDBC) API uses the Java programming language to access a database. When writing programs in Java using JDBC APIs, users can employ software that includes a JDBC-ODBC bridge to access ODBC-supported databases.

However, the JDBC-ODBC bridge -- or JDBC Type 1 driver -- should be viewed as a transitional approach, as it creates performance overhead; API calls must pass through the JDBC bridge to the ODBC driver, then to the native database connectivity interface. In addition, it was removed in Java Development Kit 8, and Oracle does not support the JDBC-ODBC bridge.

The recommended approach is the use of JDBC drivers provided by database vendors, rather than the JDBC-ODBC bridge.

Other alternatives to ODBC

In addition to JDBC, there are other alternatives to ODBC, including the following:

Object Linking and Embedding Database

OLE DB is a Microsoft technology that acts as an API between an application and a data source, like ODBC. OLE DB was designed to work with more types of data sources, such as file systems and spreadsheet tables, instead of primarily databases. Microsoft designed OLE DB to replace ODBC, but most databases continue to support ODBC. The OLE DB manager can usually translate to ODBC connections as necessary.

ADO.NET

ADO.NET is the data access technology provided as part of the .NET Framework. It can be used in .NET programs to work with databases and other data sources. Bridges exist to translate ADO.NET queries into ODBC requests.

Embedded SQL

Embedded SQL is simply putting the desired SQL statements in the application code without a middleman layer, such as ODBC, to translate them. While embedded SQL has largely fallen out of favor, it can still be used in cases where extremely high performance is needed.

History of Open Database Connectivity

ODBC was created by SQL Access Group and first released in September 1992. Although Microsoft Windows was the first operating system to provide an ODBC product, versions exist for Unix, OS/2 and Macintosh platforms as well. The latest version of the specification, version 4.0, was released for production databases in 2017.

In the newer distributed object architecture called Common Object Request Broker Architecture, or CORBA, the Persistent Object Service is a superset of both the CLI and ODBC.

ODBC has remained largely universal since its inception in 1992 and has drivers available for just about all platforms and databases. Thin-client computing, however, has reduced the prevalence of ODBC in the enterprise, as the use of web-based applications has grown.

The role of a database administrator is evolving rapidly due to the widespread use of cloud computing. Find out what makes the role of a cloud DBA different from an on-premises DBA.