Re: Using jtds to access MS SQLServer databases

Hi Glenn

I've used jtds. Below is some source:

String url = "jdbc:jtds:sqlserver://" + sqlServer + ":" + port + "/";

Connection connectionSqlServer = null;

try {

     Class.forName("net.sourceforge.jtds.jdbc.Driver");

   }

   catch (UnsatisfiedLinkError e) {

     System.out.println("JDBC connection to " + sqlServer + " failed.");

     e.printStackTrace();

     System.exit(1);

   }

Thanx, PLA

Howald, Glenn wrote:

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