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 ODBC 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 effectively 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 <http://www.simba.com/About/history.htm>. Naude, Frank. Oracle ODBC Connectivity FAQ. 10 April 2004. Oracle FAQ’s. 3 Oct. 2004 <http://www.orafaq.com/faqodbc.htm>. ODBC: a Technical Overview. December 2000. Data Access Worldwide. 6 Oct. 2004 <http://www.dataaccess.com/whitepapers/odbc.htm>. ODBC: Open Database Connectivity Overview. 3 September 2004. KBAlertz. 7 Oct. 2004 <http://www.kbalertz.com/kb_110093.aspx>. Setting up a Database through ODBC. National Instruments. 3 Oct. 2004 <http://zone.ni.com/devzone/conceptd.nsf/webmain/5A7CC41C5C75545D86256 7C8005DBAFB>. Welcome to Win32::ODBC. 13 Sept. 2001. Roth Consulting. 1 Oct. 2004 <http://www.roth.net/perl/odbc/faq/>.