|
ODBC Architecture The ODBC architecture has four components:
Application: Performs processing and calls ODBC functions to submit SQL statements and retrieve results.
Driver Manager: Loads and unloads drivers on behalf of an application. Processes ODBC function calls or passes them to a driver.
Driver: Processes ODBC function calls, submits SQL requests to a specific data source, and returns results to the application. If necessary, the driver modifies an application's request so that the request conforms to syntax supported by the associated DBMS.
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.
Multiple drivers and data sources can exist, which allows the application to simultaneously access data from more than one data source.
The ODBC API is used in two places: between the application and the Driver Manager, and between the Driver Manager and each driver. The interface between the Driver Manager and the drivers is sometimes referred to as the service provider interface, or SPI.
For ODBC, the application programming interface (API) and the service provider interface (SPI) are the same; that is, the Driver Manager and each driver have the same interface to the same functions.
ODBC is used from the calling application in one of the three ways. 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 series:
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 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 non-interoperable 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.
How does a typical ODBC program work?
A number of tasks are common to all applications, no matter how they use ODBC. Taken together, they largely define the flow of any ODBC application.
These are the following steps typically involved in using an ODBC data source from your application. We will explore these steps in more detail later:
1. Configure the data source on the terminal.
2. Connect to the data source.
3. Initialise the application.
4. Build and execute SQL statement.
5. Get the results from the data source.
6. Find out the number of rows returned.
7. Commit the transaction, if required.
8. Repeat steps 4 to 7 if required.
9. Disconnect from the data.
Because most data access work is done with SQL, the primary task for which applications use ODBC is to submit SQL statements and retrieve the results (if any) generated by those statements. Other tasks for which applications use ODBC include determining and adjusting to driver capabilities and browsing the database catalog.
SQL Grammar
Any ODBC driver must support certain minimum SQL grammar (functionality). An application can use any of this minimum grammar syntax and be assured that any ODBC-compliant driver will support that syntax.
This syntax is the bare minimum functionality required for SQL data access.
To determine whether additional features of SQL are supported, the application should use the SQLGetInfo function. Once the application knows that SQL features are supported, it can determine whether a higher-level feature is supported (if any) by calling SQLGetInfo with the individual information type corresponding to that feature.
Some of the commands that need to be supported are:
Creating a table: The CREATE TABLE statement is used to make a new table to store data. The fieldnames and datatypes are provided in the statement:
CREATE TABLE base-table-name
(column-identifier data-type
[,column-identifier data-type]...)
Deleting data:
This command provides for deletion of one or more rows from a table based upon a condition.
DELETE FROM table-name
[WHERE search-condition]
Deleting the table:
This command removes all the data and the table itself and deletes it.
DROP TABLE tablename
Adding information to a table:
The INSERT command provides for specifying the fields and the new information which will go in the table.
INSERT INTO table-name
[( column-identifier [, column-identifier]...)]
VALUES (insert-value[, insert-value]... )
Reading information from a table:
This command returns the data stored in the table. This is the only way of looking at the stored information.
SELECT [ALL | DISTINCT] select-list
FROM table-reference-list
[WHERE search-condition]
[order-by-clause]
Changing data:
The UPDATE statement changes information that is stored in the table.
UPDATE table-name
SET column-identifier = {expression | NULL }
[, column-identifier = {expression | NULL}]...
[WHERE search-condition]
Conformance Levels
As the ODBC architecture allows each vendor to expose the required functionality, there needs to be some way for the developers to get information about the current DBMS capabilities. This is achieved by categorising all ODBC drivers using a well known level of conformance with ODBC standards. Put simply, it provides a method, at runtime, using which the developer can ask the driver if it supports a certain feature. Depending upon the answer it gets the program can do the needful. There are three conformance levels for ODBC interfaces--Core, Level 1, and Level 2, details and code examples of which we shall cover later.
ODBC Components
ODBC components are installed and removed on a component-by-component basis, not on a file-by-file basis.
For example, if a translator consists of the translator itself and a number of data files, all these are installed and removed as a group; they must not be installed and removed on a file-by-file basis. The reason for this is to make sure that only complete components exist on the system. For purposes of installing and removing components, the following are defined to be ODBC components:
Core components: The Driver Manager, cursor library, installer DLL, and other related files make up the core components and must be installed and removed as a group.
Drivers: Each driver is a separate component.
Translators: Each translator is a separate component.
The base ODBC components are typically installed as a part of the operating system installation. ODBC core components can also be installed separately by running the ODBCINST program. When you load Office, Microsoft Development tools or client software for specific RDBMS engines, the relevant ODBC drivers are installed automatically.
|
