OLE DB和ODBC数据源之间有什么区别？

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

我正在阅读有关pivotcache的MS Excel帮助文章,并想知道OLE DB和ODBC源的含义

...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.

...您应该使用CommandText属性而不是SQL属性,该属性现在主要用于与早期版本的Microsoft Excel兼容。如果同时使用这两个属性,则CommandText属性的值优先。

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

对于OLE DB源,CommandType属性描述CommandText属性的值。

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

对于ODBC源,CommandText属性的功能与SQL属性完全相同,设置属性会导致数据刷新...

I really appreciate your short answers.

我非常感谢你的简短回答。

11 个解决方案

#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.

根据ADO:由阿森利媒体于2001年出版的Jason T. Roff出版的“ActiveX数据对象”(这里的优秀图表),他正好说出了莫兹拉所说的话。

(directly from page 7 of that book)

(直接来自那本书的第7页)

• ODBC provides access only to relational databases

ODBC仅提供对关系数据库的访问

• OLE DB provides the following features
  • Access to data regardless of its format or location

  无论格式或位置如何,都可以访问数据

  • Full access to ODBC data sources and ODBC drivers

  完全访问ODBC数据源和ODBC驱动程序

OLE DB提供以下功能访问数据,无论其格式或位置如何完全访问ODBC数据源和ODBC驱动程序

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

因此,OLE DB似乎与基于SQL的数据源THRU ODBC驱动程序层交互。

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).

我不是100%确定这张图片是正确的。我不确定的两个连接是通过ADO C-api的ADO.NET,以及通过ODBC到基于SQL的数据源的OLE DB(因为在此图中作者没有通过ODBC提供OLE DB的访问,我相信是一个错误)。

#2

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

ODBC: - 仅适用于关系数据库(Sql Server,Oracle等)

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

OLE DB: - 适用于关系数据库和非关系数据库。 (Oracle,Sql-Server,Excel,原始文件等)

#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)

ODBC是大多数软件供应商支持的技术无关的开放标准。 OLEDB是来自COM时代的技术特定的Microsoft API(COM是.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.

在某些时候,愿意与微软数据消费者兼容的各种数据供应商(例如Oracle等)为其产品开发了OLEDB供应商,但在大多数情况下OLEDB仍然是仅限微软的标准。现在,大多数Microsoft数据源都允许ODBC和OLEDB访问,主要是为了与旧版ODBC数据使用者兼容。此外,还存在用于ODBC的OLEDB提供程序(包装器),如果有人愿意,它允许使用OLEDB访问ODBC数据源。

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).

就功能而言,OLEDB实质上比ODBC更丰富,但遭受一环到规则的所有综合症(过于通用,过于复杂,无意见)。

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

在非Microsoft世界中,基于ODBC的数据提供者和客户端被广泛使用,而不是去任何地方。

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).

微软泡沫OLEDB内部正在逐步淘汰原有的.NET API,无论该数据源的原生传输层是什么(例如,MS SQL Server的TDS)。

#4

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.

在非常基础的层面上,这些只是不同数据源(即数据库)的不同API。 OLE DB更新,可以说更好。

You can read more on both in Wikipedia:

您可以在*上阅读更多内容:

1. OLE DB
2. ODBC

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.

即您可以使用ODBC驱动程序或OLE DB驱动程序连接到同一数据库。在这些情况下,数据库行为的差异是您的书所指的。

#5

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和OLE DB是两种竞争数据访问技术。特别是关于SQL Server,微软已经将它们作为首选未来方向推广 - 尽管在不同的时间。

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.

ODBC是一种用于访问表格式数据的行业标准接口。它主要是为数据库开发的,并在记录集中呈现数据,每个记录集合成一组字段。每个字段都有自己的数据类型,适合其包含的数据类型。每个数据库供应商(Microsoft,Oracle,Postgres,...)都为其数据库提供ODBC驱动程序。

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.

还有用于对象的ODBC驱动程序,尽管它们不是数据库表,但它们非常相似,因此以相同的方式访问数据非常有用。示例包括电子表格,CSV文件和列式报告。

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.

OLE DB是一种用于访问数据的Microsoft技术。与ODBC不同,它包含类似表格和非类似表格的数据,例如电子邮件,网页,Word文档和文件目录。但是,它是面向过程而不是面向对象的,并且被认为是开发数据源访问的一个相当困难的接口。为了解决这个问题,ADO被设计为OLE DB之上的面向对象层,并提供更简单,更高级 - 但仍然非常强大 - 的工作方式。 ADO的巨大优势在于,您可以使用它来操作特定于给定类型的数据源的属性,就像您可以使用它来访问适用于所有数据源类型的属性一样容易。您不仅限于一些令人不满意的最低共同标准。

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).

