ODBC



ODBC

Open DataBase Connectivity

Michael DiLuzio

9/28/2004

Table of Contents

Table of Contents 2

Introduction to ODBC 3

How and Why to Use ODBC 5

The ‘Not So Boring’ History of ODBC 6

How to Set Up an ODBC Data Source in Windows 7

Examples of ODBC in Perl 8

JDBC- Java’s ODBC Packages 9

Summary 13

References 14

Java Class References 14

Introduction to ODBC

What is ODBC? Well, it stands for Open DataBase Connectivity. That’s a start but it doesn’t tell us much. So let’s look at programming without it. Let’s say you have a database full of statistics about baseball. Now, you want to create a program in C++ which is just a front end to the database- that is that the user can query and display information without having to know SQL. This should really not be that hard of a project and should take a mediocre programmer maybe a day to code the GUI for this. Problem is that without ODBC, the programmer would have to create a way for their application to communicate with the DBMS. Not many people are that familiar with DBMS architecture so this mediocre programmer with the nice GUI would spend the rest of their life trying to connect their GUI to the database. Enter ODBC. With OBDC, all the drivers that you need to connect an application to a database are provided. With a few lines of code you can connect your application to a database and query information, add information, delete information, and anything else you can do with a DBMS.

Just from this example, you can see that life without ODBC would stink. Most of the time developing an application that uses a database would be spent trying to connect them together. Development time and costs would skyrocket. Programmers would revert to storing everything in text files as was the case before ODBC. Large programs would be unstable if the file gets corrupted. Planes would fall out of the sky, lakes would dry up, and disease and famine would devastate the land. Ok, maybe it wouldn’t be that bad, but you get my drift. Another means of data access before ODBC was Embedded SQL. This required a pre-compiler specific to the language that the application is written in. This pre-compiler “translates the embedded commands into host language statements that use the native API of the database (Scholl Consulting Group: ODBC History)”. This was bad because the program had to be recompiled using the right pre-compilers if you wanted to change the type of database.

So where did ODBC come from? Was it given to Moses along with the Ten Commandments (yes, it’s that important)? No. Microsoft decided that the old methods of Embedded SQL and text files stunk so they created the SQL Access Group (SAG). This was a consortium of companies that all specialized in data access. Some others besides Microsoft were Simba Technologies, AT&T, IBM, Hitachi, Borland, and Oracle (SQL Access Group Members). Representatives met over potato chips and bottles of Mountain Dew and came up with the ODBC API. Parts of the API were from other companies attempts at creating a middle layer between databases and applications. Some applications that have utilized ODBC include Microsoft Office suite of programs, ArcView GIS, MapInfo, SPSS, and Autocad LT (ODBC Overview). In the rest of this paper, we will look at the history of ODBC, how to set up an ODBC data source, and some examples of connecting to databases using both Perl and Java.

How and Why to Use ODBC

The main thing that ODBC does is provide abstraction on top of a database, to transfer SQL queries to that database, and to provide a means for the application to get those results. Since ODBC was created by the SAG which was brought together by Microsoft, at first it could only be used under Windows. But, other companies have now created ODBC drivers and packages that can be used on UNIX and Linux machines. This has led to the explosion of databases on the web. UNIX and Linux run about 80 percent of all web pages and without ODBC on these machines, the web would be a lot less interactive and many sites would not be able to use databases.

ODBC consists of packages that can be imported into any programming language that supports ODBC. Some examples of languages that support ODBC are C, C++, PHP, Perl, and Java (Java has something called JDBC which is build on top of ODBC). When you use a command defined by that package, it invokes the driver manager. This is part of the operating system. The manager then uses the correct driver to connect to the database and does what the application tells it to. Here is a simple diagram that I made to explain and entertain:

[pic]

As you can see, one great advantage that ODBC does is you can change the database and never have to change anything in your application. You can change from a MS Access database to an Oracle database and your code will not have to be changed or recompiled. The ODBC manager takes care of getting the right driver to connect to the database.

The ‘Not So Boring’ History of ODBC

Ok, so the history of ODBC isn’t as exciting as… um… well it’s just not that exciting. Nevertheless, it’s well worth looking at. As explained before, the SAG decided to come up with a very open-ended solution to connecting applications to many different types of databases. According to Tim Haynes of , an organization creating an open source version of ODBC for UNIX and other operating systems, ODBC is primarily a C based API. Some of the drivers that are used by the driver manager are written in other languages like C++.

The first version of ODBC, the child of the SAG, was 1.0. The first versions were basically modified versions of technology that Simba Technologies had developed over the years. SAG laid down some ground rules, updated Simba’s technology, and integrated it into the Windows operating system (A History of ODBC Data Access). Since then, there have been versions 2.0, 2.5, 3.0, and 3.5. The current version is 3.5, which is completely backward compatible as long as the application only uses commands defined in the version before it. For example, an application created for ODBC v3.0 will work on v2.0 as long as the application uses only v2.0 features. Keep in mind that the Win32 (Windows) version of ODBC is based on the version 2.0 API (Win32::ODBC Faq).

