Some applications rely heavily on databases. When the developer starts on their application they need to consider which database they will use – a product from a commercial vendor such as Oracle or Microsoft, or something free like PostgreSQL or indeed the very popular MySQL. It can happen however that at a later point in time the developer wants to change the database they use, and this would typically mean that the developer must extensively change the entire code base for the application to make their application compatible with the new database. It requires a lot of work and to prevent this type of “re-development” ODBC was created.
The history of ODBC
Open database connectivity or ODBC is a standard method to access databases which works as a SQL API. It was originally developed in 1992 and the purpose of this was simply to enable DBMS (database management software) to work.
Interestingly, ODBC was created by Microsoft and was included with SQL Standard from 1995 onwards. The fact that Microsoft included ODBC in SQL Standard contributed to the fact that ODBC has become so popular. Nowadays you can find an ODBC version for almost every type of operating system.
Understanding how ODBC works
The prime reason for the existence of ODBC is to enable developers to access data through any application, no matter which DBMS is used for the management of a database. So, ODBC is fully independent of platforms because of the way it was designed: this makes it distinct from all other database systems, operating systems and programming languages.
A special mechanism is used to make sure applications can access a DBMS via ODBC. There are different ways in which to implement ODBC, but typically it contains a couple of applications, a “driver manager” library which sits at the core of ODBC alongside one or more drivers for databases. Driver Managers have a key role in interpreting the queries that come in from an application.
Driver Managers use the details in the database drivers to generate DBMS-specific instructions. These database drivers are a middle layer that sits between applications, and the DBMS they need to interact with. You can basically see it as translation: application queries are translated into a type of command that can be understood by the DBMS that hosts the data.
Note that both the database and the application that needs data from that database must be compliant with ODBC in order for an ODBC implementation to be feasible. So, the application needs to be able to send ODBC commands, and on the other side the DBMS must be able to understand ODBC commands.
ODBC is very modular which gives the developers that use it a lot of freedom when creating each of the components used in an ODBC implementation. It makes it easy for a developer to code an application without knowing exactly what type of DBMS will be used to administrate the database on which the application depends.
In the same way, programmers who design database drivers just need to understand how to attach their database driver ODBC’s “driver manager” library. As a result you will find that the ODBC driver library contains drivers for hundreds of different data sources.
The team of developers behind ODBC has worked very hard over the years, and ODBC has been around long enough so that there are now more database sources compatible with ODBC than almost any other database access method in the market. ODBC is now a universal way to access databases, it works with almost all common operating systems and can even access data which is not stored in a relational database, executing data queries on XML and text files too.
iODBC
We mentioned how Microsoft was behind ODBC. iODBC, or independent open database connectivity, was designed to break the link between ODBC and Microsoft by ensuring that iODBC is independent of the operating system used. For example, with iODBC a Linux server can connect to a Microsoft SQL server by converting the typical SQL commands on the fly and returning required data.
JDBC
JDBC, short for Java Database Connectivity, is an API used by the Java programming language which gives Java users the ability to access and manipulate databases, and which defines how clients access databases. When there is no driver available for a database developers can use a JDBC to ODBC bridge, or the reverse – an ODBC to JDBC bridge.
The Java virtual machine includes a JDBC to ODBC driver however in practice many of the independent, third party JDBC to ODBC drivers are recommended as these will significantly outperform the bundled driver.
UnixODBC
iODBC lacked several features so in 1999 UnixODBC was created to compensate as iODBC was being used as the main ODBC solution for Unix at the time. Today almost every Linux distribution will include UnixODBC as it has become extremely popular. UnixODBC has a real advantage in that it uses a GUI (or graphical user interface) for management – whereas in the past the user had to modify the iODBC .ini file in order to make functional changes.
with