ODBC Research Paper All Together

Document Sample
ODBC Research Paper All Together Powered By Docstoc
					                               Baish 1

  Open Database

Connectivity (ODBC)

      Written by Darin Baish
                                                                                                 Baish 2

                                   Table of Contents

What is ODBC..................................................................................pp. 1,2

Before ODBC……………………………………………………….p. 2

How does ODBC work (the 4 parts)………………………………..pp.3,4

The Database Administrator & DSN………………………………..pp.5,6

Advantages, Disadvantages, Summary……………………………...pp.7,8,9
                                                                                    Baish 3

Darin Baish

Professor Girard

Web Programming

22 October 2004


       There are many different types of databases that exist in the world today. Each

one contains it’s own file formats, and it’s own ways to retrieve the information that is

contained within that individual database. Databases are becoming more and more

widespread as more companies are realizing the huge potential of e-commerce. These

Internet companies retain tremendous amounts of information about customers in their

databases. Everything from the customer’s name to the customer’s credit card number is

contained within their database that they utilize. Since there are so many different types

of databases in operation today, it would be appropriate to have some way of retrieving

information from those databases without having to gain knowledge of how to program

each and every one of those databases. Microsoft realized this over a decade ago and

began working on what would come to be known as ODBC. Before the introduction of

ODBC, applications written for a certain database could not work on another database.

The application was limited to whatever database that it was written for. The Microsoft

Corporation understood this dilemma and worked to fix it. ODBC, which stands for

Open Database Connectivity, is basically a set of application programming interfaces that

allow a program to interact with many different databases (Naude). This allows a

programmer to connect to Oracle, Microsoft Access, as well as many other databases

without learning the separate code for each of those databases. All a programmer needs
                                                                                   Baish 4

to be familiar with is how to code in ODBC, and that allows access to all the different

types of databases in the world that can use ODBC (Welcome). ODBC definitely has

many advantages, but with any product out on the market there are some disadvantages.

Many of the advantages that it offers for a programmer coding an application are easily

seen on the surface. The fact that a programmer does not have to learn how to program

different types of databases, as well as the fact that ODBC gives you a uniform way of

retrieving information from different types of databases is definite advantages (Naude).

The disadvantages of ODBC include such things as an error prone environment, and the

fact that an excessive amount of knowledge is required to be able to support an ODBC

application. However, ODBC’s strengths definitely outweigh its weaknesses, and the

fact that ODBC is difficult to become skilled at and code has not deterred people from

using it as a means to connect and retrieve information from databases.

       Open Database Connectivity, or ODBC, was created back in the early nineties by

the Microsoft Corporation (A History). Before ODBC, programmers would have to code

their program for the particular database that the program connected to. If that program

wanted to gain access to three different types of databases, then the programmer would

have to make three different types of code for that single program. This was not only a

waste of time, but it was also a waste of money since the programmer could not work on

any other project and had to spend time programming the same application for a different

database. If an application was created that eliminated the necessity to code a program

multiple times for different databases, companies would save a lot of time, and a lot of

money. Microsoft realized this and wanted to do something about the problem. They

decided to create ODBC. ODBC is based on the SQL Access Group’s, better known as
                                                                                       Baish 5

SAG, Call Level Interface specification (ODBC: Open). ODBC allows applications to

access data that is stored in different types of databases and in different formats. It is

widely used to gain access to relational data in the database. The convenience of being

able to access multiple databases is offset with the complexity of the ODBC code.

ODBC is hard to code, but many people climb over that hurdle due to the huge benefits

that ODBC offers. There have been a few different versions of ODBC, and each new

version has some revisions to the application programming interface. The current version

that is in use is ODBC version 3.5 (Welcome).

       Open Database Connectivity decreases the cost, and the amount of time it takes to

complete a project. To use ODBC efficiently, there must be four parts (Naude). The first

part is the application that is coded by a programmer. The application is written so that it

is able to communicate with ODBC. The code itself is extremely complex, but the many

advantages offset the complexity. In today’s world, it would be foolish to write a

program multiple times just so that the program could interface with more than one

database. The second part is the Driver Manager of ODBC. The Driver Manager

provides the ODBC interface to an application. The Driver Manager is also responsible

for loading the correct Database Driver that is desired by the application (Naude). The

Database Driver, which is the third part of ODBC, handles the translation between

ODBC’s application programming interface, and each database that it connects to. Every

single database that uses ODBC must contain its own Database Driver (Naude). When a

database is purchased by an individual, the software for that database usually contains a

Database Driver if the database supports ODBC. If the software for that database does

not contain a Database Driver and it does support ODBC, then most likely that particular
                                                                                   Baish 6

driver can be easily downloaded off the Internet for use on your system. The fourth and

final part of ODBC is the database itself. To make ODBC work effectively, you must

obviously have a database to connect to. The programmer writes an application to

connect to a database, which is either on the local machine or on a server machine. The

Driver Manager is then loaded from the program, and the correct Database Driver that

corresponds to the database that is required is found and loaded. The Database Driver

then communicates with the database, and information is either retrieved or sent into that

database. Data from the database is now able to be manipulated by adding, deleting, or

moving around the data. Figure 1, which is shown below, shows a graphical

representation of the four parts that make ODBC work together and effectively.

            Figure 1 - The four parts that makes ODBC work
                                                                                     Baish 7

       There is also a small application that is called the Database Administrator. The

Database Administrator is a small graphical user interface program that resides on just

about every computer that contains ODBC. The Database Administrator can usually be

