|
Open DataBase Connectivity (ODBC) ODBC being a specification for a database application programming interface (API), is language-independent and is also independent of any one DBMS or operating system. ODBC provides developers with a way to access data in different DBMSs through a single application.
In order to gain a better understanding of this concept, let's first take a look at how DBMS applications access data.
Structured Query Language (SQL)
DBMS applications earlier required extensive use of cryptic commands and syntax to access and view data. In order to simplify data access and make it more natural to manipulate it, a new methodology was evolved in the form of a simplistic language called Structured Query Language (SQL).
Although SQL solved the ad-hoc needs of users, the need for data access by computer programs did not go away. In fact, most database access still was (and is) programmatic, in the form of regularly scheduled reports and statistical analysis, data entry programs such as those used for order entry, and data manipulation programs such as those used to reconcile accounts and generate work orders.
These programs also use SQL, using one of the following three techniques:
Embedded SQL, in which SQL statements are embedded in a host language such as C or COBOL.
SQL Modules, in which SQL statements are compiled on the DBMS and called from a host language.
Call-Level Interface, or CLI, which consists of functions called to pass SQL statements to the DBMS and to retrieve results from the DBMS.
Example of SQL language syntax:
select NAME from PATIENTS
where AGE < 30 and
DIAGNOSIS = "HEART ATTACK" and
SMOKING = 'YES'
This query asks the DBMS to give a list of names of patients who are smokers, suffering from heart attack and are below 30 years of age.
How is SQL Processed?
To process an SQL statement, a DBMS performs the following five steps:
1. The DBMS first parses the SQL statement. It breaks the statement up into individual words, called tokens, makes sure that the statement has valid verb and valid clauses, and so on. Syntax errors and incorrect spellings can be detected in this step.
2. The DBMS validates the statement. It checks the statement against the system catalog. Do all the tables named in the statement exist in the database? Do all of the columns exist and are the column names unambiguous? Does the user have the required privileges to execute the statement? Certain semantic errors can be detected in this step.
3. The DBMS generates an access plan for the statement. The access plan is a binary representation of the steps that are required to carry out the statement i.e. the DBMS equivalent of executable code.
4. The DBMS optimises the access plan. It explores various ways to carry out the access plan. Can an index be used to speed a search? Should the DBMS first apply a search condition to Table A and then join it to Table B, or should it begin with the join and use the search condition later? Can a sequential search through a table be avoided or reduced to a subset of the table? After exploring the various alternatives, the DBMS chooses one of them.
5. The DBMS executes the statement by running the access plan.
The steps used to process an SQL statement vary in the amount of database access they require and the amount of time they take. Parsing an SQL statement does not require access to the database and can be done very quickly.
Optimisation, on the other hand, is a very CPU-intensive process and requires access to the system catalog. For a complex, multi-table query, the optimiser may explore thousands of different ways of carrying out the same query. However, the cost of executing the query inefficiently is usually so high that the time spent in optimisation is more than regained in increased query execution speed. This is even more significant if the same optimised access plan can be used over and over to perform repetitive queries.
How does ODBC standardise heterogeneous database access?
There are two requirements:
Applications must be able to access multiple DBMSs using the same source code without recompiling or relinking.
Applications must be able to access multiple DBMSs simultaneously.
Which DBMS features should ODBC expose? Only features that are common to all DBMSs or any feature that is available in any DBMS?
ODBC solves these problems in the following manner:
ODBC is a call-level interface: To solve the problem of how applications access multiple DBMSs using the same source code, ODBC defines a standard CLI. This contains all of the functions in the CLI specifications from X/Open and ISO/IEC and provides additional functions commonly required by applications.
A different library, or driver, is required for each DBMS that supports ODBC. The driver implements the functions in the ODBC API. To use a different driver, the application does not need to be recompiled or relinked. Instead, the application simply loads the new driver and calls the functions in it.
To access multiple DBMSs simultaneously, the application loads multiple drivers. How drivers are supported is operating system-specific. For example, on the Microsoft Windows operating system, drivers are dynamic-link libraries (DLLs).
ODBC defines a standard SQL grammar: In addition to a standard call-level interface, ODBC defines a standard SQL grammar. This grammar is based on the X/Open SQL CAE specification. Differences between the two grammars are minor and primarily due to the differences between the SQL grammar required by embedded SQL (X/Open) and a CLI (ODBC).
There are also some extensions to the grammar to expose commonly available language features not covered by the X/Open grammar.
Applications can submit statements using ODBC or DBMS-specific grammar. If a statement uses ODBC grammar that is different from DBMS-specific grammar, the driver converts it before sending it to the data source. However, such conversions are rare because most DBMSs already use standard SQL grammar.
ODBC provides a Driver Manager to manage simultaneous access to multiple DBMSs: Although the use of drivers solves the problem of accessing multiple DBMSs simultaneously, the code to do this can be complex. Applications that are designed to work with all drivers cannot be statically linked to any drivers. Instead, they must load drivers at run time and call the functions in them through a table of function pointers. The situation becomes more complex if the application uses multiple drivers simultaneously.
Rather than forcing each application to do this, ODBC provides a Driver Manager. The Driver Manager implements all of the ODBC functions--mostly as pass-through calls to ODBC functions in drivers--and is statically linked to the application or loaded by the application at run time. Thus, the application calls ODBC functions by name in the Driver Manager, rather than by pointer in each driver.
When an application needs a particular driver, it first requests a connection handle with which to identify the driver and then requests that the Driver Manager load the driver. The Driver Manager loads the driver and stores the address of each function in the driver. To call an ODBC function in the driver, the application calls that function in the Driver Manager and passes the connection handle for the driver. The Driver Manager then calls the function by using the address it stored earlier.
ODBC exposes a significant number of DBMS features but does not require drivers to support all of them: If ODBC exposed only features that are common to all DBMSs, it would be of little use; after all, the reason so many different DBMSs exist today is that they have different features. If ODBC exposed every feature that is available in any DBMS, it would be impossible for drivers to implement.
Instead, ODBC exposes a significant number of features more than those supported by most DBMSs but requires drivers to implement only a subset of those features. Drivers implement the remaining features only if they are supported by the underlying DBMS or if they choose to emulate them. Thus, applications can be written to exploit the features of a single DBMS as exposed by the driver for that DBMS, to use only those features used by all DBMSs, or to check for support of a particular feature and react accordingly.
So that an application can determine what features a driver and DBMS support, ODBC provides two functions (SQLGetInfo and SQLGetFunctions) that return general information about the driver and DBMS capabilities and a list of functions the driver supports.
ODBC also defines API and SQL grammar conformance levels, which specify a broad range of features supported by the driver. It is important to remember that ODBC defines a common interface for all of the features it exposes. Because of this, applications contain feature-specific code, not DBMS-specific code, and can use any drivers that expose those features. One advantage of this is that applications do not need to be updated when the features supported by a DBMS are enhanced; instead, when an updated driver is installed, the application automatically uses the features because its code is feature-specific, not driver-specific or DBMS-specific.
|
