Open Database Access and ODBC

An Issue Paper by FFE Software, Inc.

Open systems provide a standard to write applications to which is portable to multiple systems. Portability is the key. In prior relational systems, Embedded SQL was the only semi-portable interface. With the introduction of Open Database Connectivity (ODBC) by Microsoft, a truly portable database API (Applications Programming Interface) became available. ODBC is a function set that provides an interface that is portable to multiple platforms and RDBMSs. A single application can be connected to different database backends with no changes, re-compilation or re-linking required. At the same time, ODBC provides a powerful set of facilities for controlling and utilizing databases. This paper examines the issues of ODBC as an open system. It begins with an overview of portable database interfaces and then describes the nature of ODBC. It concludes with a look at the future of ODBC and the dark clouds that are threatening the standard.

Contents:

• History of Portable Interfaces, an overview of the progress of portable interfaces from Embedded SQL to ODBC.
  
• What is ODBC?, a closer look at ODBC itself.
  
• The Future of ODBC, what direction is ODBC going and what does the future hold?

History of Portable Interfaces

Embedded SQL
In the early days of relational database and throughout the '80s, the only portable interface for applications was Embedded SQL. There was no common function API and no standard 4GL. Embedded SQL uses a language specific Precompiler. SQL commands are embedded in a host programming language, such as, C or COBOL. The Precompile translates the embedded commands into host language statements that use the native API of the database.

Originally, the defacto standard for Embedded SQL was IBM's DB2 implementation. It served as the basis of the first ANSI SQL in 1986. Unfortunately, ANSI SQL 86 and its revision in 1989 only defined a subset of Embedded SQL known as Static SQL. The DB2 implementation of Dynamic SQL continued as the defacto standard well into the '90s, serving as the basis for implementations like Oracle and FirstSQL.

Open Database API
In the '90s, the SQL Access Group (SAG) was formed for major vendors. SAG defined a portable database API for SQL. The SA. specification served as the basis of the Microsoft Open Database Connectivity (ODBC) specification.

A number of drivers were developed for ODBC, and it quickly became the defacto standard for SQL APIs. SAG has continued to refine their specification, and other vendors have implemented a competing standard - IDAPI, but ODBC far surpasses them in platforms and RDBMSs supported. FirstSQL was one of the early supporters of ODBC. It provides an ODBC driver for each version of its engine - desktop and server.

FirstSQL Embedded SQL also can connect to OEM ODBC drivers. ODBC is a complicated but effective portable interface. What is ODBC? provides an overview of its capabilities. The Future of ODBC is a look ahead.

What is ODBC?

ODBC is a standardized API (Application Programming Interface). Itis a set of function calls based on the SQL Access Group (SAG) function set for utilizing a SQL database system (back-end system). The SAG set implements the basic functionality of Dynamic SQL. Embedded SQL commads can be translated to call ODBC.

With ODBC, Microsoft extended the basic SAG function set to include functions for accessing the database catalog and for controlling and determining the capabilities of ODBC drivers and their data sources (back-ends). They also have refined and fleshed out the SAG proposal. Microsoft supplies the ODBC Driver Manager for their operating systems (Windows, Windows 95, Windows NT). The ODBC Driver Manager coordinates access to ODBC drivers and their associated data sources.

ODBC Architecture

Applications access ODBC functions through the ODBC Driver Manager which dynamically links to the appropriate ODBC driver. ODBC drivers translate ODBC requests to native format for a specific data source. The data source may be a complete RDBMS like FirstSQL or it may be a simple file format, like Xbase.

Most ODBC drivers are tied to a single data source. Some, like FirstSQL, support multiple data sources. The FirstSQL ODBC driver supports both a FirstSQL data source and an Xbase data source.

ODBC Utilization

ODBC provides a robust set of functions for access to a database. The interface is complicated and takes time to learn to use well. It is often layered with a higher level interface like Embedded SQL, C++ classes or Visual Basic controls. Another example of higher level interfaces is Microsoft's OLE DB.

Though often accessed through higher level objects, ODBC provides the appropriate power to utilize databases. It is the most widely supported portable database interface available. But, a shadow has been cast over The Future of ODBC by the actions of Microsoft.

The Future of ODBC

Though its name begins with open, implying that it is not tied to a single vendor or even to a subset of RDBMS vendors, ODBC is controlled by a single vendor - Microsoft. Microsoft defines the specification of the API and supplies the basic driver manager software used on Microsoft operating systems. This control has some good aspects and some bad for the future of ODBC.

The Good (Moves towards Openness)

Microsoft has made reasonable, useful extensions to the original SQL Access Group, SAG, definitions in creating ODBC. Later releases have refined those extensions. Microsoft has committed to bringing Version 3.0 and future versions of ODBC more in line with SAG's specifications and with existing standards.

The Bad (Sidetracking the Openness)

On the negative side, Microsoft is too often using ODBC as a tool of their overall marketing campaign. Starting with Version 2.0 of ODBC, they have controlled and restricted distribution and have changed the specification for the sole purpose of furthering the goals of Microsoft. These actions have certainly severely injured the openness of ODBC.

One of the attacks that MS has made on ODBC is with the OLE DB initiative. This is discussed in the next section.

Other actions by Microsoft injurious to ODBC are:

• Updating Cursors. ANSI SQL and most RDBMSs support cursors and provide for updates and deletes of the current row in a cursor. This is known as UPDATE and DELETE WHERE CURRENT OF CURSOR. It is not supported by either Microsoft RDBMSs - Access or SQL Server. In ODBC 2.0, Microsoft introduced a complicated, non-standard mechanism to cater to their engines. At the same time, they moved CURRENT OF CURSOR to Extension Level 2, indicating a less common or important feature.
• 16-Bit Support. In ODBC 2.5, Microsoft removed support for 16-bit drivers for their operating systems. Starting with ODBC 2.5, only the 32-bit Driver Manager will be distributed for Windows 95 and NT. This has the effect for freezing all 16-bit drivers at ODBC 2.1 with no future enhancements or bug fixes. Applications developers wishing to maintain common 16/32 bit versions will face real difficulties.
• Price Controls. Since ODBC 2.0, Microsoft has exercised control over ODBC driver developers by forcing them to pay for the ODBC SDK. The ODBC SDK is available from Microsoft as part of a general yearly subscription package. The price is significant and is expected to rise in the future. This is not an open system.

OLE DB

In a major strike against ODBC, Microsoft is touting their OLE DB facility as a replacement for ODBC. OLE DB could be viewed as an object layer placed on top of ODBC, but Microsoft is likely to provide direct OLE DB drivers for their database products and to de-emphasize and perhaps discontinue ODBC drivers for their products. OLE DB is not open nor portable except between Microsoft OSs, which will become only a single OS - NT, in the next few years.

Because of Microsoft's total control of the specification and arbitrary complexities in the facility, OLE DB will not be supported by other Operating Systems - OS/2, MAC OS and various flavors of UNIX. ODBC, and Embedded SQL to a lesser degre, will remain as the only open and portable interaces for SQL accessable databases. Unfortunately, the fate of ODBC is completely under the control of Microsoft.

Copyright 1996 FFE Software All Rights Reserved WorldWide