How to explain why ODBC requires a database driver

A month ago, I was showing a buddy how use .NET to query MySQL. It worked just fine, but he didn't understand why we needed a "driver" for MySQL in conjunction with ODBC. He though ODBC would be enough.

When he asked me why, I did my best, 开发者_运维知识库but my answer came up short.

What is the correct explanation for this question?

The reason you need a driver is that ODBC is not in itself a driver for talking to databases: it's an abstraction and an API.

Essentially it is an adaptor layer designed to make all databases appear the same to the client application: you talk to the ODBC library, and it asks the driver manager to fulfill your request using the correct database (based on your connection string).

In order for your database brand to be compatible with ODBC therefore it must provide a driver which implements the required API in order that it can service requests from clients using the ODBC functions.

As has already been said, ODBC is an API and an abstraction layer, not a piece of software, per se.

However, the previous response implies that ODBC drivers come only from database vendors, and that's inaccurate. There are many third-party sources of ODBC drivers, including my own employer.

A number of database vendors include ODBC drivers with their engines as "checkbox items," so they can claim ODBC support, but those bundled drivers are minimal implementations and/or crippled in some way, to encourage "native" application development as the "superior" option. Indeed -- ODBC drivers from database vendors often perform far less well than ODBC drivers from third-party vendors, both with intent (because if database client applications are written to the database's native API, their users are locked-in to that database engine) and without (many database vendors simply don't understand enough about the nuances of the database-agnostic ODBC API to properly map it to their database-specific "native" API). Such lock-in isn't really in the database vendor's long-term best interests (in fact, they were major participants in the original development of ODBC), but short-term business goals sometimes override long-term vision.

My employer, OpenLink Software, long ago recognized that there was a need to compare performance across different databases, ODBC drivers, operating systems, and DSN connection attributes, and created an open-source benchmark utility, OpenLink ODBC Bench. This tool is cross-platform, and simulates TPC-A and TPC-C testing within your own environment, to see just which components are best suited to your needs. As an open source project, all tests may be inspected to ensure there is no skewing in any direction, and they may also be extended and adapted as needed for your specific deployment.

Of course, this all ignores the question of why you would use an ODBC Driver from .NET (which requires use of a bridging ADO.NET Provider for ODBC Data Sources, and thus an extra API translation for the double-abstraction), instead of going directly through an ADO.NET Provider for MySQL...

I see that the thread is bit old but i wanted to get any response on the view of a standard odbc driver for all DBMSs. I know that we need separate drivers for each DBMSs. Is it possible to have a standard ODBC driver which can be used seemlessly across any databases engines. I dont see a big difference between different drivers in terms of its ability to do something quite different than the other