ODBC Rocks!

Fifteen years after its launch, ODBC is a firmly entrenched cornerstone of the software industry. This article explains why and will explore the relationship between Microsoft SQL Server and ODBC and discuss where ODBC may go in the future.

Conceived as a broadly based, multi-platform, multi-database data access technology, ODBC has been an outstanding success. Probably the best known implementation of ISO/IEC 9075-3:2003 SQL Call Level Interface (part 3 of the complete SQL standard), ODBC is included in Windows, MacOS, all major Linux distributions, and is readily available for many Unix versions including AIX, HP-UX, Solaris, and FreeBSD. Even PDAs and smartphones have ODBC!

Though often thought of as an API for C and C++ applications, ODBC is frequently used with other languages. For example, many COBOL applications use ODBC for database access as do dynamic languages such as PHP, Perl, Python and Ruby along with Microsoft Access and other RADs.

Despite being conceptually limited to relational databases, no significant data source lacks an ODBC driver: text files, Excel spreadsheets, ISAMs such as dBase, Paradox, C-ISAM, Btrieve and VSAM-you name it and an ODBC driver is most likely available. For any data source that lacks an ODBC driver there are numerous driver development kits and driver development shops waiting to help you plug that gap! It goes without saying that no respectable relational database lacks an ODBC driver.

ODBC is very popular for custom enterprise application development and is widely supported by market leading ISVs. ERP, CRM, and SCM packages all use ODBC as do query, analysis, reporting and ETL packages as well as productivity applications such as Microsoft Office.

So what makes ODBC popular with different industry factions and why will ODBC remain popular for the foreseeable future?

For the data source owner ODBC is a must. The industry standard APIs required to enable access by developers, third-party tools and application packages are ADO.NET, JDBC, ODBC and OLE DB. Developing drivers for all of these requires significant time, effort, and expertise. What is the best strategy if resources are limited or time to delivery is important? The answer is simple. Implement ODBC first and evaluate requirements for the others afterwards. Why? First, there are bridges from all the other APIs to ODBC, so as soon as ODBC is enabled so are all the others. Second, even ignoring the API bridges, ODBC will provide users of the data source with the widest choice of third-party software since ODBC has been around longest and accumulated the widest selection of tools and applications.

ODBC can, and often is, implemented on top of a proprietary API. In the early days of ODBC, many perceived this to be a weakness in first generation ODBC drivers. Research soon showed that layering ODBC on top of proprietary native APIs usually has minimal performance impact. In some cases ODBC even out-performed proprietary APIs when the driver adopted strategies to overcome weak default behavior in the underlying native API. For some databases, including Microsoft SQL Server, ODBC actually fulfills the role of proprietary native API.

Corporate IT and enterprise developers live in a very heterogeneous environment and have to support an accumulated legacy spanning multiple languages, operating systems, and data sources. There may also be pressures to consolidate and standardize application platforms alongside pressure to modernize existing applications, re-engineer business processes, increase information integration, and introduce business intelligence. Phew! How to juggle these pressures and stay sane?

In data integration and consolidation scenarios from ETL to BI, ODBC’s ubiquity is invaluable in bringing data from both packaged and custom applications together.

The .NET platform is compelling where new development is required, but in many cases timescale, business risk, and overall cost factors favor code re-use and incremental modernization. ODBC has a lot to offer here. Often application developers can recompile existing code for use in .NET applications with the Microsoft Visual Studio 2005 C++ compiler using the /clr compiler switch. This greatly simplifies mixing existing C++ and ODBC business logic with a modernized user interface, which can be developed most efficiently using the .NET Framework. ODBC provides a common API for a diverse spectrum of data sources and operating systems and can access multiple data sources simultaneously. These are essential capabilities for information consolidation, aggregation, and integration whether used in native applications or via the .NET Framework Data Provider for ODBC.

