SUMMARY
This article describes how to connect to SQL Server 2000 by using the Microsoft SQL Server 2000 driver for JDBC.
NOTE: For installation instructions for Microsoft SQL Server 2000 Driver for JDBC, see the
Microsoft SQL Server 2000 Driver for JDBC Installation Guide.
After you install the Microsoft SQL Server 2000 driver for JDBC, you can connect from your program to your database in two ways: with a connection URL, or with a JNDI data source. This article describes how to configure and test your database connection by using a connection URL.
One way of connecting to a database is by through JDBC Driver Manager by using the
getConnection method of the
DriverManager class. The simplest manner of using this method takes a string parameter that contains an URL, a user name, and a password. The following sections in this article describe how to load the Microsoft SQL Server 2000 driver for JDBC from your JDBC program.
back to the top
To Set the CLASSPATH Variable
The Microsoft SQL Server 2000 driver for JDBC .jar files must be listed in your CLASSPATH variable. The CLASSPATH variable is the search string that Java Virtual Machine (JVM) uses to locate the JDBC drivers on your computer. If the drivers are not listed in your CLASSPATH variable, you receive the following error message when you try to load the driver:
java.lang.ClassNotFoundException: com/microsoft/jdbc/sqlserver/SQLServerDriver
Set your system CLASSPATH variable to include the following entries:
- \Your installation path\Lib\Msbase.jar
- \Your installation path\Lib\Msutil.jar
- \Your installation path\Lib\Mssqlserver.jar
This is an example of a configured CLASSPATH variable:
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\msutil.jar;c:\program files\Microsoft SQL Server 2000 Driver for JDBC\lib\mssqlserver.jar
To Register the Driver
Registering the driver instructs JDBC Driver Manager which driver to load. When you load a driver by using the
class.forName function, you must specify the name of the driver. This is the driver name for Microsoft SQL Server 2000 Driver for JDBC:
com.microsoft.jdbc.sqlserver.SQLServerDriver
The following sample code demonstrates how to register the driver:
Driver d = (Driver)Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
back to the top
To Pass the Connection URL
You must pass your database connection information in the form of a connection URL. This is a template URL for Microsoft SQL Server 2000 Driver for JDBC. Substitute the values for your database:
jdbc:microsoft:sqlserver://servername:1433
The following sample code demonstrates how to specify a connection URL:
con = DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433", "userName", "password");
The server name values can be an IP address or a host name (assuming that your network resolves host names to IP addresses). You can test this by pinging the host name and verifying that you receive a reply with the correct IP address.
The numeric value after the server name is the port number on which the database is listening. The values that are listed earlier in this article are sample default values. Make sure to substitute the port number that your database is using.
For a complete list of connection URL parameters, see the Microsoft SQL Server 2000 Driver for JDBC HTML Help, or see the
Online Guide. See the "Connection String Properties" section.
back to the top
Sample Code to Test the Connection
The following sample code tries to connect to the database and displays the database name, the version, and the available catalogs. Replace the server properties with the values for your server:
import java.*;
public class Connect{
private java.sql.Connection con = null;
private final String url = "jdbc:microsoft:sqlserver://";
private final String serverName= "localhost";
private final String portNumber = "1433";
private final String databaseName= "pubs";
private final String userName = "user";
private final String password = "password";
// Informs the driver to use server a side-cursor,
// which permits more than one active statement
// on a connection.
private final String selectMethod = "cursor";
// Constructor
public Connect(){}
private String getConnectionUrl(){
return url+serverName+":"+portNumber+";databaseName="+databaseName+";selectMethod="+selectMethod+";";
}
private java.sql.Connection getConnection(){
try{
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
con = java.sql.DriverManager.getConnection(getConnectionUrl(),userName,password);
if(con!=null) System.out.println("Connection Successful!");
}catch(Exception e){
e.printStackTrace();
System.out.println("Error Trace in getConnection() : " + e.getMessage());
}
return con;
}
/*
Display the driver properties, database details
*/
public void displayDbProperties(){
java.sql.DatabaseMetaData dm = null;
java.sql.ResultSet rs = null;
try{
con= this.getConnection();
if(con!=null){
dm = con.getMetaData();
System.out.println("Driver Information");
System.out.println("\tDriver Name: "+ dm.getDriverName());
System.out.println("\tDriver Version: "+ dm.getDriverVersion ());
System.out.println("\nDatabase Information ");
System.out.println("\tDatabase Name: "+ dm.getDatabaseProductName());
System.out.println("\tDatabase Version: "+ dm.getDatabaseProductVersion());
System.out.println("Avalilable Catalogs ");
rs = dm.getCatalogs();
while(rs.next()){
System.out.println("\tcatalog: "+ rs.getString(1));
}
rs.close();
rs = null;
closeConnection();
}else System.out.println("Error: No active Connection");
}catch(Exception e){
e.printStackTrace();
}
dm=null;
}
private void closeConnection(){
try{
if(con!=null)
con.close();
con=null;
}catch(Exception e){
e.printStackTrace();
}
}
public static void main(String[] args) throws Exception
{
Connect myDbTest = new Connect();
myDbTest.displayDbProperties();
}
}
If this code is successful, the output is similar to the following:
Connection Successful!
Driver Information
Driver Name: SQLServer
Driver Version: 2.2.0022
Database Information
Database Name: Microsoft SQL Server
Database Version: Microsoft SQL Server 2000 - 8.00.384 (Intel X86)
May 23 2001 00:02:52
Copyright (c) 1988-2000 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: )
Avalilable Catalogs
catalog: master
catalog: msdb
catalog: pubs
catalog: tempdb
Basic Connectivity Troubleshooting
These are common error messages that may occur when you try to connect to your SQL server:
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Login failed for user 'user'. Reason: Not associated with a trusted SQL Server connection.
This error message occurs if the SQL Server 2000 authentication mode is set to Windows Authentication mode. The Microsoft SQL Server 2000 driver for JDBC does not support connecting by using Windows NT authentication. You must set the authentication mode of your SQL Server to Mixed mode, which permits both Windows Authentication and SQL Server Authentication.
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]This version of the JDBC driver only supports Microsoft SQL Server 2000. You can either upgrade to SQL Server 2000 or possibly locate another version of the driver.
This error message occurs you try to connect to a SQL Server version earlier than SQL Server 2000. The Microsoft SQL Server 2000 driver for JDBC supports connectivity only with SQL Server 2000.