OLE DB 和 ODBC 数据源有什么区别？
我正在阅读一篇有关 Pivotcache 的 MS Excel 帮助文章，想知道 OLE DB 和 ODBC 源 的含义
...您应该使用 CommandText 属性而不是 SQL 属性， 现在主要是为了 与早期版本的兼容性 微软Excel。 如果您同时使用 属性，CommandText 属性的 值优先。
对于 OLE DB 源，CommandType 属性描述了的值 CommandText 属性。
对于 ODBC 源，CommandText 属性功能与 SQL属性，以及设置属性 导致数据被刷新...
我真的很感谢你的简短回答。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（11）
根据 Jason T. Roff 所著的 ADO：ActiveX 数据对象 ，由 O'Reilly Media 于 2001 年出版（此处为优秀图表），他所说的正是 MOZILLA 所说的。
（直接来自该书的第7页）
因此，OLE DB 似乎通过 ODBC 驱动程序层与基于 SQL 的数据源进行交互。
我不能 100% 确定此图片是否正确. 我不确定的两个连接是 ADO.NET 通过 ADO C-api，以及 OLE DB 通过 ODBC 到基于 SQL 的数据源（因为在 此图作者没有通过 ODBC 进行 OLE DB 访问，我相信是一个错误）。
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)
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).
这是我的理解（非权威）：
ODBC是大多数软件供应商支持的与技术无关的开放标准。
OLEDB 是 COM 时代的微软 API（COM 是 .NET 之前的组件和互操作性技术），
在某些时候，各种数据源供应商（例如 Oracle 等）愿意与Microsoft 数据消费者为其产品开发了 OLEDB 提供程序，但在大多数情况下 OLEDB 仍然是 Microsoft 独有的标准。 现在，大多数 Microsoft 数据源都允许 ODBC 和 OLEDB 访问，主要是为了与旧版 ODBC 数据使用者兼容。 此外，还存在用于 ODBC 的 OLEDB 提供程序（包装器），如果愿意，它允许用户使用 OLEDB 访问 ODBC 数据源。
就功能而言，OLEDB 比 ODBC 丰富得多，但存在“一环统治一切”综合症（过于通用、过于复杂、缺乏主见）。
在非 Microsoft 世界中，基于 ODBC 的数据提供程序和客户端得到了广泛使用，而且没有任何发展。
在 Microsoft 泡沫内部，OLEDB 正在被逐步淘汰，取而代之的是构建在该数据源的本机传输层之上的本机 .NET API（例如 MS SQL Server 的 TDS）。
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).
ODBC：- 仅适用于关系数据库（Sql Server、Oracle 等）
OLE DB：- 适用于关系数据库和非关系数据库。 （Oracle、Sql-Server、Excel、原始文件等）
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)
ODBC 和 OLE DB 是两种相互竞争的数据访问技术。 特别是关于 SQL Server，微软将它们作为首选的未来方向——尽管时间不同。
ODBC
ODBC 是用于访问表类数据的行业标准接口。 它主要是为数据库开发的，并以记录集合的形式呈现数据，每个记录都分组为字段集合。 每个字段都有自己的数据类型，适合其包含的数据类型。
每个数据库供应商（Microsoft、Oracle、Postgres 等）都为其数据库提供 ODBC 驱动程序。
还有一些对象的 ODBC 驱动程序，尽管它们不是数据库表，但它们非常相似，因此以相同的方式访问数据是有用的。 例如电子表格、CSV 文件和柱状报告。
OLE DB
OLE DB 是一种用于访问数据的 Microsoft 技术。 与 ODBC 不同，它包含类表和非类表数据，例如电子邮件、网页、Word 文档和文件目录。 然而，它是面向过程而不是面向对象，并且被认为是开发对数据源的访问的相当困难的接口。 为了克服这个问题，ADO 被设计为 OLE DB 之上的面向对象层，并提供一种更简单、更高级别（尽管仍然非常强大）的使用方式。 ADO 的巨大优势在于，您可以使用它来操作特定于给定类型数据源的属性，就像使用它访问适用于所有数据源类型的属性一样容易。 您不会受限于某些令人不满意的最低公分母。
虽然所有数据库都有 ODBC 驱动程序，但并非所有数据库都有 OLE DB 驱动程序。 然而，OLE 和 ODBC 之间有一个可用的接口，如果您想以类似 OLE DB 的方式访问它们，则可以使用该接口。 该接口称为 MSDASQL（Microsoft OLE DB ODBC 提供程序）。
SQL Server 数据访问技术
由于 SQL Server 是 (1) 由 Microsoft 开发的，并且 (2) Microsoft 数据库平台，因此 ODBC 和 OLE DB 都非常适合它。
ODBC
由于所有其他数据库平台都有 ODBC 接口，Microsoft 显然必须为 SQL Server 提供一个接口。 除此之外，DAO（Microsoft Access 中的原始默认技术）使用 ODBC 作为与所有外部数据源通信的标准方式。 这使得 ODBC 接口成为必要条件。
与 SQL Server 2000 一起发布的 SQL Server 版本 6 ODBC 驱动程序仍然存在。 已发布更新版本来处理后续版本中出现的新数据类型、连接技术、加密、HA/DR 等。 截至 2018 年 9 月 7 日，最新版本为 v13.1“SQL Server ODBC 驱动程序”，于 2018 年 3 月 23 日发布。
OLE DB
这是微软自己的技术，他们在 2002 年至 2005 年期间大力推广该技术，以及与之相伴的 ADO 层。 他们显然希望它将成为首选的数据访问技术。 （他们甚至将 ADO 设为 Access 2002/2003 中访问数据的默认方法。）但是，最终我们发现，由于多种原因，这种情况不会发生，例如：
远离 ODBC；
也可以直接与 ODBC 对话。 ADO.NET 可以直接与 OLE 对话
DB 也是如此（从而使 ADO 陷入停滞），但它不是（与
ADO）完全依赖于它。
由于这些原因 和其他人，微软实际上对于 v11 之后的 SQL Server 版本 (SQL Server 2012)，已弃用 OLE DB 作为数据访问技术。 在此之前的几年里，他们一直在生产和更新 SQL Server Native Client，它支持 ODBC 和 OLE DB 技术。 然而，在 2012 年底，他们宣布将与 ODBC 保持一致，以实现 SQL Server 中的本机关系数据访问，并鼓励其他人也这样做。 他们进一步表示，v11/SQL Server 2012 之后的 SQL Server 版本将主动不支持 OLE DB！
此消息一出，引发了轩然大波的抗议。 人们不明白为什么微软突然放弃一项他们花了多年时间才让他们投入的技术。 此外，SSAS/SSRS 和 SSIS 是与 SQL Server 密切相关的 MS 编写的应用程序，它们完全或部分依赖于 OLE DB。 另一个抱怨是 OLE DB 具有某些理想的功能，但似乎不可能移植回 ODBC — 毕竟，OLE DB 有许多优点。
2017 年 10 月，微软态度软化，官方未弃用的 OLE DB。 他们宣布即将推出新驱动程序 (MSOLEDBSQL)，该驱动程序将具有 Native Client 11 的现有功能集，还将引入多子网故障转移和 TLS 1.2 支持。 司机于 2018 年 3 月被释放。
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:
away from ODBC;
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.
• 2011 年 8 月：Microsoft 弃用 OLE DB (Microsoft 正在与 ODBC 保持一致以实现本机关系数据访问)
• 2017 年 10 月：Microsoft 取消 OLE DB （宣布推出适用于 SQL Server 的 OLE DB 驱动程序的新版本)
• 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)
ODBC 仅适用于关系数据库，它不能用于非关系数据库，例如 Ms Excel 文件。 Olebd 可以做一切事情。
ODBC works only for relational databases, it can't works with non-relational databases such as Ms Excel files. Where Olebd can do everything.
要知道M$为什么发明OLEDB，就不能将OLEDB与ODBC进行比较。 相反，您应该将 OLEDB 与 DAO、RDO 或 ADO 进行比较。 后者很大程度上依赖于 SQL。 然而，OLEDB依赖于COM。 但 ODBC 已经存在很多年了，因此有一个 OLEDB-ODBC 桥来解决这个问题。 我认为 M$ 发明 OLEDB 时有一个大局。
To know why M$ invents OLEDB, you can't compare OLEDB with ODBC. Instead, you should compare OLEDB with DAO,RDO, or ADO. The latter largely relies on SQL. However, OLEDB relies on COM. But ODBC is already there many years, so there's a OLEDB-ODBC bridges to remedy this. I think there's a big picture when M$ invents OLEDB.
在非常基本的层面上，这些只是针对不同数据源（即数据库）的不同 API。 OLE DB 更新且可以说更好。
您可以在 Wikipedia 中了解有关两者的更多信息：
即，您可以使用 ODBC 驱动程序或 OLE DB 驱动程序连接到同一数据库。 这些情况下数据库行为的差异就是您的书所提到的。
On a very basic level those are just different APIs for the different data sources (i.e. databases). OLE DB is newer and arguably better.
You can read more on both in Wikipedia:
I.e. you could connect to the same database using an ODBC driver or OLE DB driver. The difference in the database behaviour in those cases is what your book refers to.
两者都是数据提供者（您的代码将使用其与数据源通信的 API）。 Oledb 于 1998 年推出，旨在替代 ODBC（于 1992 年推出）
Both are data providers (API that your code will use to talk to a data source). Oledb which was introduced in 1998 was meant to be a replacement for ODBC (introduced in 1992)
我不确定所有细节，但我的理解是，OLE DB 和 ODBC 是两个 API，可用于连接到各种类型的数据库，而无需处理每个数据库的所有实现特定细节。 根据关于 OLE DB 的维基百科文章，OLE DB是 Microsoft 的 ODBC 继承者，提供了一些 ODBC 可能无法实现的功能，例如将电子表格作为数据库源进行访问。
I'm not sure of all the details, but my understanding is that OLE DB and ODBC are two APIs that are available for connecting to various types of databases without having to deal with all the implementation specific details of each. According to the Wikipedia article on OLE DB, OLE DB is Microsoft's successor to ODBC, and provides some features that you might not be able to do with ODBC such as accessing spreadsheets as database sources.
在微软网站上，它显示原生OLEDB提供程序直接应用于SQL服务器，还有另一个OLEDB提供程序称为OLEDB Provider，用于ODBC访问其他数据库，例如Sysbase、DB2等。OLEDB Provider下有不同种类的组件。 有关详细信息，请参阅 MSDN 上的分布式查询 。
At Microsoft website, it shows that native OLEDB provider is applied to SQL server directly and another OLEDB provider called OLEDB Provider for ODBC to access other Database, such as Sysbase, DB2 etc. There are different kinds of component under OLEDB Provider. See Distributed Queries on MSDN for more.