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, experts at Microsoft believed OLE DB would supersede ODBC. This is no longer the case …
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.
Ongoing Roles for ODBC
I’ll now examine some key scenarios where ODBC will play an important role in SQL Server’s future, starting with data integration and business intelligence. Some of the SQL Server components involved here rely on OLE DB internally and reflect this in their external interfaces. In recognition of the popularity of ODBC, Microsoft has decided to continue support for the Microsoft OLE DB Provider for ODBC in both 32- and 64-bit versions for Windows Server 2003, Windows Vista, and Windows Server 2008 to ensure that ODBC can be used when it is the best choice available.
Dynamic languages such as PHP, Perl, Python and Ruby account for a significant amount of developer activity. Some of these are supported on the .NET platform but are also popular on other platforms. All of these languages can access Microsoft SQL Server via ODBC, though the libraries available to achieve this vary in quality and performance. Microsoft will continuously review the need for libraries optimized for Microsoft SQL Server and is developing a PHP extension library based on SQL Server Native Client’s ODBC driver.
Microsoft … is developing a PHP extension library based on SQL Server Native Client’s ODBC driver.
Business interest in application migration and server consolidation as a means of reducing operational and license costs is increasing. SQL Server’s excellent total cost of ownership profile makes it very attractive for this role. ODBC has significant roles to play here in both data consolidation and application migration, as described earlier. Microsoft SQL Server Migration Assistant helps customers migrate schema and the database itself to Microsoft SQL Server and Microsoft is working on documentation and tools to help migrate application source code to use ODBC and Transact SQL in the future.
Non-Windows clients and mid-tier servers add an extra dimension to the scenarios described above. Currently third-party ODBC drivers meet customer requirements, but Microsoft continuously evaluates providing its own drivers to ensure customers are getting the best experience.
Last but not least, Microsoft wants existing customers to be even more successful in the future. This involves maximizing their return on existing code assets by application modernization and code reuse. If existing business logic continues to meet business needs then it makes more sense to reuse it than to rewrite it. For example, approvals and forecasting in financial applications use the same algorithms regardless of application architecture. The ability to mine routines from batch and call center applications and re-deploy in SOAs, where they then become available for B2B and self-service consumer scenarios, makes sound business sense. Naturally, .NET is a strong contender for developing the new communication and UI elements of the modernized application, but core C++ and ODBC-based business logic and data access routines can often be re-used with very little modification and at very low cost. Visual Studio and C++ CLI provide excellent re-use and interoperability capabilities and there is an opportunity to extend this further in the future by providing increasingly more sophisticated re-factoring tools.
What Do the Next 15 Years Hold in Store for ODBC?
There are two key and inter-related questions to answer here. First, how will ODBC continue to adapt to the changing capabilities of SQL Server in its role of principal native API? Second, how will ODBC evolve in its role as an industry standard cross-platform, cross-database, API?
ODBC defines areas where drivers may add their own driver-specific extensions above and beyond the core ODBC specification, and Microsoft SQL Server Native Client uses this capability to add support for new features added to Microsoft SQL Server. Other drivers have also added their own extensions for this purpose. The following two examples will demonstrate how Microsoft SQL Server Native Client adapts to SQL Server enhancements.
SQL Server 2008 adds additional date/time data types to supplement the existing datetime and smalldatetime types: date and datetime2 correspond to ODBC’s existing SQL_TYPE_DATE and SQL_TYPE_TIMESTAMP types; time (time with 0 to 7 digits fractional seconds scale) and timestampoffset (effectively datetime2 plus a timezone offset) do not match any existing ODBC data types and so new types must be added to support them. These are SQL_SS_TIME2 and SQL_SS_TIMESTAMPOFFSET.
In SQL Server a table-valued parameter (TVP) is a parameter to a T-SQL statement or stored procedure that can consist of multiple rows and columns. For example, consider the following statement:
Insert into OrderItems (OrdID, ProdCode, Qty)
Select ?, ProdCode, Qty from ?
The first parameter is the primary key of a newly inserted Order and the second is a TVP that has a row for each order item with product code and quantity values. The TVP allows multiple OrderItem rows to be inserted in a single statement from a single parameter. TVPs add performance by reducing round trips between client and server and by enabling optimizations in TDS and the relational engine. They also enhance encapsulation by enabling a single stored procedure to perform a complete business transaction, with exceptional code clarity, by relaxing normal “rectangular” restrictions on parameter sets to stored procedures. Note this example of the latter:
create type OrdItemType as table(
ProdCode integer, Qty integer)
create procedure OrderEntry
(
@CustCode varchar(5),
@Items OrdItemType READONLY,
@OrdNo integer output,
@OrdDate datetime output)
as
set @OrdDate = GETDATE();
insert into Orders (OrdDate, CustCode)
values (@OrdDate, @CustCode);
select @OrdNo = SCOPE_IDENTITY();
insert into TVPItem (OrdNo, ProdCode, Qty)
select @OrdNo, ProdCode, Qty from @Items
Without a TVP you would have to split this into two stored procedures, one for each table.
Since you can mix table-valued and traditional single-valued parameters in a statement, SQL Server Native Client must provide a way to handle what amounts to a nested table when binding ODBC parameters. This is achieved by first binding a TVP as type SQL_SS_TABLE, a new type representing a nested table. Then a statement attribute SQL_SOPT_SS_PARAM_FOCUS is set to the parameter ordinal of the TVP. This directs subsequent calls to SQLBindParameter to columns of the TVP (rather than the “top-level” parameter set) to define the nested row structure. What seems at first thought to be a complex and difficult extension to ODBC turns out to be quite simple in practice and the resulting application code is very compact and readable.
The complete TVP implementation in SQL Server Native Client builds naturally on existing ODBC concepts of array binding and “data at execution” parameter values to allow rows of a TVP to be supplied at runtime either in an in-memory array or as batches of one or more rows that are streamed to the server when the statement is executed.
So far, so good. SQL Server Native Client shows that an ODBC driver can add significant functionality without requiring any changes to the ODBC specification. Although, what happens if and when a new SQL Server feature goes beyond what ODBC extensibility allows? Are there new SQL Server application scenarios where ODBC’s architecture is less than ideal? These are tricky questions, to which possible solutions might be: (1) update the core ODBC specification, or (2) turn SQL Server Native Client into a native API that can work without an ODBC Driver Manager and so is no longer constrained by the ODBC specification. Each of these paths have implications.
Changing the ODBC specification is not entirely risk free. One of ODBC’s greatest virtues is its stability. Change the specification and existing drivers and applications may start behaving in new and unexpected ways. Branching away from the current specification may satisfy some scenarios but abandon others. Is there a utopian “third option” for SQL Server Native Client and ODBC? IBM’s call level interface for DB2 can operate in dual modes as ODBC driver and extended call level interface. This could be an evolutionary path for SQL Server Native Client and/or ODBC. Another alternative may be to enable current and future versions of ODBC to co-exist side-by-side in some way. The current ODBC Driver Manager already achieves this by supporting the slightly different behaviors of ODBC 2 and ODBC 3 side-by-side.
Are other organizations interested in extending ODBC beyond the current core specification? This is an area where Microsoft would like to encourage discussion among interested parties and assess the potential for a broadly based consensus. Some form of community-based evolution might be able to react more quickly to a wider range of input than a full-blown ISO or ANSI process. You can rest assured of one certainty, whatever happens; nothing will be allowed to undermine ODBC’s rich legacy and enduring value throughout the industry.
Microsoft regards the Entity Data Model (EDM) as a major generational step change across the whole software industry, applicable well beyond the current ADO.NET Entity Framework. Thinking how ODBC and the EDM might come together provides an additional factor in ODBC’s future. ODBC could be extended to handle queries in Entity SQL returning entity-shaped results with polymorphic rows and nested entity values. The experience with non-rectangular data for table-valued parameters suggests that handling this via driver-specific extensions in SQL Server Native Client would be feasible. However, Microsoft is enabling the EDM for a broad range of data sources, not just SQL Server. EDM support will be available for other relational database products via the ADO.NET Entity Framework, so why not do the same by extending ODBC’s core specification to support the EDM? In part the answer depends on the level of adoption EDM would see in ODBC applications, and the nature of the demand: should EDM and relational access use separate drivers, a common driver but separate connections or co-exist on the same connection? Would existing code adopt EDM access incrementally or is the EDM attractive only in new code? This is another area where Microsoft would be interested in hearing the views of interested parties.
ODBC has come a long way and hopefully this article has given you new insights into the breadth and depth of what it has and will continue to offer for many years to come; how it might evolve in the future; and of the ongoing long term commitment Microsoft has made to it.
To comment on the points raised in this article, please send e-mail to odbcfut@microsoft.com.
You can find further information about ODBC in the following references:
- Mike Pizzo’s very entertaining history of Microsoft’s data access APIs at http://blogs.msdn.com/data/archive/2006/12/05/data-access-api-of-the-day-part-i.aspx
- The Wikipedia ODBC page at http://en.wikipedia.org/wiki/Open_Database_Connectivity
- Ken North’s article on SQL and ODBC in integration frameworks for Business Integration Journal at http://www.sqlsummit.com/PDF/BIJ_North_Nov2004.pdf
- Ken North’s ODBC portal at http://www.sqlsummit.com/ODBCPORT.HTM
- Online ODBC documentation at http://msdn2.microsoft.com/en-us/library/ms710252.aspx
- Online information about Microsoft SQL Server Native Client at http://msdn2.microsoft.com/en-us/data/aa937705.aspx
- The SQL Native Client Blog at http://blogs.msdn.com/sqlnativeclient/
