Created: October 1992
Revised: July 1993
Open database connectivity (ODBC) is Microsoft's strategic interface for accessing data in a heterogeneous environment of relational and non-relational database management systems. Based on the Call Level Interface specification of the SQL Access Group, ODBC provides an open, vendor-neutral way of accessing data stored in a variety of proprietary personal computer, minicomputer, and mainframe databases. ODBC alleviates the need for independent software vendors and corporate developers to learn multiple application programming interfaces. ODBC now provides a universal data access interface. With ODBC, application developers can allow an application to concurrently access, view, and modify data from multiple, diverse databases. ODBC is a core component of Microsoft® Windows™ Open Services Architecture (WOSA). Apple has endorsed ODBC as a key enabling technology and has announced the ODBC software developer's kit for Macintosh® developers. With growing industry support, ODBC has emerged as the industry standard for data access for both Windows-based and Macintosh-based applications.
Providing data access to applications in today's heterogeneous database environment is very complex for software vendors as well as corporate developers. With ODBC, Microsoft has eased the burden of data access by creating a vendor-neutral, open, and powerful means of accessing database management systems (DBMSs).
Note In the context of this paper, DBMS refers to a database product. This may be a relational database, such as Oracle® or DB2®, or a file-based database, such as dBASE®.
ODBC provides many significant benefits to developers, end users, and the industry by providing an open, standard way to access data.
ODBC is the database access component of Windows™ Open Services Architecture (WOSA), Microsoft's strategic architecture for delivering on "Information At Your Fingertips." This paper describes how ODBC fits into the WOSA framework, the challenges facing developers and users in today's complex computing environment, how ODBC meets these challenges, and why ODBC has become the dominant solution for data access.
In the absence of a formal way to connect front-end applications to various back-end services, application developers are forced to incorporate support for vendor-specific APIs in their applications. Supporting additional services requires that application developers either build new applications or modify existing ones to accommodate diverse APIs—this process is both labor-intensive and expensive.
WOSA provides a single, system-level interface to enterprise computing environments, while hiding the complexities of heterogeneous environments from end users and developers. By taking advantage of the WOSA interface, a Windows-based desktop application does not need to contain special code for any of the types of network in use, the types of computers in the enterprise, or the types of back-end services available in order to gain seamless access to available information. As a result, even if the network, computers, or services should change, the desktop application does not need to be modified. In other words, WOSA enables Windows-based applications to connect to all the services across multiple computing environments.
The Windows operating system presents end users with a single application interface. Once users learn how to use one application, they can quickly learn others. Similarly, WOSA presents developers of distributed applications with a single interface for communicating with back-end services such as DBMSs and messaging. Instead of having to learn a different API for each implementation of a service, developers building applications with WOSA only need to learn a single API for all implementations of a particular service. Furthermore, when an existing service is modified or replaced, the front-end application is unaffected as long as the new back-end service communicates through the WOSA interface.
WOSA makes it possible for corporate developers to build stable, long-term enterprise solutions using various combinations of off-the-shelf products and custom packages, while presenting end users with a single, consistent interface. End users are spared having to learn a new application for each new service or each alteration of an existing service. Developers are spared having to constantly modify their applications to communicate with new services. WOSA makes the Windows operating system the single, reliable, strategic platform for end users, application developers, and MIS managers.
WOSA provides a single system-level interface for connecting front-end applications with back-end services. Application developers and end users alike do not have to worry about using numerous different services, each with its own protocols and API, because making these connections is the business of the operating system, not of individual applications.
WOSA provides an extensible framework in which Windows-based applications can seamlessly access information and network resources in a distributed computing environment. WOSA accomplishes this magic by making a set of common APIs available to all applications.
ODBC and MAPI (Messaging API) are two of the key components of WOSA, along with the Windows Sockets Library, the Licensing API, and Remote Procedure Calls (RPCs). ODBC addresses database connectivity technology, while MAPI addresses electronic mail and workgroup productivity applications. MAPI, ODBC, and the other WOSA components will become part of the standard Windows and Windows NT™ operating systems sometime in the future.
Information has become a key asset to corporate competitiveness. To be competitive in the 1990s, corporations need access to accurate and timely information. Companies are striving to achieve a higher level of accuracy and effectiveness in areas such as pricing, quality control, market analysis, capacity planning, inventory management, customer service, and billing. At the same time, users are demanding better tools for accessing this information. Users demand graphical user interfaces, leading edge analytical tools, easy ways of accessing and viewing information—all without having to know the structure or language of the underlying DBMS or the issues unique to their network.
To achieve this goal, corporations must provide better tools for end users to access existing information while providing a migration path for the data and the applications as they evolve or are "rightsized" to the optimum platform. This presents a difficult challenge, given the heterogeneous nature of most corporations' current information technology.
Historically, database applications have been built to access a single source of data. The range of applications varies from mainframe-based, batch-oriented DBMSs, to terminal-based, interactive applications, to personal computer–based, single-user DBMSs, to the more recent client/server DBMSs. Data typically resides in a variety of file formats, such as VSAM and ISAM, as well as in hierarchical and relational DBMSs.
Corporations typically have applications and data residing on diverse platforms and DBMSs for historical, strategic, and technological reasons. Corporations often have legacy systems that must be maintained because they contain key corporate data. Corporate mergers often bring together diverse information technologies. Systems were often developed using technology that met a specific requirement, such as an engineering application. Departmental users developed their own workgroup and single-user personal computer databases. Over time, data on any of these systems might be summarized for consumption by analysts, copied and distributed geographically, or have many snapshots taken of it.
There is a strong requirement for a common method of accessing, managing, and analyzing data. The heterogeneous nature of database environments is a problem corporations are faced with today. Many firms have discovered that much of the cost associated with application maintenance is related to data access problems. One of the first steps to meeting the needs of information users is database connectivity.
Database connectivity allows an application to communicate with one or more DBMSs. Database connectivity is a requirement whether the application uses a file-based (ISAM) approach, a client/server model, or traditional mainframe connectivity. The requirement for database connectivity has been hastened by client/server computing. As users increasingly use graphical, personal computer–based tools to analyze, prepare, and present data, they require greater access to the vast volumes of existing corporate data. In the most general sense, client/server computing means that some portion of the application runs on a personal computer. At the very least, this computer is responsible for screen presentation and gathering user input. The server (or host) is responsible for responding to queries; managing concurrency, security, backup and recovery; transaction processing; and so on. This differs from centralized, host-based applications where the entire application runs on the host platform.
Some of the key components involved with database connectivity are explained below.
Application
Allows users to perform a set of functions, such as queries, data entry, and report generation. Examples are Microsoft Excel, Microsoft Works, Aldus PageMaker®, and internally developed applications such as an executive information system or a reporting system.
Client system
The physical system where the client portion of an application runs. In the personal computer world, this may be an IBM® PC or compatible or an Apple Macintosh.
Data access software
A service layer on client systems that provides a direct interface for applications. This "middleware" or enabling software plays a key role in client/server data access. This layer accepts data retrieval and update requests directly from the application, and transmits them across the network. This "middleware" also is responsible for returning results and error codes back to the application.
Data source
The data and method of data access. The data may exist in a variety of hierarchical or relational DBMSs, or in a file with a format such as ISAM or VSAM.
Network
The physical connection of the client to the server system.
Network software
The software protocols that allow the client to communicate with the server system.
Server system
The physical system where the DBMS resides (also known as the host system). For example, the server system could be an IBM PC or compatible, a DEC VAX®, or an IBM mainframe.
One of the challenges of database connectivity is accessing multiple, heterogeneous data sources from within a single application. A second challenge is flexibility—the application should be able to directly access data from a variety of data sources without modification to the application. For example, an application could access data from dBASE in a stand-alone, small office environment, and from SQL Server or Oracle in a larger, networked environment. Due to these challenges, some Fortune 500 firms have as little as 1 percent of enterprise data in a form that is truly accessible.
These challenges are common to developers of off-the-shelf applications, and to corporate developers attempting to provide solutions to end users or to migrate data to new platforms. These challenges grow exponentially for developers and support staff as the number of data sources grows.
The problems of database connectivity are apparent in the differences among the programming interfaces, DBMS protocols, DBMS languages, and network protocols of disparate data sources. Even when data sources are restricted to relational DBMSs that use SQL, significant differences in SQL syntax and semantics must be resolved.
The primary differences in the implementation of each of these components are:
To access various database environments, an application developer would have to learn to use each vendor's programming interface, employ each vendor's SQL, and ensure that the proper programming interface, network, and DBMS software were installed on the client system. This complexity makes broad database connectivity unfeasible for most developers and users today.
Several vendors have attempted to address the problem of database connectivity in a variety of ways. The primary approaches include using gateways, a common programming interface, and a common protocol.
Application developers use one vendor's programming interface, SQL grammar, and DBMS protocol. A gateway causes a target DBMS to appear to the application as a copy of the selected DBMS. The gateway translates and forwards requests to the target DBMS and receives results from it. For example, applications that access SQL Server can also access DB2 data through the Micro Decisionware® DB2 Gateway. This product allows a DB2 DBMS to appear to a Windows-based application as a SQL Server DBMS. Note that an application using this gateway would need a different gateway for each type of DBMS it needs to access, such as DEC Rdb, Informix®, Ingres™, and Oracle.
The gateway approach is limited by architectural differences among DBMSs, such as differences in catalogs and SQL implementations, and the need for one gateway for each target DBMS. Gateways remain a very valid approach to database connectivity, and are essential in certain environments, but are typically not a broad, long-term solution.
A single programming interface is provided to the developer. It is possible to provide some standardization in a database application development environment or user interface even when the underlying interfaces are different for each DBMS. This is accomplished by creating a standard API, macro language, or set of user tools for accessing data and translating requests for, and results from, each target DBMS. A common interface is usually implemented by writing a driver for each target DBMS.
The DBMS protocol, SQL grammar, and networking protocols are common to all DBMSs, so the application can use the same protocol and SQL grammar to communicate with all DBMSs. Examples are remote data access (RDA) and distributed relational database architecture (DRDA). RDA is an emerging standard from SAG, but not available today. DRDA is IBM's alternative DBMS protocol. Common protocols can ultimately work very effectively in conjunction with a common interface.
There are several current vendor-specific approaches that address database connectivity. These current approaches have several limitations. Many companies expend resources solving the same problem. This results in diverse implementations and duplication of effort. The results are inconsistent interfaces for end users and developers, overlap in effort, and a compromise in functionality and connectivity options. Although some current implementations provide viable solutions, none have the critical mass to emerge as a de facto standard.
Common interfaces, protocols, and gateways may be combined. A common protocol and interface provides a standard API for developers as well as a single protocol for communication with all databases. A common gateway and interface provides a standard API for developers and allows the gateway to provide functionality, such as translation and connectivity to wide area networks, that would otherwise need to be implemented on each client station. Note that a common gateway or protocol still requires a common interface to hide complexities from developers.
ODBC addresses the heterogeneous database connectivity problem using the common interface approach. Application developers use one API to access all data sources. ODBC is based on a CLI specification, which was developed by a consortium of over 40 companies (members of the SQL Access Group and others), and has broad support from application and database vendors. The result is a single API that provides all the functionality that application developers need, and an architecture that database developers require to ensure interoperability. This will result in a rich set of applications that use ODBC, and provide applications with much broader access to data than ever before.
ODBC defines an API. Each application uses the same code, as defined by the API specification, to talk to many types of data sources through DBMS-specific drivers. A Driver Manager sits between the applications and the drivers. In Windows, the Driver Manager and the drivers are implemented as dynamic-link libraries (DLLs).
The application calls ODBC functions to connect to a data source, send and receive data, and disconnect.
The Driver Manager provides information to an application such as a list of available data sources; loads drivers dynamically as they are needed; and provides argument and state transition checking.
The driver, developed separately from the application, sits between the application and the network. The driver processes ODBC function calls, manages all exchanges between an application and a specific DBMS, and may translate the standard SQL syntax into the native SQL of the target data source. All SQL translations are the responsibility of the driver developer.
Applications are not limited to communicating through one driver. A single application can make multiple connections, each through a different driver, or multiple connections to similar sources through a single driver.
To access a new DBMS, a user or an administrator installs a driver for the DBMS. The user does not need a different version of the application to access the new DBMS. This is a tremendous benefit for end users, as well as providing significant savings for IS organizations in support and development costs.
End users do not work directly with the ODBC API, but they benefit in several ways when they use applications written with ODBC. Users may:
ODBC was designed to allow application developers to decide between using the least common denominator of functionality across DBMSs or exploiting the individual capabilities of specific DBMSs.
ODBC defines a standard SQL grammar and set of function calls that are based upon the SAG CLI specification, called the core grammar and core functions, respectively. If an application developer chooses only to use the core functionality, they need not write any additional code to check for specific capabilities of a driver.
With core functionality, an application can:
ODBC also defines an extended SQL grammar and set of extended functions to provide application developers with a standard way to exploit advanced capabilities of a DBMS. In addition to the above features, ODBC includes a set of extensions that provide enhanced performance and increased power through the following features:
Finally, ODBC supports the use of DBMS-specific SQL grammar, allowing applications to exploit the capabilities of a particular DBMS.
One ODBC driver can be developed that provides access to the DBMS. Any ODBC application may then gain access to that DBMS. This provides a wider number and variety of tools that will work with the DBMS, resulting in larger market potential for vendors and a wider variety of tools for corporations to choose from.
ODBC enjoys a great deal of industry momentum and acceptance as the dominant standard. Database vendors, with the help of third-party developers, have created drivers for their products. Several major application vendors have now shipped products which are ODBC-enabled. ODBC's acceptance to date is due to a variety of reasons:
The following databases will be supported by one or more database drivers by the end of 1993:
| ADABAS SQL Server | IBM DB2/6000 | Quadbase |
| Btrieve | IBM SQL/400® | Raima |
| CA-IDMS | IBM SQL/DS™ | R:BASE® |
| CA-Datacom | Informix | Siemens/Nixdorf SESAM |
| CA-DB | Ingres | Siemens/Nixdorf UDSD |
| DAL | Integra SQL | SQL Server |
| dBASE | Interbase | SupraServer |
| DEC Rdb | Microsoft Access® | Systems 2000 |
| DEC RMS | Microsoft Excel | Tandem NonStop™ SQL |
| Focus® | Microsoft FoxPro | Teradata® |
| Formatted Text | Model 204 | Text files |
| Gupta SQLBase® | NetWareSQL | UNIFY |
| HP® ALLBASE/SQL | Nomad Gateway | WATCOM SQL for Windows |
| HP Image®/SQL | Oracle | White Cross 9000 |
| IBI EDA/SQL™ | Paradox® | XDB |
| IBM DB2 | PICK | |
| IBM DB2/2 | Progress |
The following application vendors have released ODBC-enabled products or have publicly endorsed ODBC technology as of July 1993:
| Andyne | JYACC |
| Approach Software | Knowledgeware |
| Blue Sky Software | LABTECH |
| Blyth | Lotus Development |
| Brio | mdbs |
| Bull HN | Micro Design International |
| Canaan Analytics | Microsoft Corporation |
| Cincom Systems, Inc. | Natural Language |
| Computer Corporation of America | Neuron Data |
| Clear Access | PageAhead Software Corp. |
| Cognos | Parcplace Systems |
| Computer Associates | Pilot Technologies |
| Coromandel | Pioneer Software Systems Corp. |
| CSA | Powersoft |
| DataEase International | Progress Software |
| Dharma Systems | Revelation |
| EASEL | SPC |
| Fairfield Software | SPSS |
| FileNet | Sterling Software |
| Genus Software | SoftwareTechnologies |
| Great Plains Software | The Dodge Group |
| Guild Software | Trinzic |
| Gupta Technologies, Inc. | Visionware |
| Hewlett-Packard Company | Winclient |
| Icons International | Xdb |
The next release of the ODBC Software Development Kit, version 2.0, will be available in the first quarter of 1994, and will provide considerable enhancements based on input from software vendors and corporate developers. The new version will support the 32-port technology of Windows NT, scrollable cursors independent of driver capability, additional sample applications, and sophisticated debugging tools.
Apple has endorsed ODBC technology and will continue to enable developers to exploit the power of ODBC. Apple has announced their ODBC Software Developers Kit and will announce additional ODBC-compatible drivers later this year. ODBC is a completely portable API, and may be ported to other major operating system environments in the future.
ODBC is available for software vendors and corporate developers. The ODBC SDK includes development tools, documentation, a dBASE test driver for developing and testing ODBC applications, and an application for testing ODBC drivers. ODBC will be included in future versions of the Windows operating system. Microsoft has shipped numerous Windows-based applications with ODBC technology, and these products will continue to provide data connectivity through ODBC in future releases.
The Microsoft ODBC SDK contains everything necessary for developing Windows-based ODBC applications and drivers. The ODBC SDK version 1.0 includes the following:
Apple has announced its ODBC Software Developers Kit, which enables Macintosh developers to build applications and drivers using ODBC. The kit consists of an installer disk, ODBC test application, a test DBMS driver, and other components to assist in the development of ODBC-compatible applications for the Macintosh. Please contact Apple for availability and pricing information.
The Microsoft ODBC Driver Catalog provides a quick reference for driver availability. This catalog contains key information on vendors that provide ODBC drivers, pricing, availability, and contacts.
CompuServe® provides the Windows Extension forum, which has an ODBC section. Please refer to this forum for updates on ODBC's status. The library in this section contains relevant files that may be downloaded. To access the forum from CompuServe, type GO WINEXT, and then select the ODBC section.
Technet, Microsoft's technical information network, is a community of support professionals, system integrators and solution builders, many of whom have experience implementing ODBC technology.
The Microsoft Developer Network publishes technical information for all developers who write applications for Microsoft operating systems or who use Microsoft development tools. ODBC-experienced developers contribute information to the network, which helps speed the acceptance and implementation of ODBC technology.
Providing data access to applications in today's heterogeneous database environment is very complex for software vendors as well as corporate developers. ODBC solves this data access problem for software vendors and corporations by providing a standard, open, and vendor-neutral API. ODBC allows corporations and software vendors to protect their investments in existing DBMSs, and protect developers' acquired DBMS skills. ODBC benefits users as more end-user applications connect to additional data sources, making the vast volumes of corporate data more readily available. ODBC is a portable API, which allows it to be a cross-platform tool. It is based upon the SQL Access Group (SAG) Call Level Interface (CLI) and provides a standard SQL language based upon ANSI standards. With ODBC, Microsoft provides many benefits to developers, end users, and the industry by creating a vendor-neutral, open, and powerful means of accessing data.