What is ODBC?

Open Database Connectivity (short: ODBC) is a standardized interface for SQL databases, which allows one to access different database management systems and execute queries. The goal is to have a programming interface that works independently of the selected database and can thus be connected to various existing databases.

How does Open Database Connectivity work?

ODBC was first introduced in 1992. At that time it was difficult to establish communication between a database and an application because no standardized tool was available.

The development of ODBC then made it possible to program database-independent applications. For this purpose, the drivers are used, which differ from database to database. They translate the general query into the specific language of the relational database. Therefore, a separate driver must also be installed for each data store.

This allows the programmer to make a standard Open Database Connectivity data request, which is then translated for the database in use, such as MySQL, depending on the driver. Furthermore, the access rights for the memory are also stored in the driver, such as the user name and password.

In which Architecture is ODBC used?

Suppose an application uses data from two different relational databases, such as MySQL and PostgreSQL. Both data stores contain information that only becomes valuable once it is merged. However, it is not possible to store both tables in the same database.

Instead of laboriously copying the information from one table and joining it to the other database, ODBC can be used to query and merge the data directly. This is done by installing the appropriate drivers on the database servers. Then, using SQL queries, the information can be read and merged as desired. From the interface, the merged data can then be further processed or forwarded.

What are the Advantages and Disadvantages of using it?

ODBC offers developers the possibility to program applications that can be used generally because they are independent of the concrete database. As long as the memory follows the relational concept, the interface is usable. This is especially advantageous if one wants to program software and offer it for sale.

At the same time, one can access the same Open Database Connectivity interface with several, different applications and is thus more flexible. However, this high flexibility has the disadvantage that the queries sometimes load comparatively slowly, which delays the execution of transactions. Therefore, its use is rather unsuitable for high-performance applications where loading time plays a major role.

In addition, some preparatory work is necessary to make good use of Open Database Connectivity. For each data source, the appropriate drivers must be installed, which differ for individual data sources. In complex system landscapes, this can sometimes be a complicated matter and the drivers must also be updated regularly.

Furthermore, the drivers are based on Microsoft and Windows. Although drivers for Linux and macOS platforms have been added in the meantime, problems may occur in individual cases, since ODBC was developed and optimized for Windows.

What is OLEDB?

OLEDB was also developed by Microsoft and provides an Application Programming Interface (short: API) for databases. It offers the same possibilities as ODBC and is therefore also considered the successor of Open Database Connectivity. However, it fetches the data already on a higher level and makes it possible thereby also non-relational databases to query, which is seen as a large advantage opposite ODBC.

As soon as the application accesses multiple databases, which are not only relational, OLEDB should be used.

This is what you should take with you

ODBC is a standardized interface for relational databases, which allows programming applications that do not depend on the specific database technology.
This interface offers programmers the possibility to combine several databases and their information in a simple and uncomplicated way.
However, its use can lead to long query times than direct access. In addition, the systems must be equipped with the appropriate drivers for the databases beforehand.