Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

A Good Word for ODBC

By Phil Factor,

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.

Total article views: 251 | Views in the last 30 days: 1
Related Articles

SQL Server 2005 ODBC Driver

Is there a "standard 2005 driver" as well as the "SQL Native Client" ODBC driver?


SQL 2005 Standard Server connection to AS400

Not able to connect to AS400 system using any driver


Comparison of Oracle Drivers

Oracle is usually a dirty word in the SQL Server community, but like it or not, lots of data reside...


Strict Database Standards and Conventions

In this article by Mattias Fagerlund, he shows you how he employs strict database naming standards a...


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones