SQL: Difference between ODBC and OLEDB

From: http://www.maxi-pedia.com/What+is+the+difference+between+ODBC+and+OLEDB



What is the difference between ODBC and OLEDB?

First, let us take a look at ODBC.

ODBC

ODBC stands for Open Data Base Connectivity, which is a connection method to data sources.

When connecting a tool or application to a database, a data source, also called a Database Source Name (DSN), using an SQL driver or other driver if connecting to other database type, needs to be set up. Then, the application is connected to the database using this DSN.

DSN is a data structure used to describe a connection to a database. A DSN will take the form of a protocol: subprotocol: host: port:. Those are the parameters required to completely specify the connection. The exact format of the DSN will vary depending on your programming language.

ODBC is the method of connectivity that encompases the DNS. Most database systems support ODBC.

OLEDB

OLEDB stands for Object Linking and Embedding Database. It is an application programming interface designed by Microsoft for accessing different types of data stores in a uniform manner.

It is a newer and improved successor to ODBC.

OLEDB is the successor to ODBC, a set of software components that allow a "front end interface" such as a graphical user interface (GUI) based on VB, C++, Access, and others to connect with a back end such as SQL Server, Oracle, DB2, mySQL, etc.

OLEDB components in many cases offer improved performance over the older ODBC.

OLEDB is a different type of data provider that came about with MS’s Universal Data Access in 1996.

OLEDB does not require a DSN.

Additional information

More information can be found at the Microsoft website:

http://www.microsoft.com/data

From: http://www.dotnetspider.com/forum/23743-what-e-difference-between-OLEDB-ODBC-conc.aspx

ODBC-it is designed for connecting to relational databases. (Sql Server, Oracle etc)
However, OLE DB can access relational databases as well as nonrelational databases. (Oracle, Sql-Server, Excel, raw files, etc)

There is data in your mail servers, directory services, spreadsheets, and text files. OLE DB allows SQL Server to link to these nonrelational database systems. For instance, if you want to query, through SQL Server, the Active Directory on the domain controller, you couldn’t do this with ODBC, because it’s not a relational database. However, you could use an OLE DB provider to accomplish that.

In summary, The key difference between OLE DB and ODBC is that OLE DB can provide connection to data stored in non-relational format.

From: http://stackoverflow.com/questions/103167/what-is-the-difference-between-ole-db-and-odbc-data-sources

According to this book (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).

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s