what is the difference between OLE DB and ODBC data sources?

Question

I was reading a MS Excel help article about pivotcache and wonder what they mean by OLE DB and ODBC sources

...You should use the CommandText property instead of the SQL property, which now exists primarily for compatibility with earlier versions of Microsoft Excel. If you use both properties, the CommandText property’s value takes precedence.

For OLE DB sources, the CommandType property describes the value of the CommandText property.

For ODBC sources, the CommandText property functions exactly like the SQL property, and setting the property causes the data to be refreshed...

I really appreciate your short answers.

Solution 1

According to ADO: ActiveX Data Objects, a book by Jason T. Roff, published by O'Reilly Media in 2001 (excellent diagram here), he says precisely what MOZILLA said.

(directly from page 7 of that book)

• ODBC provides access only to relational databases

• OLE DB provides the following features
• Access to data regardless of its format or location
• Full access to ODBC data sources and ODBC drivers

So it would seem that OLE DB interacts with SQL-based datasources THRU the ODBC driver layer.

I'm not 100% sure this image is correct. The two connections I'm not certain about are ADO.NET thru ADO C-api, and OLE DB thru ODBC to SQL-based data source (because in this diagram the author doesn't put OLE DB's access thru ODBC, which I believe is a mistake).

Solution 2

ODBC:- Only for relational databases (Sql Server, Oracle etc)

OLE DB:- For both relational and non-relational databases. (Oracle, Sql-Server, Excel, raw files, etc)

Solution 3

Here's my understanding (non-authoritative):

ODBC is a technology-agnostic open standard supported by most software vendors. OLEDB is a technology-specific Microsoft's API from the COM-era (COM was a component and interoperability technology before .NET)

At some point various datasouce vendors (e.g. Oracle etc.), willing to be compatible with Microsoft data consumers, developed OLEDB providers for their products, but for the most part OLEDB remains a Microsoft-only standard. Now, most Microsoft data sources allow both ODBC and OLEDB access, mainly for compatibility with legacy ODBC data consumers. Also, there exists OLEDB provider (wrapper) for ODBC which allows one to use OLEDB to access ODBC data sources if one so wishes.

In terms of the features OLEDB is substantially richer than ODBC but suffers from one-ring-to-rule-them-all syndrome (overly generic, overcomplicated, non-opinionated).

In non-Microsoft world ODBC-based data providers and clients are widely used and not going anywhere.

Inside Microsoft bubble OLEDB is being phased out in favor of native .NET APIs build on top of whatever the native transport layer for that data source is (e.g. TDS for MS SQL Server).

Solution 4

ODBC and OLE DB are two competing data access technologies. Specifically regarding SQL Server, Microsoft has promoted both of them as their Preferred Future Direction - though at different times.

ODBC

ODBC is an industry-wide standard interface for accessing table-like data. It was primarily developed for databases and presents data in collections of records, each of which is grouped into a collection of fields. Each field has its own data type suitable to the type of data it contains. Each database vendor (Microsoft, Oracle, Postgres, …) supplies an ODBC driver for their database.

There are also ODBC drivers for objects which, though they are not database tables, are sufficiently similar that accessing data in the same way is useful. Examples are spreadsheets, CSV files and columnar reports.

OLE DB

OLE DB is a Microsoft technology for access to data. Unlike ODBC it encompasses both table-like and non-table-like data such as email messages, web pages, Word documents and file directories. However, it is procedure-oriented rather than object-oriented and is regarded as a rather difficult interface with which to develop access to data sources. To overcome this, ADO was designed to be an object-oriented layer on top of OLE DB and to provide a simpler and higher-level – though still very powerful – way of working with it. ADO’s great advantage it that you can use it to manipulate properties which are specific to a given type of data source, just as easily as you can use it to access those properties which apply to all data source types. You are not restricted to some unsatisfactory lowest common denominator.

While all databases have ODBC drivers, they don’t all have OLE DB drivers. There is however an interface available between OLE and ODBC which can be used if you want to access them in OLE DB-like fashion. This interface is called MSDASQL (Microsoft OLE DB provider for ODBC).

SQL Server Data Access Technologies

Since SQL Server is (1) made by Microsoft, and (2) the Microsoft database platform, both ODBC and OLE DB are a natural fit for it.

ODBC

Since all other database platforms had ODBC interfaces, Microsoft obviously had to provide one for SQL Server. In addition to this, DAO, the original default technology in Microsoft Access, uses ODBC as the standard way of talking to all external data sources. This made an ODBC interface a sine qua non. The version 6 ODBC driver for SQL Server, released with SQL Server 2000, is still around. Updated versions have been released to handle the new data types, connection technologies, encryption, HA/DR etc. that have appeared with subsequent releases. As of 09/07/2018 the most recent release is v13.1 “ODBC Driver for SQL Server”, released on 23/03/2018.

OLE DB

This is Microsoft’s own technology, which they were promoting strongly from about 2002 – 2005, along with its accompanying ADO layer. They were evidently hoping that it would become the data access technology of choice. (They even made ADO the default method for accessing data in Access 2002/2003.) However, it eventually became apparent that this was not going to happen for a number of reasons, such as:

1. The world was not going to convert to Microsoft technologies and away from ODBC;
2. DAO/ODBC was faster than ADO/OLE DB and was also thoroughly integrated into MS Access, so wasn’t going to die a natural death;
3. New technologies that were being developed by Microsoft, specifically ADO.NET, could also talk directly to ODBC. ADO.NET could talk directly to OLE DB as well (thus leaving ADO in a backwater), but it was not (unlike ADO) solely dependent on it.