So, that’s the short history if ODBC. Besides some stability and security upgrades throughout the versions, not much has changed. I believe that from version 1.0 to version 2.0, ODBC changed from 16-bit to 32-bit and that’s why Windows ODBC is based off version 2.0. Most development has been on drivers to interface with the driver manager on one side and the actual DBMS on the other.

How to Set Up an ODBC Data Source in Windows

When you create a program that utilizes ODBC, you need to set up the data source (the database) so that the operating system knows where to find it. Remember that ODBC is part of the operating system, allowing your application to interface to any database that is ODBC compliant. This example of how to set up an ODBC data source will be for the Windows operating system, more specifically Windows XP.

While operating systems like Linux and UNIX also have ODBC, they are created by third party vendors and need to be installed separately. Therefore, setting up an ODBC data source in these environments depends on how that specific version of ODBC needs to be set up.

So, you’re creating an application that needs to use a database under the Windows XP environment. The first thing you need to do is to create the database and store it somewhere. Let’s say that you create a Microsoft Access database called db1.mdb and store it in the C:\Inetpub directory (Microsoft’s Internet Information Server root folder).

To set up the data source, do the following:

1. Go to the Control Panel, open Administrative Tools, and click on Data Sources (ODBC).

2. Under the User DSN tab, click Add.

3. Now, you get to select the driver to use. For a Microsoft Access Database, you use Microsoft Access Driver (.mdb). Select it and click Finish.

4. Now, pick a data source name and type it in. You will need this later when telling your program which data source you want to connect to. As you can see in the above graphic, some of the data source names on my computer are DRINKS, MLB_SALARIES, SCHEDULER_DATASOURCE, and stats.

5. Now, select your database. Click Select under Database, find, and select your database. If you don’t have one yet, click create and make one.

6. You can also set advanced options like a username and password if your database uses one by clicking Advanced. Finally, click OK and you have an ODBC data source set up. Congratulations!

Examples of ODBC in Perl

Now, we know a few things about ODBC. We have an abstract idea of what it is (remember that picture with the talking driver manager) and we know how to set up a data source. Let’s learn how to actually use it and do cool stuff to databases. Remember, all these examples are for Windows. But, all you need to do to move it over to UNIX or Linux is change what packages are imported. These examples come from the California Polytechnic State University web site (Perl Examples).

The first thing you need to do is import the ODBC package so the compiler knows what you’re talking about:

use Win32::ODBC;

So, now connect to the database like so:

$dsn = “NAME_OF_DATASOURCE”;

$db = new Win32::ODBC($dsn);

$db is a variable which ends up pointing to the database and $dsn is a variable holding the name of the data source. Next, formulate some SQL statements and apply them to the database. Keep in mind that things like INSERT, CREATE, and DELETE do not have to return information while statements like SELECT return rows of data. This is how to apply either type of statement to the database.

$sql = “INSERT INTO people (id) VALUES (‘0001234’{‘id’})”;

$db->Sql($sql);

For this code, you do not need to do anything else because nothing is returned. But, let’s look at a SELECT statement and learn how to get the values back from the database.

$sql = “SELECT * FROM customers”;

$db->Sql($sql);

while ($db->FetchRow())

{

($id, $name, $city) = $db->Data("id", "name", "city");

DO WHAT YOU WANT WITH THE DATA HERE

}

After executing the SQL statement, you can then loop through the fetched data. After the SQL is executed, a table of results is created. A pointer points at the spot just before the first row of data. Code execution enters the while loop and FetchRow() increments to the first row of data fetched. You get the data into variables using the Data() method and process however you want from there. Once the FetchRow() method has exhausted all the fetched rows, the while loop stops executing.

One last thing to do is to close your connection to the database.

$db->Close();

That’s it! See how easy using ODBC is. In just six lines of code, you could open a connection to a database, insert a row into a table, and close the connection again. Just think how hard this would be without ODBC.

JDBC- Java’s ODBC Packages

Just like Perl, Java has packages that you can import to talk to the ODBC driver manager. Java refers to this as JDBC, or Java DataBase Connectivity. It is just as simple as Perl but has extra things like error handling that makes the code a little longer. Here are some examples from a project that I made in software engineering.

First, you need to import the package that lets you use JDBC. It’s all contained in a package called java.sql.

import java.sql.*;

