What Is Open Database Connectivity?

A data access application program interface (API) that supports access to any data source that can use an ODBC driver.

Open Database Connectivity (ODBC) is a component of the Microsoft Open Services Architecture (WOSA, Windows Open Services Architecture) related database. It establishes a set of specifications and provides a set of standard APIs for database access. (Application Programming Interface). These APIs use SQL to accomplish most of their tasks. ODBC itself also provides support for the SQL language, and users can send SQL statements directly to ODBC. Access database API specifications defined by Open Database Connectivity. These APIs are independent of different vendors' DBMSs and specific programming languages. The ODBC specification was later adopted by X / OPEN and ISO / IEC as part of the SQL standard. For details, please refer to the relevant standard documents such as ISO / IEC 9075-3: 1995 (E) Call-Level Interface (SQL / CLI). .

Chinese name: Open database interconnect

Foreign name: ODBC
Presenter: Microsoft

1. Open Database Connectivity 1. Generation and Development

Relational databases became mainstream products of database systems soon after they were created. Because each DBMS vendor has its own set of standards, people had a standardization idea long ago, so SQL was produced, and its syntax specifications gradually became popular. Accept and become the dominant language on RDBMS. Initially, in order to solve the problem of interconnection, database vendors often provide embedded SQL APIs. When users want an RDBMS in the operating system on the client side, they often need to embed SQL statements in the program for pre-compilation. Because different manufacturers have different degrees of differences in data format, data operation, specific implementation, and even syntax, they are not compatible with each other.

For a long time, the non-standard situation of this API has not satisfied users and RDBMS vendors. In the late 1980s, some well-known manufacturers formed the SQL Access Group (SAG for short), and proposed the core of the SQL API specification: Call Level Interface (CLI).

In November 1991, Microsoft announced ODBC and launched a usable version the following year. In February 1992, version 2.0 of the ODBC SDK was launched. ODBC is based on the syntax stipulated by the SAG's SQL CAE draft. It is divided into Core, Level 1, and Level 2 definitions, which respectively regulate a total of 51 commands of 22, 16, and 13, 29 of which even exceed the original in the SAG CLI Definition, powerful and flexible. It also includes standard error code sets, standard connection and login DBMS methods, standard data type representations, and more.

Because of the advanced ideology of ODBC, and there is no similar standard or product to compete with it, it stands out and has been favored by many manufacturers and users just two or three years after its introduction, and has become a widely accepted standard. Common DBMSs provide ODBC driver interfaces. ODBC has become an important supporting technology in client / server systems.

2. Open database connection 2. Basic ideas and characteristics

The basic idea of ODBC is to provide users with a simple, standard, and transparent public programming interface for database connection. Developers implement the underlying driver according to the ODBC standard. This driver is transparent to the user and allows different uses according to different DBMSs. The technology is optimized and implemented, which is conducive to continuously absorbing new technology and improving it.

This is also the idea of database-driven. After the emergence of ODBC, users can install different DBMS drivers and use the same SQL statements to perform the same operations on different DBMSs without precompilation. ODBC has brought about a change in database connection methods. In the traditional way, developers need to be familiar with multiple DBMSs and their APIs. Once changes occur on the DBMS side, it often leads to the rebuilding of the client system or the modification of the source code, which brings great difficulties to development and maintenance. In the ODBC method, no matter what the underlying network environment is and no matter what kind of DBMS is used, users use the same standard code in the program. There is no need to understand the characteristics of each DBMS and its API one by one. The source program is not re-edited due to the underlying changes. Construction or modification, thereby reducing the workload of development and maintenance and shortening the development cycle.

To summarize, ODBC has the following flexible characteristics:

1) Make user programs highly interoperable, the same object code is applicable to different DBMSs; an ODBC driver for a specific DBMS allows any ODBC-enabled application to become an ODBC client.

2) Because of the openness of ODBC, it provides convenience for program integration and provides technical support for client / server structure.

3) Since the application is separated from the underlying network environment and DBMS, it simplifies development and maintenance difficulties. Application developers do not need to modify their applications to allow them to access data in multiple backends. As long as a specific backend has an ODBC driver, an ODBC-enabled frontend can access it.

3. Open Database Connectivity 3. Architecture and Implementation

ODBC relies on a layered structure to achieve its standards and openness. ODBC is divided into four layers: applications, driver managers, drivers, and data sources. Microsoft Corporation has standardized the ODBC procedures. It provides standard functions, syntax, and error codes for developers and users at the application layer. Microsoft also provides a driver manager, which is a dynamic link library, ODBC, in Windows. DLL. The driver layer is provided by Microsoft, a DBMS vendor or a third developer. It must conform to the ODBC rules. For Oracle, it is ORA6WIN.DLL. For SQL Server, it is SQLSRVR.DLL.

