Today we have a guest editorial from Phil Factor
I’d like to put in a good word for one of the neglected pillars of Microsoft’s database connectivity, ODBC. Even if you are securely cocooned in the .NET environment, it is so wonderful to be able to occasionally connect to, and access, a whole range of other data sources such as Clipper, Ingres, Lotus Notes, ADABAS or LDAPs with a Call-Level interface that is an international SQL Standard. It allows you to use the same code to access outlandish data sources on IBM i5/OS, Linux, Mac OS X systems, or to host a SQL Server database within a Linux application environment. ODBC is fast. After all, the fastest way to get data into SQL Server is BCP which uses ODBC. (via a special BCP API). Where LINQ promises generic connectivity via providers, ODBC delivers, and has done for years. Deep in the bowels of ADO.NET, and the SQL Native Client, there still lies the great ODBC 3.51 system, if you want it. Every copy of windows that is shipped has it. Why not?
“Don’t try that Phil’, I hear you think ‘What about the curse of obtuse ODBC connection strings?, What was so good about ODBC-standard SQL? Why did none of the Desktop Database Drivers ever seem to work reliably?”
“OK. You can actually be protected from connection strings. I’ve seen apps do this well; And those horrid messages from Jet engines? Whereas the support in SQL Server for ODBC is excellent, the Microsoft drivers, with the notable exception of SQL Server's, have always been poor. Third-party providers have come up with a rich source of excellent drivers, but the Microsoft ‘desktop database drivers’ only just manage the minimum grammar. ODBC conformance comes in three flavours: minimum, Core and Extended. If we had ODBC drivers that were implemented as standard to at least Core level, we’d love ‘em. The Microsoft ‘desktop database drivers’ only just manage the minimum grammar.”
I picked up my old trusty, well-thumbed, 1992 ODBC 2.0 Programmers Reference and SDK guide the other day. It was wonderful to read. Even with hindsight, there was nothing wrong with the vision, and it is uncomfortably close to LINQ. Being able to get the metadata from the ODBC Database? No worries! Even the constraints? Sure. An extended driver gave you a powerful interface into the database you were accessing, and a visionary API. What about a standard device-independent dialect of SQL? Yup, all there.
For solving all those connectivity problems that beset us in the everyday life of database applications, there is a lot to be said for ODBC. Sure many of the drivers need to be developed to handle the newer data types; OK, it doesn’t supply enough information about the schema for complex applications. Tools that only deal with ODBC information will never rival tools that run queries on the native database. These are surely problems that can be solved. With ODBC/JDBC, we are close to an effective universal standard for data access across platforms, applications and data sources. It seems something that is worth achieving.