For these reasons and others, Microsoft actually deprecated OLE DB as a data access technology for SQL Server releases after v11 (SQL Server 2012). For a couple of years before this point, they had been producing and updating the SQL Server Native Client, which supported both ODBC and OLE DB technologies. In late 2012 however, they announced that they would be aligning with ODBC for native relational data access in SQL Server, and encouraged everybody else to do the same. They further stated that SQL Server releases after v11/SQL Server 2012 would actively not support OLE DB!

This announcement provoked a storm of protest. People were at a loss to understand why MS was suddenly deprecating a technology that they had spent years getting them to commit to. In addition, SSAS/SSRS and SSIS, which were MS-written applications intimately linked to SQL Server, were wholly or partly dependent on OLE DB. Yet another complaint was that OLE DB had certain desirable features which it seemed impossible to port back to ODBC – after all, OLE DB had many good points.

In October 2017, Microsoft relented and officially un-deprecated OLE DB. They announced the imminent arrival of a new driver (MSOLEDBSQL) which would have the existing feature set of the Native Client 11 and would also introduce multi-subnet failover and TLS 1.2 support. The driver was released in March 2018.

Solution 5

• August, 2011: Microsoft deprecates OLE DB (Microsoft is Aligning with ODBC for Native Relational Data Access)

• October, 2017: Microsoft undeprecates OLE DB (Announcing the new release of OLE DB Driver for SQL Server)

View more solutions

Comments

[B. Burgdorf]

Just a side note, according to this book Implementing a Data Warehouse with Microsoft SQL Server 2012: "Microsoft has announced that at some point in the near future, support for OLE DB connections will be removed in favor of ODBC connections."

[Bogey Jammer]

Since october 6 2017, it is undeprecated. See blogs.msdn.microsoft.com/sqlnativeclient/2017/10/06/…

[Reaper]

If OLE DB uses ODBC to connect to SQL data sources, then any SQL data source which is supported by OLE DB would have to be supported by ODBC, however this is not the case - the original diagram must have been correct (and not this one).

[Andy Dent]

Wrong, both can talk to non-relational stores depending on the drivers.

[bobobobo]

Actually sometimes OLE DB wraps the ODBC driver, sometimes it doesn't. See here

[Hernán]

This entry jamesmccaffrey.wordpress.com/2006/05/02/odbc-vs-ole-db says that for SQL DS, OLEDB goes thru ODBC.

[Asad Saeeduddin]

@DannyVarod Isn't that the wrong way around? Did you mean, "If OLE DB uses ODBC to connect to SQL data sources, any SQL data source which is supported by ODBC must be supported by OLE DB"?

[Reaper]

@Asad No. If A is a only wrapper of B, then anything supported by A must be supported by B. A=OLEDB, B=ODBC. And OLE DB is not only a wrapper above ODBC, even though it does have partial support for ODBC connections. The opposite relation (if B supports it than A does too) is not required because no one said that A is a complete wrapper of B.

[Asad Saeeduddin]

@DannyVarod Ah, never mind. I missed the critical qualifier in "any SQL data source which is supported by OLE DB would ...". I was talking about the fact that since OLE DB supports non-RDBMS data sources, it is entirely possible for the unfiltered set of data sources supported by OLE DB to be a superset of the ones supported by ODBC.

[Reaper]

@Asad OLE DB does have support for relational DBs that ODBC does not, that is what I meant by this diagram being wrong and the original one being more correct.

[Chris Wood]

As with many IT related subjects, things have almost come full circle. SQL 2012 was the last version to support OLE DB Native provider and applications should now switch to back ODBC. like the "olden days" of SQL Server technet.microsoft.com/en-us/library/hh967418.aspx

[Michael David Watson]

"OLE DB is newer and arguably better" this may have been true in 2008 but not in 2014.

[Michael David Watson]

I would also like to add the OLEDB requires use of COM which is very dated

[Mike Dimmick]

ADO.NET does not wrap ADO. ADO.NET classes generally talk directly to their database or database network library, not via any other provider/driver layer. For example, System.Data.SqlClient handles the TDS protocol in managed code, only using native code to handle the TCP/Named Pipes/etc transmission over the network. For databases that don't have a managed provider of their own, you can use System.Data.OleDb to wrap OLE DB or System.Data.Odbc to wrap ODBC, but it's not recommended.

[LuckyLikey]

@MichaelDavidWatson So what would you say. Better use ODBC oder OLEDB? I need to support as much different SQL Databases as possible. And as it points out, OLE DB may also access an ODBC Datasource. So why would you say "OLE DB is newer and arguably better" is still incorrect in 2015? :)

[Wernfried Domscheit]

No, with ODBC you can query even flat CSV-Files, not only relational databases.

[Robino]

@LuckyLikey MS have deprecated OLEDB and SQL Server no longer supports it (SS 2012 was the last to support it). msdn.microsoft.com/en-us/library/hh967418.aspx

[Scott Chu]

Wrong! There're also text file and XML ODBC driver.

[uzay95]

I think this is not correct... Open Database Connectivity (ODBC) is Microsoft's strategic interface for accessing data in a heterogeneous environment of relational and non- relational database management systems. support.microsoft.com/en-us/kb/110093

[Yousha Aleayoub]

lol, yo guys are talking about ODBC in 2009 or 2016...? it WAS correct.

[marktwo]

@ChieltenBrinke I cobbled the post together from several sources, such as the links I have updated my post to include and, not least, the comments they provoked. Other sources were Jason Roff's book on ADO mentioned by bobobobo, and The Access 2002 Desktop Developer's Handbook, by Litwin, Getz and Gunderloy (real old, but a real classic). I don't have any sort of inside track at Microsoft, so my speculations as to the thinking behind their various changes of direction, though plausible, are entirely my own.