Many organizations and developers have high hopes for ODBC, based on wild vendor claims. ODBC is supposed to make applications more portable without sacrificing functionality, performance, or stability. ODBC is also supposed to reduce programmer training and programming efforts, as developers have to learn only one application programming interface (API) in order to access any type of database. But, like all things that are too good to be true, ODBC is having a hard time living up to its reputation.
Oracle, Sybase, DB2, and Access have far more differences than similarities. In fact, they share very few features. Basic SQL syntax may be the same, but each has many SQL extensions and even those SQL statements that are the same may return different results and definitely exhibit different performance characteristics. Each RDBMS also has its own distinctive set of features that go far beyond simple SQL. Every DBMS implements differently (or not at all) capabilities such as transaction management, triggers, stored procedures, built-in functions, data types, error messages and codes, large data type API functions, cursors, and system catalogs.
ODBC does not directly support all functions in all database servers. For this reason, ODBC supports an "escape clause" that allows programs to send a direct API call to the target DBMS. By invoking it, the program immediately loses portability, because the program is now making direct calls to the DBMS API. The net result is a program consisting of a hodgepodge of ODBC and native DBMS API commands.
To code a "portable" application, a project team would have to have in-depth knowledge of all of the differences between all of the target DBMSs. This is a substantial undertaking -- one that is beyond the means of most projects. Even if a project team had sufficient knowledge, it would then be necessary to figure out a design that would work across multiple DBMSs. This too would be a very substantial effort and there is no assurance that the final design would lead to a practical application. An application that is designed using the least-common-denominator approach of ODBC would have to give up so much functionality and would be so poorly optimized for any given RDBMS that it may have no practical value. It would be like designing a dwelling for use in a mountain range, a desert, a forest, and an urban area. An architect would probably come up with a tent. The problem is that not many people in a city want to live in a tent.
Even if you write a portable application, the problem of keeping it portable persists. DBMS and tool vendors constantly change features in their products. A project team would have to find a way to make the code flexible enough to include these changes in the design as each DBMS evolves, and be able to introduce these changes without impacting the overall portability of the design. This effort would be so great that it could be a never-ending task.
Each time vendors release a new version of their DBMS, they add functions and API calls to support these new functions. ODBC or the corresponding ODBC drivers may not support these functions. Sometimes vendors revamp or change commands within their APIs in order to streamline application development and increase performance. Sybase and Oracle made such changes in the latest releases of their products, and developers must put in the necessary effort to understand the effects of these changes on their programs and program designs.
The ODBC drivers themselves add a degree of uncertainty and instability to the programming process. Some ODBC drivers are developed by DBMS vendors and others are built by third-party vendors. Drivers have various ODBC conformance levels and are programmed depending on how the driver developer interpreted the ODBC specification. Each driver has its individual behavioral characteristics -- and bugs -- which are uncovered by trial and error. Working through each driver can be an exhausting and time-consuming process that adds to the total development time and cost.
Interestingly, Microsoft representatives recently told me that ODBC was not being marketed as a solution for mission-critical applications. I pointed out that Visual Basic data controls and Access use ODBC, which means that, by their own definition, Visual Basic is not suitable for mission-critical applications. One alternative is to use Microsoft SQL Server Programmerıs Toolkit for Visual Basic to directly access SQL Server without ODBC, but under this scenario you must build custom data controls.
There is also a question regarding RDBMS vendor support for ODBC. Oracle, Sybase, Borland, and IBM have yet to release ODBC drivers. Oracle has instead released Oracle Glue, which is the companyıs own high-level API for Windows programming.
RDBMS vendors are balking because it would be extremely uncomfortable for them if Microsoft controlled the RDBMS API and thereby controlled the design of future versions of their RDBMS. Microsoft controlling the RDBMS API is as illogical as Oracle controlling the Windows API. RDBMS vendors are constantly developing new features and new APIs to support these features, and they will not allow Microsoftıs ODBC specification to restrict them. The competitive, free marketplace requires vendors to build into their products unique features and capabilities so they can continue to support their existing user bases and attract new customers.
The danger for ODBC users is that they may be locked out of future functions, either because the RDBMS vendors delay support for ODBC or because the ODBC specification cannot be revised fast enough to keep up with new RBDMS functionality. This problem will become more obvious as DBMS vendors begin to introduce new "object-oriented" features that will be difficult or impossible to support within the ODBC framework.
ODBC is not a free lunch. ODBC forces project teams to deal with added layers of complexity which they definitely do not need, given the already over-complicated world of client/server. Added layers of complexity mean more points of potential failure and more vendors who inevitably point fingers at each other when a problem occurs in client/server connectivity code.
Most vendors will concede that ODBC has many problems but they insist that ODBC is a new technology -- a technology that needs time to "mature." But experimenting with new technology and hoping that it matures can be very risky. Developers already have very tight project deadlines and enough problems stabilizing client/server applications without having to deal with "maturing" technologies. Even if ODBC does mature, it will still provide only very limited benefits -- mostly in cases where users are generating simple SQL queries only.
Some may argue that it is not always possible to go direct; that ODBC -- like airline trips -- makes it possible to take indirect flights where direct flights are not available. But there is a reason most travelers prefer direct flights. Stopovers take extra time. Things can get lost between segments. And it is possible to get held over indefinitely -- especially if you go through Denver in the winter. My recommendation is to take direct flights whenever possible and avoid getting sidetracked -- or sidelined -- by ODBC.