Background
The Microsoft SQL Server 2000 Driver for JDBC is licensed from Data Direct (http://www.datadirect.com). It is a Type-4 JDBC driver and is freely available for download. This driver is not as feature-rich as Data Direct’s most recent commercial JDBC driver.
The JDBC driver can be installed on Windows and Unix clients, making SQL Server available to clients running Linux, HP/UX, AIX, etc.
This driver is only licensed for use with SQL Server 2000. Pre-RTM versions of the driver could connect to previous versions of SQL Server. The RTM and subsequent releases check the TDS version upon connection and will fail if the version is from older or newer versions of SQL Server.
The Microsoft SQL Server 2005 JDBC Driver is internally developed by Microsoft. It is a Type-4 JDBC driver and is freely available for download.
The supported platforms for the 2005 driver are: HP-UX, IBM AIX, Linux, Solaris, Windows 2000, Windows Server 2003, or Windows XP
This 2005 driver is licensed for use with SQL Server 2005 and SQL Server 2000
Driver types
Type 1 – Commonly known as the JDBC-ODBC bridge, Type 1 drivers translate JDBC calls to ODBC calls to perform data access. It requires an ODBC driver to exist on the client to function. For the most part this limits the availability to Windows based operating systems.
Type 2 – Also known as a native-API driver, Type 2 drivers contain Java code that calls native C/C++ methods provided by the database vendor to perform data access. This requires client-side database libraries to function. They can be used on multiple platforms as long as the native library exists for that particular platform.
Type 3 – Known as a network-protocol driver, Type 3 drivers are written entirely in Java, but they call into a middleware API (such as Oracle’s OCI layer) that performs protocol conversions to communicate with the database. The client piece can reside on any platform that has a Java Virtual Machine but the milddleware piece can only be used on platforms for which it has been ported.
Type 4 – Also known as a native-protocol driver, Type 4 drivers are written entirely in Java and talk directly to the database using the native database protocol (such as SQL Server’s TDS). It can be used on any platform that has a Java Virtual Machine.
File Extensions
.JAVA files – Java source code files use the file extension .java. This is basically a text file, and you can use your preferred development environment to write and edit the code. The filename is case-sensitive, and it must match the name and case of the class that is defined in the code.
.CLASS files – When you compile a .java source file, the output that is produced is a .class file. This file contains the bytecode instructions that are interpreted and executed by the Java interpreter. This is similar to an .exe file in Win32 terminology.
.JAR files – The .class files that comprise the Microsoft JDBC driver are packaged into a compressed format called .jar files. The file extension .jar stands for Java ARchive, and it is similar to a .zip or .cab file. To view the contents, you can extract the contents of a .jar file using jar.exe (from your JDK) or the WinRAR shareware program. The files do not need to be extracted to be interpreted by a Java application.
Installation and Configuration
Java Development Kit (SDK) vs. Java Runtime Environment (JRE).
The J2SE Software Development Kit (SDK) contains the tools necessary to develop and compile Java applications and also execute the compiled programs. It contains the JRE as part of its installation, but it requires more disk space than the JRE.
The JRE is the minimum requirement for running Java applications on a machine. It does not include the tools necessary to compile applications, but it is typically has a smaller footprint than the full SDK.
You can install the SDK from the following website:
http://java.sun.com/j2se/1.4.2/download.html
JDBC Driver Setup
The JDBC driver can be downloaded from http://www.microsoft.com/downloads (keyword JDBC). The build numbers of the available drivers are listed below:
· SQL Server 2000 Driver for JDBC RTM – build 2.2.0022
· SQL Server 2000 Driver for JDBC SP1 – build 2.2.0029
· SQL Server 2000 Driver for JDBC SP2 – build 2.2.0037
· SQL Server 2000 Driver for JDBC SP3 – build 2.2.0040
· SQL Server 2005 RTM – build 1.0.809.102
2000 JDBC driver service packs are only intended to roll up bug fixes. No new functionality is projected for future service packs. The 2005 JDBC driver will have regular releases.
The setup.exe file is based on the standard Windows Installer technology. Perform a complete installation to install all JDBC components. The online documentation comes in both HTML and Adobe PDF formats. This is the primary source of information for JDBC syntax and functionality.
Although the default installation will install the driver into C:\Program Files\Microsoft SQL Server 2000 Driver for JDBC, it will be easier to maintain your environment variables and batch files if you install it into a directory with a shorter name. I recommend using C:\MSJDBC\MSJDBC for the latest version and C:\msjdbc\MSJDBC_22_033 etc for older builds. This will allow you to use different versions of the driver concurrently
System Environment Variables
· 2000 CLASSPATH – The CLASSPATH must be set to point to each of the three .jar files that comprise the Microsoft JDBC driver (msbase.jar, mssqlserver.jar, and msutil.jar). By default, these are located in the C:\Program Files\Microsoft SQL Server 2000 Driver for JDBC\lib directory. The full path and filename must be used for each file in the CLASSPATH. Each path must be separated by a semicolon (;).
CLASSPATH=.;C:\Program Files\Microsoft SQL Server 2000 Driver for JDBC\lib\msbase.jar;C:\Program Files\Microsoft SQL Server 2000 Driver for JDBC\lib\mssqlserver.jar;C:\Program Files\Microsoft SQL Server 2000 Driver for JDBC\lib\msutil.jar;
Or
CLASSPATH=.;C:\MSJDBC\MSJDBC\lib\msbase.jar;C:\MSJDBC\MSJDBC\lib\mssqlserver.jar;C:\MSJDBC\MSJDBC\lib\msutil.jar
2005 CLASSPATH – The CLASSPATH must be set to point to the .jar file that comprises the Microsoft 2005 JDBC driver (sqljdbc.jar). The full path and filename must be used for each file in the CLASSPATH. Each path must be separated by a semicolon (;).
CLASSPATH=.;C:\JDBC\sqljdbc_1.0\enu\sqljdbc.jar;
Note: The path and file names are case-sensitive.
Note: It is a good idea to include the current directory (.) as part of the CLASSPATH definition.
Note: If there are spaces in the class path it will not work.
You might want to maintain a set of batch files with different CLASSPATH statements for different driver versions. E.g:
SP3.bat:
Set CLASSPATH=.;C:\MSJDBC\MSJDBC\lib\msbase.jar;C:\MSJDBC\MSJDBC\lib\mssqlserver.jar;C:\MSJDBC\MSJDBC\lib\msutil.jar
SP1_033.bat:
Set CLASSPATH=.;C:\MSJDBC\MSJDBC_22_033\lib\msbase.jar;C:\MSJDBC\MSJDBC_22_033\lib\mssqlserver.jar;C:\MSJDBC\MSJDBC_22_033\lib\msutil.jar
· While you can put both the 2000 and 2005 drivers in the same CLASSPATH, you cannot put multiple version of the 2005 driver in the same CLASSPATH.
· PATH – To facilitate the compilation and execution of JDBC programs, you may want to modify your PATH environment variable to include the location of javac.exe. For example, the path to javac.exe for the Sun 1.4 JDK might be C:\j2sdk142_08\bin. Adding this to your path will facilitate compilation of Java code from the command line.
Distributed Transactions (SQL Server JTA)
The Microsoft JDBC driver supports distributed transactions via SQL Server extended stored procedures. These are implemented in sqljdbc.dll, which is found in the C:\Program Files\Microsoft SQL Server 2000 Driver for JDBC\SQLServer JTA directory.
Beginning with JDBC SP2, the JDBC driver also runs on 64-bit Windows and can connect to the 64-bit edition of SQL Server 2000. The 64-bit version of sqljdbc.dll is located in the C:\Program Files\Microsoft SQL Server 2000 Driver for JDBC\SQLServer JTA 64-bit directory.
To install JTA support, do the following:
1. Copy the appropriate sqljdbc.dll to your SQL Server’s binn directory (for example, C:\Program Files\Microsoft SQL Server\<instance_name>\binn).
From Query Analyzer, run the instjdbc.sql script to install the extended stored Procedures.
