Enavigo :: SQL Server 2000 Page :: Java and Microsoft SQL Server 2000

Creating a Tomcat DataSource using SQL Server 2000

This was something that I needed to do for a project at work and was definitely unpleasant. To set up a development environment for Tomcat with SQL Server 2000 as the database of choice and I also wanted to use a DataSource object. It improves performance and is easy to obtain using JNDI, but the set up and development process are less than straightforward. Doing so with Microsoft's SQL Server 2000 is even less pleasant than normal due to the company's disengagement from Java. That means support is sketchy and although the documentation to Microsoft's own JDBC drivers is not bad, they are not too helpful when getting going. Eventually I decided to skip their JDBC drivers altogether in favor for an open source alternative. This is an outline of my findings in 36 hours or so of exploration.

I would not have been able to provide this page if it were not for the kind guidance and wisdom of John Norman. Thanks John!

My setup included the following components:
The aim is that you work on your code, compile and deploy on Tomcat and get Tomcat to refresh the application code so that you can test your new build.

Java

First set up Java, which is easy -- simply download and run the installer. Add Java's bin directory, inside its installation directory, to your system's Path (Control Panel -> System -> Advanced Tab -> Enivronment Variables) and test it by opening a console window by simply running 'java'. You should see version information coming up and if not, the path is incorrect. Also, add a new entry to the environment - JAVA_HOME - that will help Tomcat and Ant find where you installed your Java SDK.

Ant

Unzip Ant to a location on your system. Then add Ant's bin directory to the system's path. Finally, create an environment variable ANT_HOME that will point to Ant's directory.
Update: July 12, 2004
Some users may see an error when calling Ant saying that there is another version of Ant on the system which is incompatible (normally saying that version 1.6.1 is required while 1.6.0 is installed). This happens because there is either another instance of Ant in the CLASSPATH (not the system PATH). Remove that CLASSPATH entry and open a new console window and the problem will be gone.

Tomcat

If you are using the Tomcat installer, Tomcat will do most of the installation work for you. The only thing that is left for you is to deceide where to install Tomcat. Once the installation is done, add an environment variable CATALINA_HOME which should point at the directory where Tomcat was installed. I am using this value below to as reference to Tomcat's installation directory.
The installer should have offered you the ability to set an administrator user. If you did not, edit the file $CATALINA_HOME/conf/tomcat-users.xml and add a user like this:
You can now also test whether Tomcat is properly installed, normally at http://localhost:8080,

jTDS JDBC Driver

Once you installed Tomcat, you need to add the JDBC driver to its $CATALINA_HOME/common/lib folder. Just copy the jTDS jar file to that location and you should be good to go. You can also add the driver jar file to the CLASSPATH (which will help you in obtaining a database Connection object the usual way - using the DriverManager object.

Ant Contrib Tasks

The Ant Contrib Tasks spare you the task of writing specialized tasks for Ant that do things that are not normally possible with it out of the box. Once you unzip the file you downloaded, copy the ant-contrib jar file to $ANT_HOME/lib directory. That will teach Ant a bunch of new tricks.

Ant Catalina Tasks

So that Ant can also deploy and undeploy applications to Tomcat, you must copy another Ant task jar file. Inside $CATALINA_HOME/server/lib you will find a file called catalina-ant.jar (version name may be appended). Copy that file to Ant's lib directory.

Deployment

Assuming you wrote some servlets, JSPs and other class files and want to start deploying, you will need to set up Ant to compile, create a WAR file and finally deploy the WAR file to Tomcat.
To accomplish that, Ant uses two files - build.xml and build.properties. The first contains the Ant tasks that tell Ant what to do when building the application, and the second tells Ant the values of variables that are used in build.xml. This way you can change values easily in build.properties without violating build.xml. Sample versions appear below:
It is important that you go over build.properties and set the values in it to reflect your system. Among those values are the location of servlet.jar, the URL for the Tomcat server and its administrative passwords.
I am using the following directory structure - which is reflected in build.properties:
  • config - where I put web.xml and context.xml which are discussed below.
  • src
    • java - includes the source files
  • web - where I put the JSP files and static web content
  • classes - the destination folder for compiled files
  • dist - the destination for the output of the build process, the jar and war files for the application

build.xml has several important tasks, among them compile, deploy and undeploy. Examine them to make sure that all the values are properly set. If not, Ant will report the failures to you.

Although you can simply copy the result war file once you created it to Tomcat's webapps directory, it may not always pick up on that fact. A much better way is to use Tomcat's Manager application (available at http://localhost:8080/manager/html which allows you to upload, reload and remove applications both manually and like we would, using Ant.
One thing that is somewhat unusual is that using Ant's deploy task will overwrite Tomcat's server.xml. It does back the file up when it overwrites it, but you should be aware that it will overwrite possibly important settings (like futile attempts to set a Context or other JNDI settings). The alternative it offers is much nicer: you can create the same Context block like the one you would have put in server.xml in its own special file - context.xml (this became a feature of Tomcat 5). That way your application is better coupled with its own context information.
As opposed to web.xml which is to be placed inside the WEB-INF directory of the war file, context.xml has to be placed inside the META-INF directory of the war file. If the file will not be copied into that folder, the DataSource will not work.
So what to put in context.xml and web.xml? Look at the examples below.

This should be it. I hope. For more information, look at the information below:
Update: July 4, 2004
Somehow, apparently due to the date and a special star alignment, Microsoft's drivers decided to cooperate and work. Example files below; make sure to rename them context.xml and web.xml respectively.
context.xml
web.xml

For comments and horror stories, feel free to e-mail me at [yuval at enavigo.com]. Last Update: July 21, 2004