ODBC vs. OLE DB

A frequent question I get from new software test engineers is, "What is the difference between ODBC and OLE DB?" Both are specifications which describe how an application program can access the data in a data store. ODBC stands for Open Database Connectivity. The ODBC call-level interface specification was created by Microsoft in 1992 as a way to standardize program-to-SQL data communication. Before ODBC, application programmers had to use a different set of API calls for every type of database. By creating a standard interface, programmers could write one set of code (for the most part) that would work with any ODBC-compliant database. ODBC was quickly embraced by most major database vendors and became a de facto standard. Notice that this cooperation happened when Microsoft was not a very large company.

OLE DB originally stood for Object Linking and Embedding for Databases, but now the acronym just means a COM-based interface to a wide range of data sources. OLE DB is sometimes written as OLEDB or OLE-DB. OLE DB came into being in the mid 1990s through an evolution and merging of several Microsoft technologies. The idea of OLE DB is to provide programmers with a consistent interface to many different types of data, including SQL databases, Excel spreadsheets, and so on.

The best way to understand the relationship between ODBC and OLE DB is by way of a picture. (Click on the image at the bottom of this blog entry to enlarge it so you can see it clearly). Imagine that you are a developer or tester writing a program which needs to access and manipulate some data. If the data is stored in a SQL relational database, use can use ODBC calls. It turns out that working directly with ODBC is a bit awkward. An alternative is to use OLE DB. OLE DB programming tends to be quite a bit easier than ODBC programming, in part because OLE DB operates at a higher level of abstraction. The downside of OLE DB is a slight performance penalty in most cases. Now if you want to get at Excel data, you can also use OLE DB calls to access and manipulate the data. Of course I’ve left out a lot of details, but this overview should help you understand the difference between ODBC and OLE DB.

If you enjoyed reading this blog you might enjoy working at Microsoft. My parent company, Volt Information Sciences, is always looking for software engineers. Check out the job listings at http://jobs.volt.com/.