Definition of Open Database Connectivity (ODBC) in The Network Encyclopedia.
What is Open Database Connectivity (ODBC)?
ODBC stands for Open Database Connectivity is a Microsoft database technology that provides an application programming interface (API) that Microsoft Windows-based applications can use to access structured query language (SQL) databases (such as those created and managed with Microsoft SQL Server) over a network.
How It Works
Open database connectivity (ODBC) defines a call-level interface that lets applications access data in any database through the appropriate ODBC driver. The ODBC API allows applications to be independent of the database management systems (DBMS’s) that manage their source data.
The main components of ODBC are as follows:
- ODBC API: A set of function calls, error codes, and SQL syntax that defines how data in a DBMS is accessed.
- ODBC database drivers: Dynamic-link libraries (DLLs) that can process ODBC function calls for specific DBMS’s. ODBC drivers translate application calls to ODBC into calls that the specific DBMS can respond to.
- ODBC Driver Manager: Loads the ODBC drivers that an application needs.
- Data Source: Consists of the data the user wants to access and its associated operating system, DBMS, and network platform (if any) used to access the DBMS.
ODBC, which is based on SQL, provides interoperability by allowing an application to access different SQL DBMS’s by using a common set of code. Developers can thus build client/server database applications without having to pre-specify which DBMS’s will be used for the back end. ODBC uses database drivers to link applications to any ODBC-compliant DBMS. ODBC database drivers are available for more than 50 popular DBMS’s, including SQL Server, Microsoft Access, Microsoft FoxPro, Microsoft Excel, Paradox, dBase, and delimited text files.
With ODBC, applications are not tied to proprietary vendor APIs and can run independently of the underlying data communication protocols. Data can be sent and received in a format suitable for the application instead of being tailored for a vendor-specific DBMS.
You can configure ODBC by using the ODBC utility in Control Panel. This utility lets you define a data source name (DSN) for the database being accessed and drivers for accessing the database. To configure ODBC with this utility, you associate a DSN with an ODBC driver, which lets an application interact with a database that is located either on the local machine or on a network server.
Open Database Connectivity Applications
An application is a program that calls the ODBC API to access data. Although many types of applications are possible, most fall into three categories, which are used as examples throughout this guide.
- Generic Applications These are also referred to as shrink-wrapped applications or off-the-shelf applications. Generic applications are designed to work with a variety of different DBMSs. Examples include a spreadsheet or statistics package that uses ODBC to import data for further analysis and a word processor that uses ODBC to get a mailing list from a database. An important subcategory of generic applications is application development environments, such as PowerBuilder or Microsoft® Visual Basic®. Although the applications constructed with these environments will probably work only with a single DBMS, the environment itself needs to work with multiple DBMSs.What all generic applications have in common is that they are highly interoperable among DBMSs and they need to use ODBC in a relatively generic manner.
- Vertical Applications Vertical applications perform a single type of task, such as order entry or tracking manufacturing data, and work with a database schema that is controlled by the developer of the application. For a particular customer, the application works with a single DBMS. For example, a small business might use the application with dBase, while a large business might use it with Oracle. The application uses ODBC in such a manner that the application is not tied to any one DBMS, although it might be tied to a limited number of DBMSs that provide similar functionality. Thus, the application developer can sell the application independently from the DBMS. Vertical applications are interoperable when they are developed but are sometimes modified to include noninteroperable code once the customer has chosen a DBMS.
- Custom Applications Custom applications are used to perform a specific task in a single company. For example, an application in a large company might gather sales data from several divisions (each of which uses a different DBMS) and create a single report. ODBC is used because it is a common interface and saves programmers from having to learn multiple interfaces. Such applications are generally not interoperable and are written to specific DBMSs and drivers.