虽然所有数据库都有ODBC驱动程序,但它们并不都具有OLE DB驱动程序。但是,如果要以类似OLE DB的方式访问它们,则可以使用OLE和ODBC之间的接口。此接口称为MSDASQL(用于ODBC的Microsoft OLE DB提供程序)。

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.

由于SQL Server是(1)由Microsoft制作,(2)Microsoft数据库平台,因此ODBC和OLE DB都很适合它。

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.

由于所有其他数据库平台都有ODBC接口,因此Microsoft显然必须为SQL Server提供一个。除此之外,DAO是Microsoft Access中的原始默认技术,它使用ODBC作为与所有外部数据源通信的标准方式。这使得ODBC接口成为必要条件。与SQL Server 2000一起发布的SQL Server版本6 ODBC驱动程序仍然存在。已发布更新版本以处理随后发布的新数据类型,连接技术,加密,HA / DR等。截至09/07/2018,最新版本是v13.1“用于SQL Server的ODBC驱动程序”,于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:

这是微软自己的技术,它们在2002年至2005年间大力推广,以及随附的ADO层。他们显然希望它成为首选的数据访问技术。 (他们甚至将ADO设置为访问Access 2002/2003中数据的默认方法。)然而,最终显而易见的是,由于多种原因,这种情况不会发生,例如:

1. The world was not going to convert to Microsoft technologies and away from ODBC;

世界不会转换为Microsoft技术而是远离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;

DAO / ODBC比ADO / OLE DB快,并且也完全集成到MS Access中,因此不会自然死亡;

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.

微软正在开发的新技术,特别是ADO.NET,也可以直接与ODBC对话。 ADO.NET也可以直接与OLE DB对话(从而使ADO处于死水状态),但它(不像ADO)完全依赖于它。

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!

出于这些原因和其他原因,Microsoft实际上不赞成使用OLE DB作为v11之后的SQL Server版本的数据访问技术(SQL Server 2012)。在此之前的几年里,他们一直在制作和更新SQL Server Native Client,它支持ODBC和OLE DB技术。然而,在2012年末,他们宣布他们将与ODBC协调SQL Server中的本机关系数据访问,并鼓励其他人也这样做。他们进一步表示,v11 / SQL Server 2012之后的SQL Server版本将主动不支持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.

这一消息引发了一场*风暴。人们无法理解为什么MS突然贬低他们花费数年时间让他们承诺的技术。此外,SSAS / SSRS和SSIS是MS编写的与SQL Server密切相关的应用程序,它们全部或部分依赖于OLE DB。另一个抱怨是OLE DB具有某些可取的功能,似乎无法移植回ODBC - 毕竟,OLE DB有许多优点。

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.

2017年10月,微软提出了正式撤销和正式弃用的OLE DB。他们宣布即将推出新驱动程序(MSOLEDBSQL),它将具有Native Client 11的现有功能集,并且还将引入多子网故障转移和TLS 1.2支持。该驱动程序于2018年3月发布。

#6

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)

两者都是数据提供者(您的代码将用于与数据源通信的API)。 1998年推出的Oledb旨在取代ODBC(1992年推出)

#7

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.

我不确定所有细节,但我的理解是OLE DB和ODBC是两个可用于连接到各种类型数据库的API,而不必处理每个数据库的所有实现特定细节。根据*关于OLE DB的文章,OLE DB是Microsoft的ODBC的继承者,并提供了一些您可能无法使用ODBC的功能,例如访问电子表格作为数据库源。

#8

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.

在Microsoft网站上,它显示原生OLEDB提供程序直接应用于SQL服务器,另一个OLEDB提供程序称为OLEDB Provider for ODBC以访问其他数据库,例如Sysbase,DB2等。在OLEDB Provider下有不同类型的组件。有关更多信息,请参阅MSDN上的分布式查询。

#9

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

•2011年8月:Microsoft弃用OLE DB(Microsoft正在使用ODBC与本机关系数据访问对齐)

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

•2017年10月:Microsoft不推荐使用OLE DB(宣布SQL Server的OLE DB驱动程序的新版本)

#10

ODBC works only for relational databases, it can't works with non-relational databases such as Ms Excel files. Where Olebd can do everything.

ODBC仅适用于关系数据库,不适用于非关系数据库,如Ms Excel文件。 Olebd可以做任何事情。

#11

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.

要知道M $为什么会发明OLEDB,你无法将OLEDB与ODBC进行比较。相反,您应该将OLEDB与DAO,RDO或ADO进行比较。后者很大程度上依赖于SQL。但是,OLEDB依赖于COM。但是ODBC已经有很多年了,所以有一个OLEDB-ODBC桥来解决这个问题。当M $发明OLEDB时,我认为有一幅大图。