Here’s something that differs from Perl. You need to specify the driver that you want to use. Usually the JDBC-ODBC Bridge Driver will work. Load it using the following code:

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Next, create a connection to the data source. Just like Perl, you need the name and if needed, the username and password. Unlike Perl, Java has error handling and thrown errors must be caught. The second line of code can throw an SQLException. Java’s web site says that this error is thrown “if a database access error occurs” (DriverManager).

String url = "jdbc:odbc:" + “DATASOURCE_NAME_GOES_HERE”;

try

{

Connection con = DriverManager.getConnection(url, “ USER_ID_IF_NEEDED”, “PASSWORD_IF_NEEDED”);

} catch (SQLException e) { ERROR HANDLING HERE }

If you do not need a user id and password, just omit them and pass in the url as the only parameter (Connection).

Next, provided an error did not occur while connecting to the data source, create a statement. A statement is a means to execute an SQL statement on the database. The connection to the database creates the statement. Remember the two types of SQL statements from before (ones that return data and ones that don’t)? They come into play here. If you are retrieving data, it is a good idea to specify the format you want the data to come in. If you are updating, deleting, creating, or adding data, you do not need to specify anything. As with the connection, creating a statement can throw an SQLException (Statement).

To create a statement that will NOT return data:

try

{

Statement stmt = con.createStatement();

} catch (SQLException e2){ ERROR HANDLING HERE }

To create a statement that will return data, we need to learn about ResultSet. A ResultSet is a Java interface (you cannot create an instance of a ResultSet) that allows us to access the data retrieved. It defines some constants that correspond to how the ResultSet behaves. The following code creates a statement that, when it returns data, will give us a ResultSet where we can scroll up or down rows but cannot update values in the ResultSet. (ResultSet).

try

{

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

} catch (SQLException e2) { ERROR HANDLING HERE }

Now, we can execute SQL statements. Create a statement and use the Statement you created to execute it like so:

try

{

stmt.executeUpdate(“INSERT INTO people (id) VALUES (‘0001234’{‘id’})”);

} catch (SQLException) { ERROR HANDLING HERE }

The SQLException is thrown if a database error is encountered or if for some reason your SQL statement returns a ResultSet. Now, if you want to retrieve values, do the following:

try

{

ResultSet rs = stmt.executeQuery(“SELECT * from CUSTOMERS”);

while (rs.next())

{

String name = rs.getString(“name”);

int id = rs.getInt(“id”);

DO WHAT YOU WANT HERE

}

} catch (SQLException) { ERROR HANDLING HERE }

Finally, just like Perl, close the connection.

con.close();

One thing to remember about these examples is that if you put each line in its own try / catch block, Java will not compile it. Let’s say the connection statement fails. Your error handling code runs and then the JVM starts the next try / catch block. Since there was an error in the previous block, the connection variable was never created so Java will not know what con.createStatement is. Java’s compiler catches these errors and will not compile. It is a good idea to create all the variables before hand and set them all to null or to put all the Connection, Statement, and ResultSet commands in one try / catch block.

This Java example is very simplistic. Sun has provided thousands of other commands that can be used to increase security and stability of database access that we cannot go into here. You can find more information on JDBC on Java’s web site which is . Compared to the Perl example, Java uses more lines of code but handles errors more effectively.

Summary

ODBC has provided programmers with an easy to use API. Without it, programmers would be stuck programming their own way to interface databases. It would be time consuming, non-portable, insecure, and unstable. Programming time and costs would increase and many programmers would just ditch databases altogether and go back to text files. The ability to create an application that will be portable to any database system on any platform and work without recompiling or rewriting code has changed software engineering and web development forever.

ODBC has allowed programmers without intensive knowledge of databases the ability to integrate them into their applications. In the web environment, ODBC has allowed anyone and everyone to create simple Perl or Java CGIs and Servlets that access information from a database. Had it not been for the early work of some companies and then the creation of the SQL Access Group, many web applications would be either too insecure or unstable to use. Imagine the web without sites like Yahoo or eBay or having to schedule classes without info.ship.edu. All of these sites built the bridge between their application and their databases using ODBC.

References

A History of ODBC Data Access. Simba Technologies. 2004. 22 October 2004.

Haynes, Tim. “ODBC: The Unix Story”. 3 June 2003. . 22 October 2004.

OBDC Overview. Arizona State University. 22 October 2004.

Perl Examples. California Polytechnic State University. 22 October 2004.

Scholl Consulting Group: ODBC History. FFE Software, Inc. 1996. 22 October 2004.

SQL Access Group Members. X/Open Company Limited. 1995. 22 October 2004.

The Win32::ODBC Faq. Roth Consulting. 13 September 2003. 22 October 2004.

Java Class References

Connection (Java 2 Platform SE 5.0)).

DriverManager (Java 2 Platform SE 5.0)).

ResultSet (Java 2 Platform SE 5.0)).

Statement (Java 2 Platform SE 5.0)).

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download