What is ODBC and OLEDB – Interview Question

Open Database Connectivity (ODBC) :

In computing, Open Database Connectivity (ODBC) provides a standard software interface for accessing database management systems (DBMS). The designers of ODBC aimed to make it independent of programming languages, database systems, and operating systems. Thus, any application can use ODBC to query data from a database, regardless of the platform it is on or DBMS it uses.

ODBC provides a standard software API method for accessing both relational and non-relational DBMS. It was developed by the SQL Access Group in 1992 in order to facilitate easier communication between applications and databases across computing platforms. Prior to its creation, if an application needed the ability to communicate with more than a single database, it would have to support and maintain an interface for each. ODBC provides a universal middleware layer between the application and DBMS, allowing the application developers to only have to learn a single interface, nor do they have to update their software if changes are made to the DBMS specification, only the driver needs updating.

Object Linking and Embedding, Database (OLE DB):

OLE DB (Object Linking and Embedding, Database) is an API designed by Microsoft for accessing data from a variety of sources in a uniform manner. It is a set of interfaces implemented using the Component Object Model (COM). It was designed as a higher-level replacement for, and successor to, ODBC, extending its feature set to support a wider variety of non-relational databases, such as object databases and spreadsheets, powerpoint that do not necessarily implement SQL.

OLE DB separates the data store from the application that needs access to it through a set of abstractions that include the datasource, session, command and rowsets. This was done because different applications need access to different types and sources of data and do not necessarily want to know how to access functionality with technology-specific methods. OLE DB is conceptually divided into consumers and providers. The consumers are the applications that need access to the data, and the provider is the software component that implements the interface and therefore provides the data to the consumer.