ODBC - Better than you think

"Oh no", you say, "it's ODBC! That horrible, slow and buggy database API! Who in their right mind would use that!"

And I am afraid that I have a slightly different opinion on ODBC than that. I actually think ODBC is pretty good as an API. As far as implementation of the ODBC driver has been for MySQL in particular, well let's be honest and admit that there has been ups and downs, but what is available right now is good and solid, and this has been that case for a while, due to the great team working on MySQL Connector/ODBC right now.

And if you don't know or use ODBC, let me tell you a few facts about it and tell you about some myths:

• ODBC isn't "slow" or "fast" or "buggy". ODBC is just a specification for an API. The implementation may be slow or fast or buggy or all of those things, but that is a different thing.
• If there is exists a "native" API, ODBC still doesn't have to be slower. For example the native API ight well BE ODBC. Also, an ODBC driver may well work around the native API and make thing faster than the C API that is usually the native one (OK, I know, with MySQL, the API is really the "wire protocol", at least that is the notion in some cases, but the C client API implements that more or less completely).
• Myth: "Using ODBC will mean my application is database independent". This is not true, really. ODBC is a useful API for building database independent applications, but that doesn't mean that ODBC will do the job for you, but it helps you doing the job.
• Myth: "ODBC is one of those horrible Microsoft proprietary technologies". This isn't true either actually. Fact is, Microsoft has been pretty good here. ODBC is an extended implementation of the SAG (SQL Access Group) CLI (Call Level Interface) and ODBC has always been compliant with SAG CLI at some level. (These days, SAG no longer exists as a separate entity, and it became part of X/Open, which is now Open Group).
  
• Myth: "ODBC is a Windows only technology". This just isn't true, either. Although I have to admit that I had rather see the ODBC layers for Linux etc be based on SAG CLI than on ODBC, but the difference isn't that big actually.
• Myth: "The Driver Manager is the component that is screwing things up". This may well be true, it depends on the Driver Manager you use. But an application can, if it so wishes, ignore the Driver Manager. The reason for this is that Microsoft, when it created ODBC, introduced the Driver Manager, but still wanted to stay aligned with SAG CLI, who said nothing about this actual implementation, it just defined the API. So for Microsoft to align with SAG CLI, but still having an environment where the applications and the drivers were aligned with SAG CLI, despite having three layers, made the DM API the same as the Driver API. There are a few exceptions, mainly that the DM makes things easy for you when it comes to managing different ODBC / SAG CLI versions, so that the DM may map one call that has changed to another call. But excluding the added benefit of the DM, you can link your applications directly against the driver, should you so wish.
• Myth: "ODBC is so GUI oriented with the automatic dialogs and stuff, it's not useful for a server application". This is also wrong, it is not difficult at all to create command-line based ODBC applications, that will not pop up any dialog at all. But then you have to provide any information to ODBC in some other way of course.
  

Having said all these things, and I have to admit that I actually like ODBC, there are some issues with it. One such issue is that the ODBC API is to an extent assuming that there is a cursor based database in the background. ODBC may also assume a simple file-based database, like access. But in the case of MySQL, the means of communications is more using complete datasets on the client, than client / server based cursors. The good thing is that the Connector / ODBC developers has done a good job of making this transparent to the end user, so anyone used to using, say, Oracle with ODBC will feel just at home with accessing MySQL using ODBC, despite the fact that the semantics of the underlying are quite different.

An interesting aspect of ODBC is that it provides many information functions, like what is the name of th database system (MySQL), what is the version (5.1.32), how are table names and other object names treated? How is sorting handled? Which SQL functions are available. Etc. These are the functions that used in, say, Excel to access almost any RDBMS through ODBC. But note that Excel, to achieve this, only has help from ODBC in the sense that Excel can know how to handle the different RDBMS systems, Excel itself still has to implement it.

I will write more on ODBC later, and I have a command-line ODBC tool that I plan to publish as Open Source eventually. As usual with me, this is rather simple code, but it is useful I think. Right now it is Windows only, although the code should be easy to port to Linux as there is no GUI code.

Happy databasing and see you at the MySQL User Conference!
/Karlsson