Using jtds to access MS SQLServer databases

Is there anyone out there who has successfully accessed a MS SQLServer
database table with the jtds product (http://jtds.sourceforge.net)? Is
sample code available? Are there any known issues with configuration or
dependencies that I need to know about?

I tried to build a quick test Java program to connect, but I couldn't
get it to run. I am getting a null pointer exception when I initialize
the class. I got the Microsoft JDBC programs to run and connect, but
there appear to be some functions of jtds which would serve my purposes
better.

As near as I can determine, the program finds an error during the call
to to constructor for class jtds2.

Error in jtds2.main: java.lang.NullPointerException

java.lang.ExceptionInInitializerError

      java/lang/Error.<init>()V+1 (Error.java:43)

 
java/lang/ExceptionInInitializerError.<init>(Ljava/lang/Throwable;)V+1
(ExceptionInInitializerError.java:46)
      jtds2.<init>()V+0 (jtds2.java:105)

      jtds2.main([Ljava/lang/String;)V+0 (jtds2.java:62)


Sample java program follows (modified from the IBM toolbox for Java JDBC
example):


////////////////////////////////////////////////////////////////////////
//////////
//
// BasicJDBC example.  This program uses the native JDBC driver for the
// Developer Kit for Java to build a simple table and process a query
// that displays the data in that table.
//
// Command syntax:
//    BasicJDBC
//
////////////////////////////////////////////////////////////////////////
//////////
//
// This source is an example of the IBM Developer for Java JDBC driver.
// IBM grants you a nonexclusive license to use this as an example
// from which you can generate similar function tailored to
// your own specific needs.
//
// This sample code is provided by IBM for illustrative purposes
// only. These examples have not been thoroughly tested under all
// conditions. IBM, therefore, cannot guarantee or imply
// reliability, serviceability, or function of these programs.
//
// All programs contained herein are provided to you "AS IS"
// without any warranties of any kind.  The implied warranties of
// merchantability and fitness for a particular purpose are
// expressly disclaimed.
//
// IBM Developer Kit for Java
// (C) Copyright IBM Corp. 2001
// All rights reserved.
// US Government Users Restricted Rights -
// Use, duplication, or disclosure restricted
// by GSA ADP Schedule Contract with IBM Corp.
//
////////////////////////////////////////////////////////////////////////
//////////

// Include any Java classes that are to be used. In this application,
// many classes from the java.sql package are used and the
// java.util.Properties class is also used as part of obtaining
// a connection to the database.
import java.sql.*;
import java.util.Properties;

// Create a public class to encapsulate the program.
public class jtds2 {

    // The connection is a private variable of the object.
    private Connection connection = null;

    // Any class that is to be an 'entry point' for running
    // a program must have a main method. The main method
    // is where processing begins when the program is called.
    public static void main(java.lang.String[] args) {

        // Create an object of type BasicJDBC. This
        // is fundamental to object-oriented programming. Once
        // an object is created, call various methods on
        // that object to accomplish work.
        // In this case, calling the constructor for the object
        // creates a database connection that the other
        // methods use to do work against the database.
        try {
        jtds2 test = new jtds2();

        // Call the rebuildTable method. This method ensures that
        // the table used in this program exists and looks the
        // way it should. The return value is a boolean for
        // whether or not rebuilding the table completed
        // successfully. If it did no, display a message
        // and exit the program.
        if (!test.rebuildTable()) {
            System.out.println("Failure occurred while setting up " +
                               " for running the test.");
            System.out.println("Test will not continue.");
            System.exit(0);
        }

        // The run query method is called next. This method
        // processes an SQL select statement against the table that
        // was created in the rebuildTable method. The output of
        // that query is output to standard out for you to view.
        test.runQuery();

        // Finally, the cleanup method is called. This method
        // ensures that the database connection that the object has
        // been hanging on to is closed.
        test.cleanup();
        } catch (ExceptionInInitializerError e) {
            // If any of our SQL statements failed (other than the drop
of the table
            // that was handled in the inner try/catch block), the error
message is
            // displayed and false is returned to the caller, indicating
that the table
            // may not be complete.
            System.out.println("Error in jtds2.main: " +
e.getException());
            // System.out.println(e.getCause());
            e.printStackTrace();
            System.exit(0);
        }
    }


    /**
    This is the constructor for the basic JDBC test. It creates a
database
    connection that is stored in an instance variable to be used in
later
    method calls.
    **/
    public jtds2() {

        // One way to create a database connection is to pass a URL
        // and a java Properties object to the DriverManager. The
following
        // code constructs a Properties object that has your user ID and
        // password. These pieces of information are used for connecting
        // to the database.
        Properties properties = new Properties ();
        properties.put("user", "sa");
        properties.put("password", "sa");
        properties.put("TDS", "4.2");  
        /* property TDS required to connect to Sql Server 6.5 */

        // Use a try/catch block to catch all exceptions that can come
out of the
        // following code.
        try {
            // The DriverManager must be aware that there is a JDBC
driver available
            // to handle a user connection request. The following line
causes the
            // native JDBC driver to be loaded and registered with the
DriverManager.
            Class.forName("net.sourceforge.jtds.jdbc.Driver");

            // Create the database Connection object that this program
uses in all
            // the other method calls that are made. The following code
specifies
            // that a connection is to be established to the local
database and that
            // that connection should conform to the properties that
were set up
            // previously (that is, it should use the user ID and
password specified).
            String url =
"jdbc:jtds:sqlserver://DVJRMC:1433/DynamicVision";
            connection = DriverManager.getConnection(url, properties);

        } catch (Exception e) {
            // If any of the lines in the try/catch block fail, control
transfers to
            // the following line of code. A robust application tries to
handle the
            // problem or provide more details to you. In this program,
the error
            // message from the exception is displayed and the
application allows
            // the program to return.
            System.out.println("Caught exception: " + e.getMessage());
        }
    }


    /**
    Ensures that the qgpl.basicjdbc table looks you want it to at the
start of
    the test.

    @returns boolean    Returns true if the table was rebuild
successfully;
                        returns false if any failure occurred.
    **/
    public boolean rebuildTable() {
        // Wrap all the functionality in a try/catch block so an attempt
is
        // made to handle any errors that may happen within this method.
        try {

            // Statement objects are used to process SQL statements
against the
            // database. The Connection object is used to create a
Statement
            // object.
            Statement s = connection.createStatement();

            try {
                // Build the test table from scratch. Process an update
statement
                // that attempts to delete the table if it currently
exists.
                s.executeUpdate("drop table basicjdbc");
            } catch (SQLException e) {
                // Do not perform anything if an exception occurred.
Assume
                // that the problem is that the table that was dropped
does not
                // exist and that it can be created next.
            }

            // Use the statement object to create our table.
            s.executeUpdate("create table basicjdbc(id int, name
char(15))");

            // Use the statement object to populate our table with some
data.
            s.executeUpdate("insert into basicjdbc values(1, 'Frank
Johnson')");
            s.executeUpdate("insert into basicjdbc values(2, 'Neil
Schwartz')");
            s.executeUpdate("insert into basicjdbc values(3, 'Ben
Rodman')");
            s.executeUpdate("insert into basicjdbc values(4, 'Dan
Gloore')");

            // Close the SQL statement to tell the database that it is
no longer
            // needed.
            s.close();

            // If the entire method processed successfully, return true.
At this point,
            // the table has been created or refreshed correctly.
            return true;

        } catch (SQLException sqle) {
            // If any of our SQL statements failed (other than the drop
of the table
            // that was handled in the inner try/catch block), the error
message is
            // displayed and false is returned to the caller, indicating
that the table
            // may not be complete.
            System.out.println("Error in rebuildTable: " +
sqle.getMessage());
            return false;
        }
    }



    /**
    Runs a query against the demonstration table and the results are
displayed to
    standard out.
    **/
    public void runQuery() {
        // Wrap all the functionality in a try/catch block so an
attempts is
        // made to handle any errors that might happen within this
        // method.
        try {
            // Create a Statement object.
            Statement s = connection.createStatement();

            // Use the statement object to run an SQL query. Queries
return
            // ResultSet objects that are used to look at the data the
query
            // provides.
            ResultSet rs = s.executeQuery("select * from basicjdbc");

            // Display the top of our 'table' and initialize the counter
for the
            // number of rows returned.
            System.out.println("--------------------");
            int i = 0;

            // The ResultSet next method is used to process the rows of
a
            // ResultSet. The next method must be called once before the
            // first data is available for viewing. As long as next
returns
            // true, there is another row of data that can be used.
            while (rs.next()) {

                // Obtain both columns in the table for each row and
write a row to
                // our on-screen table with the data. Then, increment
the count
                // of rows that have been processed.
                System.out.println("| " + rs.getInt(1) + " | " +
rs.getString(2) + "|");
                i++;
            }

            // Place a border at the bottom on the table and display the
number of rows
            // as output.
            System.out.println("--------------------");
            System.out.println("There were " + i + " rows returned.");
            System.out.println("Output is complete.");

        } catch (SQLException e) {
            // Display more information about any SQL exceptions that
are
            // generated as output.
            System.out.println("SQLException exception: ");
            System.out.println("Message:....." + e.getMessage());
            System.out.println("SQLState:...." + e.getSQLState());
            System.out.println("Vendor Code:." + e.getErrorCode());
            e.printStackTrace();
        }
    }


    /**
    The following method ensures that any JDBC resources that are still
    allocated are freed.
    **/
    public void cleanup() {
        try {
            if (connection != null)
                connection.close();
        } catch (Exception e) {
            System.out.println("Caught exception: ");
            e.printStackTrace();
        }
    }
}

Glenn Howald
Programming Manager
Jefferson Regional Medical Center
1600 W 40th Ave
Pine Bluff, AR 71603
870-541-4064