located under the control panel from the Start menu on Microsoft Windows. When the

program is double clicked and it opens, a person can see the drivers that his/her machine

has on it, as well as many other little details such as the version number and tracing

features. Usually the first tab on the Database Administrator is the User Data Source

Name. It is actually the Data Source Name that is used to connect to the individual

database that the program wants to connect to. The application that is coded by the

programmer locates the Data Source Name, and that Data Source Name corresponds to a

certain Database Driver. Once the Data Source Name is known, the Driver Manager is

loaded and retrieves the Database Driver that corresponds to that Data Source Name.

After that happens, the Database Driver is then loaded. Once that occurs, communication

between the application and the database can begin and information can be shared back

and forth (ODBC: a Technical). It seems like the process is extremely complex, but

once a person knows how to program in the ODBC application programming interface,

which is held together by C functions, he/she will definitely realize the benefits of using

ODBC to connect to many different types of databases. Below is Figure 2. Figure 2

shows the Database Administrator which is opened by double clicking the icon that says

ODBC in the control panel. It has a listing of Database Drivers that are installed on the

computer. It allows the user to add, remove or configure the Database Drivers (ODBC: a

Technical). The Database Administrator also has other tabs that allow creation and

modification of other items (Setting).
                                                                                   Baish 8

          Figure 2 - The Database Administrator

       Before a programmer can use ODBC to connect to a database, there are some

tasks that must be done. First, the correct ODBC Database Driver for the database that is

going to be accessed must be installed on the local system that the program will run off

of. Second, the user must set up the correct ODBC Data Source Name so that the

application that is coded can connect to the desired database. This task can be

accomplished multiple ways. The first and most simplistic way to set up the correct Data

Source Name is to use the Database Administrator (Setting). Another way of setting up

the Data Source Name is to hard code it into the program that is being written.
                                                                                     Baish 9

       Open Database Connectivity was created in the early nineties by the Microsoft

Corporation (A History). Microsoft realized the problem that a lot of programmers were

having when writing applications that dealt with more than one database. Different code

that performed essentially the same task had to be written multiple times in order for the

application to work with different databases. This resulted in a waste of time and a waste

of money for many big and small companies. Microsoft decided to write an application

programming interface that would allow an application to be written so that it was not

limited to just one certain database. Microsoft called their technology Open Database

Connectivity, or ODBC. ODBC really changed the way programmers coded their

applications that had to interact with a database. There were other such technologies out

there when ODBC came into the picture, but because of Microsoft’s advertising and

constant pushing of their product, it became the industry standard. ODBC has many

advantages when it comes to coding an application to interact with a database. One is the

fact that it allows access to many different types of databases (ODBC: Open). It allows a

uniform way of retrieving information. ODBC is highly efficient and has reasonable

memory requirements, which is always a plus for any product. It is also very portable

across many operating systems. The technology is extremely fast, but with all these

advantages comes disadvantages. The application code is very complex and has a steep

learning curve. Another disadvantage is that ODBC requires that all the information

must look like a relational database. That is completely fine, however if the information

is not in a relational database it can be extremely difficult to write an ODBC driver that

exposes the data, because the programmer would have to write, on top of the original

structure, the relational engine. With all these different items to worry about, it can be
                                                                                      Baish 10

exceptionally easy to make a mistake when writing the application. However, some

would argue that it is easy to make a coding mistake in any environment and that would

be a valid point. One last disadvantage is simply the fact that coding in ODBC requires a

lot of knowledge, skill, as well as experience. Many programmers that are coding an

application to interact with a database decide to use ODBC despite the many

disadvantages because the programmers feel that the benefits far surpass the

disadvantages. ODBC contains four parts to make it work effectively (Naude). There is

the application that is written so that it can interact with a database. Then, there is the

Driver Manager, which loads the correct Database Driver that the application needs. It is

the Database Driver that connects to the correct database (Naude). It is these four parts

that make Open Database Connectivity possible. Each database that is used in a program

must have it’s own Database Driver. The Database Driver is usually bundled with the

software for the database that is installed. If it is not, then the correct Database Driver

can usually be found very easily on the World Wide Web. A user can see what drivers

are installed on the current computer by going into the control panel and double clicking

on the ODBC icon. The Database Administrator opens up and is shown. The current

drivers along with any other information that is needed can be obtained from this simple

graphical user interface program. Creation and modification of User Data Source Names

can be performed in this window. It is actually the Data Source Name that is coded into

an application (Setting). The Driver Manager is then loaded. The Data Source Name

refers to a certain Database Driver and that driver is then loaded by the application and

information can be had from the database in which the application uses. Open Database

Connectivity is a fantastic piece of technology that has moved the development of
                                                                              Baish 11

database applications to another level. The future of ODBC is unknown; however it

seems that it is here to stay.
                                                                           Baish 12

                                    Works Cited

A History of Data Access Innovation. 2004. Simba Technologies Incorporated. 10 Oct.

      2004 <>.

Naude, Frank. Oracle ODBC Connectivity FAQ. 10 April 2004. Oracle FAQ’s. 3 Oct.

      2004 <>.

ODBC: a Technical Overview. December 2000. Data Access Worldwide. 6 Oct. 2004


ODBC: Open Database Connectivity Overview. 3 September 2004. KBAlertz. 7 Oct.

      2004 <>.

Setting up a Database through ODBC. National Instruments. 3 Oct. 2004



Welcome to Win32::ODBC. 13 Sept. 2001. Roth Consulting. 1 Oct. 2004