ODBC vs OLEDB

If you know what they are, you’re probably a developer. If you don’t know what they are, but want to know – you’re probably a future developer.

Because of the technical nature of this comparison, I’ve included a glossary of terms at the end of the article. If you’re new to this sort of thing, you may want to take a glance at that first.

Let’s take a look at these terms, what they’re used for and which one I recommend.

Defining Terms

ODBC Driver Architecture

ODBC is short for Open Database Connecting. It is an interface standard, designed for communication between different apps and operating systems (OS).

Like, for instance, if you used wrote a program for Linux, but you wanted I to work in (OS) too. Your answer would be an API like ODBC.

In days gone by, programs used to be re-written entirely for new or different OS. The process was inefficient.

ODBC popped up in 1992 to solve that problem.

ODBC was originally created for Structured Query Language (SQL). It has since expanded to handle more programming languages.

OLE BD is short for Object Linking and Embedding Database. This is a group of API’s designed to provide access to app data in different file formats. This included SQL capability (like ODBC), and many other languages.

OLE BD was set to succeed ODBC, but things changed…

ODBC vs. OLEDB

ODBC was initially focused on SQL and, if you’re using SQL, it makes sense to go with ODBC. The obvious choice used to be OLEDB. But as you’ll find out later in the article, the last SQL release to support OLEDB launched in 2012. And it’s phasing out fast.

This change in strategy by Microsoft took some off guard. Many stubborn users clung to ODBC for so long, so the move made sense. Also there was the fact that ODBC was expanding.

As for the difference between these two, it’s very hard to say without getting very technical.

At the core they are different API’s for different data sources.

One opinion is that ODBC is more specific and to the point, where OLEDB is overly generic and over-complicated.

Current Support

SQL’s 2012 launch was the last to support OLEDB. This skews the vote in favour of ODBC.

ODBC has expanded its compatibility with the use of drivers, which is a major driving factor in the change of strategy by Microsoft.

Developers need to Adapt

The abovementioned SQL release (denali) came with seven years of support for OLEDB. This means that as I write this, developers have just two years left to adapt.

Everyone understands that it can be hard to adapt, but OLEDB users will have no choice very soon.

The Differences between ODBC and OLEDB

Bear with me, it’s about to get very technical. For ease of reading I’ve contained the information in a table.

This table is based off of information by a technical white paper from ftp.sas.com

That just about sums it up. I hope you have a better grasp of the difference between ODBC and OLEDB now. If you don’t, I’ve provided some further reading below, as well as a link to the technical paper mentioned above.

If you have experience in using these two API’s, why not let us know in the comments? Did we get something wrong? Is there something you could add for the newbies out there?

We’d love to hear from you in the comments.

Glossary

ODBC: Open Database Connecting

OLE DB: Object Linking and Embedding Database

OS: Operating System (like windows)

API: Application Programming Interface

Relational Database: A set of data items sorted into tables. The data items can be accessed and reassembled without reorganizing the database tables.

Non-Relational Database: Does not follow the relational standard. Also known as NoSQL database.

Further Reading

Link to (outdated – see above, OLEDB is losing SQL functionality) white paper: http://ftp.sas.com/techsup/download/v8papers/odbcdb.pdf

Relational vs. Non-relational database: https://www.mongodb.com/scale/relational-vs-non-relational-database

User opinions on ODBC vs. OLEDB: https://community.qlik.com/thread/106540

Sharing is caring!