ODBC paper

Document Sample
ODBC paper Powered By Docstoc
					        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




                                                                                                                                                        2
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.




                                                                                             3
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.




                                                                                         4
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:




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




                                                                                             5
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 iODBC.org, 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.




                                                                                           6
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



                                                                                              7
           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);




                                                                                            8
$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



                                                                                               9
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:



                                                                                           10
          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_INSENSITIV
                        E, 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 }




                                                                                          11
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
http://java.sun.com. Compared to the Perl example, Java uses more lines of code but
handles errors more effectively.




                                                                                           12
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.




                                                                                          13
References
A History of ODBC Data Access. Simba Technologies. 2004. 22 October 2004.
       <http://www.simba.com/About/history.htm>

Haynes, Tim. “ODBC: The Unix Story”. 3 June 2003. iODBC.org. 22 October 2004.
      <http://www.iodbc.org/index.php?page=docs/odbcstory>

OBDC Overview. Arizona State University. 22 October 2004.
     <http://ces.asu.edu/ceslab/kb/ODBC.htm>

Perl Examples. California Polytechnic State University. 22 October 2004.
       <http://multiweb.lib.calpoly.edu/odbc/examples.html>

Scholl Consulting Group: ODBC History. FFE Software, Inc. 1996. 22 October 2004.
       <http://www.martinscholl.com/html/mhc/odbc_history.html>

SQL Access Group Members. X/Open Company Limited. 1995. 22 October 2004.
     <http://www.rdg.opengroup.org/public/news/nov95/sqlaccgp.htm>

The Win32::ODBC Faq. Roth Consulting. 13 September 2003. 22 October 2004.
      <http://www.roth.net/perl/odbc/faq/>


Java Class References

Connection (Java 2 Platform SE 5.0)).
      <http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Connection.html>

DriverManager (Java 2 Platform SE 5.0)).
      <http://java.sun.com/j2se/1.5.0/docs/api/java/sql/DriverManager.html>

ResultSet (Java 2 Platform SE 5.0)).
       <http://java.sun.com/j2se/1.5.0/docs/api/java/sql/ResultSet.html>

Statement (Java 2 Platform SE 5.0)).
       <http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Statement.html>




                                                                                   14

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:13
posted:8/18/2011
language:English
pages:14