Back to Research Page

About JDBC

Outline:  

1.  Introduction to JDBC

2.  The Teradata Driver for the JDBC Interface

3.  Overview of JDBC Architecture

4.  Using JDBC in Java Programs

5.  An Application Example

///////////////////////////////////////////////////////////////////////////////////////////////////////

1.  Introduction to JDBC.

Java Database Connectivity (JDBC) is a specification for an application programming interface (API).  This API allows platform independent Java applications to access database management systems using Structured Query Language (SQL).

The JDBC API contains a standard set of interfaces that provides for opening connections to databases, executing SQL statements, and processing results.  These interfaces are shown in the following diagram.

2.  The Teradata Driver for the JDBC Interface.

The Teradata JDBC driver is a set of Java classes that work with the JDBC interface, enabling you to access the Teradata RDBMS using the Java language and shows the relationship between JDBC and the Teradata JDBC driver.

3.  Overview of JDBC Architecture.

The Teradata JDBC driver uses a three-tier architecture to access to the Teradata database as shown below.

The Teradata JDBC Java classes use the TCP/IP protocol to connect to the Teradata JDBC gateway, which is constantly listening on the network port for connection requests.  For each gateway connection, a new session is created.  The Java program can select to work with different gateways by using different URL's.

All JDBC function requests are routed to the gateway, which in turn accesses the Teradata database using Teradata CLI.  More than one gateway can run on the same host if the gateways are configured to use different network ports.

The three tire access architecture offers the following benefits:

The client portion of the Teradata JDBC driver is platform independent and can be used on any machine which has a Java enabled brower or  Java Virtual Machine Interpreter installed.  

The Server portion of the Teradata JDBC driver is called Teradata JDBC Gateway, and it runs on a UNIX MP-RAS server.  The Teradata JDBC Gateway connects the Teradata JDBC driver clients to the Teradata database and acts as a middleware tier, controlling and managing the database access.

4.  Using JDBC in Java Programs.

There are two types of Java Program.

These two types of Java Programs dictate somewhat different configurations and uses for the Teradata JDBC driver software.

The Java applet is a Java program designed to be downloaded via the network from a Web server as part of a Web document (HTML).  Once the applet is downloaded, the browser executes the applet in a Java virtual machine.  The code for these Java applet (applet classes) resides on the Web server containing the HTML Web document.

Because the applet is downloaded from a network (Internet), it is considered to be an 'untrusted' application on the client computer.  Web browsers are designed to impose significant limitations on the operations applets are allowed to perform, such as not allowing them to access local files or to perform network connections to an arbitrary network host.  The later is very important, because the Teradata JDBC driver classes need to connect to the Teradata JDBC Gateway.  Since the applets are only allowed to connect to the Web server host, the Teradata JDBC gateway must run on the same host as the Web server serving the applet.

Note:  The access limitations enforced on remotely loaded applets are imposed by the browser.  Future browser designs may use the notion of 'trusted applets', which are applets the browser decides have come from a reliable source and therefore can be trusted to perform all operations.  For example, they might be 'trusted' because they have been signed with a particular cryptographic key, or because the user has decided to 'trust' applets from a particular source.

The Java application is a program designed to run as a standalone application in a Java virtual machine.  Unlike Java applets, Java applications are allowed to read and write files, open network connections to other hosts, etc., just like any other application program.  A Java program can connect to any Teradata gateway, so there is restriction on where the Teradata JDBC Gateway must run.

When accessing the Teradata JDBC driver from a Java application, the driver Java classes must be installed somewhere in the CLASSPATH on the computer running the Java application; the Teradata JDBC gateway must be installed and running on a UNIX MP-RAS system with network access to a Teradata RDBMS.  The Java application may access more than one gateway, on more than one computer.

5.  An Application Example:  This example is from the JDBC reference manual published by NCR.

Import java.net.URL;

Import jdbc.sql.*;

Import java.io.*;

Import jdbc.teradata.*;

Class SimpleSelect

{

            public static void main (String args[])

            {

            //Create A URL specifying a TERA-MP-RAS data source name

                        //In this example gateway server is running on opnsrv01 and Teradata is running on asf1

                        String url = “jdbc:tera-mp-ras://opnsrv01:6000/asf1/tmod=default”;

                        try

                        {

                                    Class.forName(“TeraDriver”);

                                    //Connect to the Teradata database specified in url and submit user & password

                                    Connection con = DriverManager.getConnection (rul, “guest”, “please”);

                                    //Execute a SELECT statement

                                    Statement stmt = con.createStatement ();

                                    ResultSet rs = stmt.executeQuery (“Select * from guest.table1 order by 1;”);

                                    //Display the results – see function below

                                    dispResultSet(rs);

                                    //Close the result set, statement and connection

                                    rs.close();

                                    stmt.close();

                                    con.close();

                        }

 //If an error occurs do the following

catch (SQLException ex)

{

            System.out.println (“\n***SQLException caught***\n”);

            While (ex != null)

            {

                        System.out.println (“SQLState: “ +ex.getSQLState ());

                        System.out.println (“Message: “ +ex.getMessage ());

                        System.out.println (“Vendor: “ +ex.getErrorCode ());

                        ex = ex.getNextException ();

                        System.out.println (“”);

            }

}

catch (java.lang.Exception ex)

                        {

                                    ex.printStackTrace ();

                        }

}

private static void dispResultSet (ResultSet rs)

                        throws SQLException

{

                        int iColCnt;

                        //Get the number of  columns from the metadata

                        ResultSetMetaData rsmd = rs.getMetData ();

                        int numCols = rsmd.getColumnCount ();

                        //Position the cursor to the beginning of the first row – if no rows, returns false

                        boolean more = rs.next ();

                        while (more)

                        {

                                    for (iColCnt = 1; iColCnt <= numCols; iColCnt++)

                                    {

                                    //This prints all column results as strings

                                                if (iColCnt > 1) System.out.print(“,”);

                                                System.out.print(rs.getString(iColCnt);

                                    }

                                    System.out.println(“”);

                                    More = rs.next ();

                        }

}

}