Open Database Connectivity (ODBC):
Definition and Explanation:
Open Database Connectivity (ODBC) is a standard interface used
for accessing data of databases. The application that uses ODBC interface can
access and process data of different types of databases such as Oracle database,
Ms-Access, and many other types of databases. Using the ODBC standard, you can
access information in a SQL database server, Oracle server etc. You can also
access PC-based database using ODBC standard. ODBC is also very important for
ODBC was developed in the early 1990's to provide a DBMS independent means
for processing the data of relational database. ODBC is a component of
Microsoft's Windows Opened System Architecture (WOSA). In other
words, you can say that ODBC has been implemented by Microsoft and is a part of
Windows operating system. It is mostly used in client-server database systems.
The client application can access data of different types of databases on the
Components of Open Database Connectivity (ODBC) Architecture:
Open Database Connectivity (ODBC) standard consists of
different components, and each performs its role to access and process the data
The major components of ODBC are:
(i). Application program
(ii). ODBC drivers
(iii). Driver manager
(i). Application program
The application program is very important component of Open
Database Connectivity. It provides interface, which makes it easier to access
data from database.
It performs the following tasks:
- Sends requests to create a connection with a data source. A Data Source
is a database & associated DBMS, operating system, and network platform.
- Sends the SQL statements to database server and receives results etc.
ODBC provides a standard means for each of these requests. It also defines a
standard set of error codes and messages.
(ii). Open Database Connectivity (ODBC) Drivers:
Open Database Connectivity (ODBC) drivers are the DLLs that
contain functions for connecting to various databases. An ODBC driver processes
ODBC requests and also submits SQL statements to the data source. It is the
responsibility of a driver to ensure that requests submitted by ODBC are
executed correctly. The driver also translates data source, error codes, and
messages into the ODBC standard codes and messages.
Functions of ODBC Driver:
The basic functions of the ODBC driver are as follows:
- Provides the database connection.
- Prepares and executes SQL statements.
- Processes the transactions.
- Returns the result of queries.
- Informs about the application errors.
Types of ODBC Drivers:
There is a different driver for each type of database such as Oracle
database, Ms-Access database etc. Drivers are supplied by DBMS vendors and are
also available of different software companies.
The ODBC drivers are divided into two types.
Single-Tier Driver is used to connect to PC-based database system. It
processes both ODBC requests and SQL statements.
Multiple-Tier Driver is used to connect to Client/Server database
system. It processes ODBC requests but passes the SQL statements to the database
server, where they are processed.
(iii). Driver Manager:
The function of the driver manager is between application
program and ODBC driver. The driver manager processes the initialization
requests and validates the format and order of ODBC requests that it receives
from the application programs. The driver manager is provided by the Microsoft
and is included in Windows operating system.
Setting Up ODBC Data Source:
A data source identifies the database and the DBMS that processes it. Before
you can use ODBC to connect to a database, the ODBC drivers must be installed on
your system. Each type of database uses a specific ODBC driver.
You can setup the ODBC data source on your system, using the Windows ODBC
manager. The ODBC manager is a component of Control Panel of Windows. The icon
of the ODBC manager is labeled with 32-bit ODBC. as shown in figure.
When you open the ODBC manager, the ODBC Data Source Administrator dialog box
is opened as shown in figure below. The ODBC Data Source Administrator dialog
box contains different tabs used to add data sources. The titles of the first
three tabs ends with DSN, which stands for Data Source Name.
The ODBC data source is divided into three types.
(i). User Data Source
(ii). System Data Source
(iii). File Data Source
(i). User Data Source:
A user DNS information is stored in the registry of the local machine. It is
only available to the user who creates it.
(ii). System Data Source:
A system DSN is one that is local to a single computer. The operating system
and any user on that system can use a system DSN. It means that once the system
DSN has been set up, all programs and services running on that computer can
access it. For example, if you are using Internet information server to connect
to a database, you will probably be setting up a system DNS for the database.
In general, the system DSN is created on the Web server for Web applications.
When the user accesses the Web server through browser, the system DSN
establishes the connection with DBMS and the database.
(iii). File Data Source:
A File Data Source stores DNS information in a text file. The text file is an
INI file containing information about the database driver and its location. It
is not associated with a particular machine, so it can be on a network drive.
This file can be shared among database users. This file can, also be e-mailed or
otherwise distributed to different users.