Microsoft KB Archive/313100

= HOW TO: Get Started with Microsoft JDBC =

Article ID: 313100

Article Last Modified on 1/9/2004

-

APPLIES TO


 * Microsoft SQL Server 2000 Driver for JDBC
 * Microsoft SQL Server 2000 64-bit Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q313100



IN THIS TASK

 * SUMMARY
 * To Set the CLASSPATH Variable
 * To Register the Driver
 * To Pass the Connection URL
 * Sample Code to Test the Connection
 * Basic Connectivity Troubleshooting



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:
 * \ \Lib\Msbase.jar
 * \ \Lib\Msutil.jar
 * \ \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

back to the top

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(&quot;com.microsoft.jdbc.sqlserver.SQLServerDriver&quot;).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(&quot;jdbc:microsoft:sqlserver://localhost:1433&quot;, &quot;userName&quot;, &quot;password&quot;); 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 &quot;Connection String Properties&quot; 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 = &quot;jdbc:microsoft:sqlserver://&quot;; private final String serverName= &quot;localhost&quot;; private final String portNumber = &quot;1433&quot;; private final String databaseName= &quot;pubs&quot;; private final String userName = &quot;user&quot;; private final String password = &quot;password&quot;; // Informs the driver to use server a side-cursor, // which permits more than one active statement // on a connection. private final String selectMethod = &quot;cursor&quot;; // Constructor public Connect{} private String getConnectionUrl{ return url+serverName+&quot;:&quot;+portNumber+&quot;;databaseName=&quot;+databaseName+&quot;;selectMethod=&quot;+selectMethod+&quot;;&quot;; }    private java.sql.Connection getConnection{ try{ Class.forName(&quot;com.microsoft.jdbc.sqlserver.SQLServerDriver&quot;); con = java.sql.DriverManager.getConnection(getConnectionUrl,userName,password); if(con!=null) System.out.println(&quot;Connection Successful!&quot;); }catch(Exception e){ e.printStackTrace; System.out.println(&quot;Error Trace in getConnection : &quot; + 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(&quot;Driver Information&quot;); System.out.println(&quot;\tDriver Name: &quot;+ dm.getDriverName); System.out.println(&quot;\tDriver Version: &quot;+ dm.getDriverVersion ); System.out.println(&quot;\nDatabase Information &quot;); System.out.println(&quot;\tDatabase Name: &quot;+ dm.getDatabaseProductName); System.out.println(&quot;\tDatabase Version: &quot;+ dm.getDatabaseProductVersion); System.out.println(&quot;Avalilable Catalogs &quot;); rs = dm.getCatalogs; while(rs.next){ System.out.println(&quot;\tcatalog: &quot;+ rs.getString(1)); }                    rs.close; rs = null; closeConnection; }else System.out.println(&quot;Error: No active Connection&quot;); }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

back to the top

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.

back to the top

Keywords: kbhowtomaster KB313100

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.