Feb. 23, 2011, 11:32 a.m.
posted by novinick
How Does ODBC Work?
ODBC is much more than a document. On one hand, it is the definition of a CLI, a set of definitions and rules. On the other hand, it is software. First, we describe how ODBC, as implemented by Microsoft, works.
Logically, ODBC consists of two layers; see Figure. These two layers are between the application and a number of databases that the application can access. In ODBC, these are called data sources. A data source can be MySQL, DB2, Oracle, or Microsoft Access, for example.
1. ODBC consists of two layers
The application "talks" to the first layer of ODBC, called the ODBC driver manager. This module can be seen as a part of Windows itself. The job of the driver manager can be compared to that of the printer manager under Windows.
The working of ODBC resembles printing software. Therefore, let us first take a closer look at the job of the printer manager. If we want to print a document, we send it to the printer manager with an instruction that it should be printed by a specific printer. Because each printer is differentone might be a black-and-white printer, whereas another supports color; one might have a resolution of 600 dots per inch, and another 1200a special driver has been developed for each printer. Although internally they differ considerably, the printer manager considers these drivers to be the same. Indeed, the printer manager has been set up to hide these differences from the users. In a sense, this module acts as a switchboard.
The ODBC driver manager has a comparable task. For example, if an application wants to access an MySQL database, the driver manager links to the appropriate driver; this is the second layer of ODBC. This driver has been developed specifically to access MySQL databases. If an application wants to access DB2, a driver is linked that has been developed specifically for DB2. The power of the drivers and whether the database is located at the other end of the world is transparent to the driver manager and, therefore, to the application. This kind of detail is hidden by drivers. Although each driver looks the same to the driver manager, the internal processing is different for each driver.
Via the Windows Control Panel, you can check which drivers have been installed on your own machine. Figure shows a list with installed ODBC drivers. As an example, the first driver offers access to DB2, the second to dBASE files, and the fourth to Microsoft Access databases.
2. List with installed ODBC drivers
Drivers can be implemented in different ways. Figure shows a number of possible implementations. In the first implementation, all software components run on the same machine. The application communicates with the ODBC driver manager, which, in turn, calls an ODBC driver. The latter communicates directly with a database server. In fact, the ODBC driver acts as an entry to the database server.
3. Various implementations of ODBC
In the second implementation, the ODBC driver and the database server have been bundled together to form one layer of software that processes the calls of the ODBC API functions plus the SQL statements. Drivers that, for example, want to access data that is stored in spreadsheet files are built in this way because no available database server can be used. The third alternative is only a variation of the second. The difference is that the data is stored not locally, but on a remote file server.
The fourth implementation is important for client/server environments. The database server (together with the database) is located on a remote machine. To access that machine, middleware has been installed. These products have been optimized to send SQL statements through a local network. Most middleware products support their own CLI. The ODBC driver for this form of implementation translates the ODBC CLI to the product-dependent CLI. The driver itself does not know that a remote database server is accessed because it is completely shielded from this. For the fifth form, the ODBC driver and the middleware component on the client side have been brought together into one product.
However, no matter how the ODBC driver works, it is completely transparent to the ODBC driver manager and to all applications.
It is important to know that Microsoft is not the only company developing ODBC drivers; other companies are also doing this. There are even companies that supply drivers but not a database server product.