3.1Application Open database connection 3.1 application layer (Application)

An application using the ODBC interface can perform the following tasks: request a connection and session with the data source (SQLConnect); send a SQL request (SQLExecDirct or SQLExecute) to the data source; define the storage area and data format for the result of the SQL request; Request the result; Processing error; Return the result to the user if necessary; Control the transaction and request the execution or rollback operation (SQLTransact); Terminate the connection to the data source (SQLDisconnect).

3.2Driver Manager Open Database Connectivity 3.2 Driver Manager

The driver manager provided by Microsoft is a dynamic link library ODBC.DLL with an input library. Its main purpose is to load the driver. In addition, it performs the following tasks:

handle several ODBC initialization calls;

Provide ODBC function entry points for each driver;

Provide parameters and order verification for ODBC calls.

3.3Driver Open Database Connectivity 3.3 Driver

A driver is a DLL that implements the interaction between ODBC functions and data sources. When an application calls SQL Connect or SQLDriver Connect, the driver manager loads the corresponding driver, and it responds to ODBC function calls from the application. It is required to perform the following tasks: establish a connection with the data source; submit a request to the data source; convert the data format when the application requires it; return the result to the application; format the running error as a standard code and return; Describe and handle cursors as needed.

These functions are the specific implementation of application layer functions. The configuration of the driver can be divided into the following two types.

1) single-tier (single-tier) In this way, the driver must process ODBC calls to SQL statements and directly manipulate the database, so it has data access capabilities. The most common configuration of this kind is to access heterogeneous databases on the same computer through ODBC, such as accessing XBase, Excel, Paradox and other data files in Powerbuilder.

2) Multiple-tier In this configuration, the driver only handles ODBC calls, and passes SQL statements to the server for execution, and then returns the results. This is often the case where the application, driver manager, and driver reside on the client side, while the data source and data access functions reside on the server side. For example, use Foxpro or Excel to access data on SQL Server or Oracle.

Sometimes a gateway is added between the above two to solve the problem of communication protocol conversion, etc. At this time, the driver first transmits the request to the gateway.

3.4 Open database connection 3.4 data source

It consists of the data the user wants to access and its related operating system, DBMS and network environment.

4. Open database connection 4. Consistency level

From an application point of view, the ideal situation is that each data source and driver supports the same set of ODBC function calls and SQL statements. However, due to the great differences in the implementation of various DBMSs, the systems and environments they depend on are also different, and the degree of support for ODBC is inconsistent. Conformance Levels establish a standard division of numerous functions, providing assistance and choices for applications and drivers. It defines the range of ODBC functions and SQL statements supported by the driver. We can use the three functions SQLGetInfo, SQLGetFunctions, and SQLTypeInfo to learn the set of functions supported by the driver.

ODBC divides the level from two aspects of API and SQL syntax.

4.1API Open Database Connectivity 4.1 API Consistency

ODBC divides function calls into three levels.

1) Core API It includes basic functions that match the CLI of SAG, including: allocating and releasing environments, connections, and statement handles; connecting to data sources; preparing and executing SQL statements or executing SQL statements immediately; for SQL statements and results Parameters in the column allocate memory; retrieve data from results, retrieve information about results; commit and undo transactions; retrieve error information.

2) Level 1 API It includes all the functions of the core API, such as connecting to a data source with a dialog box of a specific driver; setting and querying statement values and connection options; sending some or all parameter values; retrieving some and all results; retrieving Directory information; retrieves information about drivers and data sources.

3) Second-level API Its functions include all the functions of the core and first-level APIs; browsing available connections and a list of available data sources; sending parameter value arrays, retrieving result arrays; retrieving the number of parameters and parameter descriptions; application scrolling Moving cursor; retrieve SQL statements and native tables; retrieve various directory information; call conversion DLL.

4.2SQL Open database connection 4.2 SQL syntax consistency level

From SQL, it can be divided into three levels: minimal SQL syntax, core SQL syntax, and extended SQL syntax.

ODBC now seems to be a relatively old thing. It was relatively stereotyped around 1996. Its latest version is 3.8. MICROSOFT does not plan to make any major updates to it. However, because it is a relatively mature and ancient specification, ODBC can be used on most DBMSs. It can be said that a decent DBMS should support ODBC 3.0 or above.

5. ODBC Open Database Connectivity 5. Examples using ODBC

1) The Access front-end uses the Oracle ODBC driver (included with Access 1.1) to access the Oracle back-end data.

2) The Visual Basic front end uses the dBASE ODBC driver (which is part of the MS ODBC database driver package) to access the data of the dBASE back end.

3) C application written with C + ODBC SDK + Win SDK uses AS / 400 ODBC driver provided by Rochester Software to access data in AS / 400. ^[1]