Open Database Connectivity
In
ODBC accomplishes DBMS independence by using an ODBC driver as a translation layer between the application and the DBMS. The application uses ODBC functions through an ODBC driver manager with which it is linked, and the driver passes the query to the DBMS. An ODBC driver can be thought of as analogous to a printer driver or other driver, providing a standard set of functions for the application to use, and implementing DBMS-specific functionality. An application that can use ODBC is referred to as "ODBC-compliant". Any ODBC-compliant application can access any DBMS for which a driver is installed. Drivers exist for all major DBMSs, many other data sources like address book systems and Microsoft Excel, and even for text or comma-separated values (CSV) files.
ODBC was originally developed by Microsoft and Simba Technologies during the early 1990s, and became the basis for the Call Level Interface (CLI) standardized by SQL Access Group in the Unix and mainframe field. ODBC retained several features that were removed as part of the CLI effort. Full ODBC was later ported back to those platforms, and became a de facto standard considerably better known than CLI. The CLI remains similar to ODBC, and applications can be ported from one platform to the other with few changes.
History
Before ODBC
The introduction of the mainframe-based relational database during the 1970s led to a proliferation of data access methods. Generally these systems operated together with a simple command processor that allowed users to type in English-like commands, and receive output. The best-known examples are SQL from IBM and QUEL from the Ingres project. These systems may or may not allow other applications to access the data directly, and those that did use a wide variety of methodologies. The introduction of SQL aimed to solve the problem of language standardization, although substantial differences in implementation remained.
Since the SQL language had only rudimentary programming features, users often wanted to use SQL within a program written in another language, say
SELECT * FROM city
could be inserted as text within C source code, and during compiling it would be converted into a custom format that directly called a function within a librarychar *
using similar library code.
There were several problems with the Embedded SQL approach. Like the different varieties of SQL, the Embedded SQLs that used them varied widely, not only from platform to platform, but even across languages on one platform – a system that allowed calls into IBM Db2 would look very different from one that called into their own SQL/DS.[dubious ] Another key problem to the Embedded SQL concept was that the SQL code could only be changed in the program's source code, so that even small changes to the query required considerable programmer effort to modify. The SQL market referred to this as static SQL, versus dynamic SQL which could be changed at any time, like the command-line interfaces that shipped with almost all SQL systems, or a programming interface that left the SQL as plain text until it was called. Dynamic SQL systems became a major focus for SQL vendors during the 1980s.
Older mainframe databases, and the newer
Early efforts
By the mid-1980s the rapid improvement in microcomputers, and especially the introduction of the
By the late 1980s there were several efforts underway to provide an abstraction layer for this purpose. Some of these were mainframe related, designed to allow programs running on those machines to translate between the variety of SQL's and provide a single common interface which could then be called by other mainframe or microcomputer programs. These solutions included IBM's Distributed Relational Database Architecture (
One of the early examples of such a system was
Around the same time, an industry team including members from Sybase (Tom Haggin), Tandem Computers (Jim Gray & Rao Yendluri) and Microsoft (Kyle Geiger) were working on a standardized dynamic SQL concept. Much of the system was based on Sybase's DB-Library system, with the Sybase-specific sections removed and several additions to support other platforms.[3] DB-Library was aided by an industry-wide move from library systems that were tightly linked to a specific language, to library systems that were provided by the operating system and required the languages on that platform to conform to its standards. This meant that a single library could be used with (potentially) any programming language on a given platform.
The first draft of the Microsoft Data Access API was published in April 1989, about the same time as Lotus' announcement of Blueprint.[4] In spite of Blueprint's great lead – it was running when MSDA was still a paper project – Lotus eventually joined the MSDA efforts as it became clear that SQL would become the de facto database standard.[2] After considerable industry input, in the summer of 1989 the standard became SQL Connectivity (SQLC).[5]
SAG and CLI
In 1988 several vendors, mostly from the Unix and database communities, formed the SQL Access Group (SAG) in an effort to produce a single basic standard for the SQL language. At the first meeting there was considerable debate over whether or not the effort should work solely on the SQL language itself, or attempt a wider standardization which included a dynamic SQL language-embedding system as well, what they called a Call Level Interface (CLI).[6] While attending the meeting with an early draft of what was then still known as MS Data Access, Kyle Geiger of Microsoft invited Jeff Balboni and Larry Barnes of Digital Equipment Corporation (DEC) to join the SQLC meetings as well. SQLC was a potential solution to the call for the CLI, which was being led by DEC.
The new SQLC "gang of four", MS, Tandem, DEC and Sybase, brought an updated version of SQLC to the next SAG meeting in June 1990.
MS continued working with the original SQLC standard, retaining many of the advanced features that were removed from the CLI version. These included features like scrollable cursors, and metadata information queries. The commands in the API were split into groups; the Core group was identical to the CLI, the Level 1 extensions were commands that would be easy to implement in drivers, while Level 2 commands contained the more advanced features like cursors. A proposed standard was released in December 1991, and industry input was gathered and worked into the system through 1992, resulting in yet another name change to ODBC.[9]
JET and ODBC
During this time, Microsoft was in the midst of developing their
The SAG standardization efforts presented an opportunity for Microsoft to adapt their Jet system to the new CLI standard. This would not only make Windows a premier platform for CLI development, but also allow users to use SQL to access both Jet and other databases as well. What was missing was the SQL parser that could convert those calls from their text form into the C-interface used in Jet. To solve this, MS partnered with
Release and continued development
ODBC 1.0 was released in September 1992.
Circa 1993, OpenLink Software shipped one of the first independently developed third-party ODBC drivers, for the
Meanwhile, the CLI standard effort dragged on, and it was not until March 1995 that the definitive version was finalized. By then, Microsoft had already granted Visigenic Software a source code license to develop ODBC on non-Windows platforms. Visigenic ported ODBC to the classic Mac OS, and a wide variety of Unix platforms, where ODBC quickly became the de facto standard.[15] "Real" CLI is rare today. The two systems remain similar, and many applications can be ported from ODBC to CLI with few or no changes.[16]
Over time, database vendors took over the driver interfaces and provided direct links to their products. Skipping the intermediate conversions to and from Jet or similar wrappers often resulted in higher performance. However, by then Microsoft had changed focus to their
As Microsoft turned its attention away from working directly on ODBC, the Unix field was increasingly embracing it. This was propelled by two changes within the market, the introduction of
Sun Microsystems used the ODBC system as the basis for their own open standard, Java Database Connectivity (JDBC). In most ways, JDBC can be considered a version of ODBC for the programming language Java instead of C. JDBC-to-ODBC bridges allow Java-based programs to access data sources through ODBC drivers on platforms lacking a native JDBC driver, although these are now relatively rare. Inversely, ODBC-to-JDBC bridges allow C-based programs to access data sources through JDBC drivers on platforms or from databases lacking suitable ODBC drivers.
ODBC today
ODBC remains in wide use today, with drivers available for most platforms and most databases. It is not uncommon to find ODBC drivers for database engines that are meant to be embedded, like SQLite, as a way to allow existing tools to act as front-ends to these engines for testing and debugging.[21]
Version history
ODBC specifications[22]
- 1.0: released in September 1992[23]
- 2.0: c. 1994
- 2.5
- 3.0: c. 1995, John Goodson of Intersolv and Frank Pellow and Paul Cotton of IBM provided significant input to ODBC 3.0[24]
- 3.5: c. 1997
- 3.8: c. 2009, with Windows 7[25]
- 4.0: Development announced June 2016[26] with first implementation with SQL Server 2017 released Sep 2017 and additional desktop drivers late 2018 final spec on Github
Desktop Database Drivers[27]
- 1.0 (1993–08): Used the SIMBA query processor produced by PageAhead Software.
- 2.0 (1994–12): Used with ODBC 2.0.
- 3.0 (1995–10): Supports Windows 95 and Windows NT Workstation or NT Server 3.51. Only 32-bit drivers were included in this release.
- 3.5 (1996–10): Supports double-byte character set (DBCS), and accommodated the use of File data source names (DSNs). The Microsoft Access driver was released in an RISC version for use on Alpha platforms for Windows 95/98 and Windows NT 3.51 and later operating systems.
- 4.0 (late 1998): Support Microsoft Jet Engine Unicode format along with compatibility for ANSI format of earlier versions.
Drivers and Managers
Drivers
ODBC is based on the device driver model, where the driver encapsulates the logic needed to convert a standard set of commands and functions into the specific calls required by the underlying system. For instance, a printer driver presents a standard set of printing commands, the API, to applications using the printing system. Calls made to those APIs are converted by the driver into the format used by the actual hardware, say PostScript or PCL.
In the case of ODBC, the drivers encapsulate many functions that can be broken down into several broad categories. One set of functions is primarily concerned with finding, connecting to and disconnecting from the DBMS that driver talks to. A second set is used to send SQL commands from the ODBC system to the DBMS, converting or interpreting any commands that are not supported internally. For instance, a DBMS that does not support cursors can emulate this functionality in the driver. Finally, another set of commands, mostly used internally, is used to convert data from the DBMS's internal formats to a set of standardized ODBC formats, which are based on the C language formats.
An ODBC driver enables an ODBC-compliant application to use a data source, normally a DBMS. Some non-DBMS drivers exist, for such data sources as
Driver Manager
Device drivers are normally enumerated, set up and managed by a separate Manager layer, which may provide additional functionality. For instance, printing systems often include functionality to provide spooling functionality on top of the drivers, providing print spooling for any supported printer.
In ODBC the Driver Manager (DM) provides these features.[30] The DM can enumerate the installed drivers and present this as a list, often in a GUI-based form.
But more important to the operation of the ODBC system is the DM's concept of a Data Source Name (DSN). DSNs collect additional information needed to connect to a specific data source, versus the DBMS itself. For instance, the same MySQL driver can be used to connect to any MySQL server, but the connection information to connect to a local private server is different from the information needed to connect to an internet-hosted public server. The DSN stores this information in a standardized format, and the DM provides this to the driver during connection requests. The DM also includes functionality to present a list of DSNs using human readable names, and to select them at run-time to connect to different resources.
The DM also includes the ability to save partially complete DSN's, with code and logic to ask the user for any missing information at runtime. For instance, a DSN can be created without a required password. When an ODBC application attempts to connect to the DBMS using this DSN, the system will pause and ask the user to provide the password before continuing. This frees the application developer from having to create this sort of code, as well as having to know which questions to ask. All of this is included in the driver and the DSNs.
Bridging configurations
A bridge is a special kind of driver: a driver that uses another driver-based technology.
ODBC-to-JDBC (ODBC-JDBC) bridges
An ODBC-JDBC bridge consists of an ODBC driver which uses the services of a
JDBC-to-ODBC (JDBC-ODBC) bridges
A JDBC-ODBC bridge consists of a
OLE DB-to-ODBC bridges
An OLE DB-ODBC bridge consists of an
ADO.NET-to-ODBC bridges
An ADO.NET-ODBC bridge consists of an
See also
- GNU Data Access
- Java Database Connectivity (JDBC)
- Windows Open Services Architecture
- ODBC Administrator
References
- Bibliography
- Geiger, Kyle (1995). Inside ODBC. Microsoft Press. ISBN 9781556158155.
- Citations
- ^ McGlinn, Evan (1988), Blueprint Lets 1-2-3 Access Outside Data", InfoWorld, vol. 10, no. 14, 4 April 1988, pp. 1, 69
- ^ a b Geiger 1995, p. 65.
- ^ Geiger 1995, p. 86-87.
- ^ Geiger 1995, p. 56.
- ^ Geiger 1995, p. 106.
- ^ Geiger 1995, p. 165.
- ^ a b Geiger 1995, p. 186-187.
- ^ ISO/IEC 9075-3 – Information technology – Database languages – SQL – Part 3: Call-Level Interface (SQL/CLI)
- ^ Geiger 1995, p. 203.
- ISBN 978-0-306-47251-0. Retrieved 2010-07-28.
The first ODBC drivers […] used the SIMBA query processor, which translated calls into the Microsoft Jet ISAM calls, and dispatched the calls to the appropriate ISAM driver to access the backend […]
- ^ "Linux/UNIX ODBC – What is ODBC?".
- ^ "Our History", Simba Technologies
- ^ Idehen, Kingsley Uyi (October 1994). "ODBC and progress V7.2d". Usenet Newsgroup comp.databases. Retrieved 13 December 2013.
- ^ Idehen, Kingsley Uyi (1995-07-18). "Need ODBC/Ingres driver for DEC OSF/1". Usenet Newsgroup comp.databases.oracle. Retrieved 13 December 2013.
- ^ Sippl, Roger (1996) "SQL Access Group's Call-Level Interface", Dr. Dobbs, 1 February 1996
- ^ "Similarities and differences between ODBC and CLI", InfoSphere Classic documentation, IBM, 26 September 2008
- ^ "OLE DB and SQL Server: History, End-Game, and some Microsoft "dirt"". 25 September 2011.
- ^ "Announcing the new release of OLE DB Driver for SQL Server".
- ^ Anderson, Andrew (2003-06-20). "Open Database Connectivity in Jaguar". O'Reilly MacDevCenter.com. O'Reilly Media, Inc. Retrieved 13 December 2013.
- ^ Sellers, Dennis (2001-07-17). "ODBC SDK update out for Mac OS Classic, Mac OS X". MacWorld. IDG Consumer & SMB. Retrieved 13 December 2013.
- ^ Werner, Christian (2018) "SQLite ODBC Driver" Archived 2014-06-26 at the Wayback Machine, 2018-02-24
- ^ "ODBC Versions". Linux/UNIX ODBC. Easysoft. Retrieved 2009-10-27.
- ^ Antal, Tiberiu Alexandru. "Access to an Oracle database using JDBC" (PDF). Cluj-Napoca: Technical University of Cluj-Napoca. p. 2. Archived from the original (PDF) on 2011-07-22. Retrieved 2009-10-27.
ODBC 1.0 was released in September 1992
- ISBN 9781572315167)
- ^ "What's New in ODBC 3.8". Microsoft. Retrieved 2010-01-13.
Windows 7 includes an updated version of ODBC, ODBC 3.8.
- ^ Rukmangathan, Krishnakumar (2016-06-07). "A new release of ODBC for Modern Data Stores". Microsoft Data Access / SQL BI Technologies Blog. Microsoft. Retrieved 2017-01-03.
After more than 15 years since the last release, Microsoft is looking at updating the Open Data Base Connectivity (ODBC) specification.
- ^ "History of the Desktop Database Drivers".
- ^ "SAP HANA System Properties". DB-Engines. Retrieved 2016-03-28.
- ^ "Connect to SAP HANA via ODBC - SAP HANA Developer Guide for SAP HANA Studio - SAP Library". help.sap.com. Retrieved 2016-03-28.
- ^ Sybase. "Introduction to ODBC". infocenter.sybase.com. Sybase. Retrieved 8 October 2011.
- ^ "Java JDBC API". docs.oracle.com. Retrieved 18 December 2018.
- ^ Microsoft, "Data Access Technologies Road Map", Deprecated MDAC Components, Microsoft "ADO Programmer's Guide" Appendix A: Providers, Microsoft OLE DB Provider for ODBC, retrieved July 30, 2005. Archived 2001 October 5 at the Wayback Machine