In addition to directly reusing existing business logic, ODBC is also a prime candidate to accelerate conversion from other proprietary APIs. Consider license consolidation via database migration, where it may be more cost effective to convert applications that use proprietary APIs such as DB-Library, CT-Library and OCI to ODBC rather than to rewrite completely for .NET since ODBC is conceptually quite similar to other native code APIs.

ISVs often choose to support multiple databases to increase addressable market and customer appeal. ODBC offers several approaches to this requirement. Above all, ODBC offers a common API that can address multiple data sources and, as seen above, a wide range of available drivers. However, there are differences among data sources and this is reflected in a range of behaviors across different ODBC drivers.

The simplest approach to solving this problem uses a combination of the most restrictive behavior patterns and SQL dialect subset. ODBC offers escape sequences that iron out minor differences among SQL dialects to assist in this approach. Next, if this is insufficient, applications can query ODBC to determine the characteristics of the particular driver and database in use and adapt dynamically to them. An application can also determine the actual driver and database in use and trade off generality to exploit unique characteristics in meeting the most stringent functional and performance demands. ODBC places no restrictions on the statements sent from applications to data sources, so does not suppress the richness of the SQL dialect on a connection.

Some ISVs use an internal data abstraction layer to enable use of different APIs for different databases. ODBC has much to offer, even in this scenario. For some databases, notably Microsoft SQL Server, ODBC is the best performing API available.

System integrators and value-added resellers (VARs) benefit from ODBC in two ways. They can guarantee that whatever the operating platform and other infrastructure, ODBC will be available for all the data sources they will encounter, which simplifies development of a specialist proprietary toolset. Secondly, staff familiar with ODBC can be deployed to satisfy a wider range of customer situations than staff whose skill sets span a narrower spectrum.

ODBC and Microsoft SQL Server

ODBC has broad ongoing appeal across the software industry and remains a key, though often unsung, element of the Microsoft Data Platform. Let me now discuss the relationship between ODBC and Microsoft SQL Server in more detail.

When first launched, DB-Library was SQL Server’s only API for client applications. Microsoft later supplemented the SQL Server API with ODBC then with OLE DB and most recently with ADO.NET. DB-Library has been deprecated due to technical limitations and is today only supported to provide backwards compatibility to legacy applications that have not yet converted to one of the other APIs.

At the time of its release, the SQL Server team at Microsoft believed OLE DB would supersede ODBC. This is no longer the case and ODBC’s future is completely secure. ODBC is more widely used than OLE DB and it is better suited to some key scenarios that I will discuss later in this article.

Both OLE DB and ODBC are true native APIs for SQL Server in that they map API calls directly into SQL Server’s network protocol, Tabular Data Stream (TDS). When Microsoft recommended best practices are followed, ODBC is a very thin wrapper over TDS with no intermediate buffering between network packet buffers and the application. It therefore has excellent performance and scalability characteristics.

ODBC and OLE DB support for Microsoft SQL Server is available in WDAC (Windows Data Access Components), originally known as MDAC (Microsoft Data Access Components), and in Microsoft SQL Server Native Client, a component of Microsoft SQL Server 2005 and later versions. Support in WDAC targets legacy and generic applications that do not exploit the unique features of Microsoft SQL Server 2005 and later versions. Applications that do wish to fully exploit the unique features of Microsoft SQL Server 2005 or later should instead use Microsoft SQL Server Native Client, which enables use of features such as snapshot isolation, database mirroring, query notification and data types such as xml and varchar(max). Microsoft will continue to add support for new SQL Server features to all of the APIs it supports: ADO.NET (via SqlClient), ODBC and OLE DB (via SQL Server Native Client) and JDBC.

By: Chris Lee

Chris Lee is a program manager in the Data Programmability Runtime Connectivity team with responsibility for Microsoft SQL Server Native Client. Prior to joining Microsoft he worked for Micro Focus, Merant, Blyth Software, Inmos and Perkin-Elmer Data Systems. His career experience includes work on operating systems, data access methods, compilers, development tools, and database access.

Chris.T.Lee@microsoft.com