How to get started with Microsoft JDBC

Note:: For installation instructions on the Microsoft SQL Server 2000 JDBC driver, see Microsoft SQL Server 2000 Driver for JDBC Installation Guide.

After the Microsoft SQL Server 2000 JDBC driver is installed, you can connect to the database from your program in two ways: Use the connection URL or use the JNDI data source. This article describes how to configure and test the database connection by using the connection URL.

One way to connect to the database is to use the JDBC driver ManagerDriverManagerClassGetConnectionMethod. The simplest way to use this method is to use a string parameter that contains the URL, user name, and password. The following sections describe how to load the Microsoft SQL Server 2000 JDBC driver from a JDBC program.

Go back to the top and set the CLASSPATH variable

The Microsoft SQL Server 2000 JDBC driver. jar file must be listed in the CLASSPATH variable. The CLASSPATH variable is a search string used by the Java Virtual Machine (JVM) to find the JDBC driver on your computer. If the driver is not listed in the CLASSPATH variable, the following error message appears when you attempt to load the driver:

Java. lang. ClassNotFoundException: com/microsoft/jdbc/sqlserver/SQLServerDriver

Set the system CLASSPATH variable and add the following items:

• \Your installation path\ Lib \ Msbase. jar
• \Your installation path\ Lib \ Msutil. jar
• \Your installation path\ Lib \ Mssqlserver. jar

The following 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

The purpose of registering a driver is to notify the JDBC driver manager of the driver to be loaded. When usingClass. forNameWhen a function loads a driver, you must specify the name of the driver. The following is the name of the Microsoft SQL Server 2000 JDBC driver:

Com. microsoft. jdbc. sqlserver. SQLServerDriver

The following code example demonstrates how to register a driver:

Driver d = (Driver)Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();

Return to the top and pass the connection URL

The database connection information must be transmitted in the form of a connection URL. The following is the template URL of the Microsoft SQL Server 2000 JDBC driver. Replace the following values with your database values:

Jdbc: microsoft: sqlserver: // servername: 1433

The following code example demonstrates how to specify a connection URL:

con = DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433", "userName", "password");

The server name value can be an IP address or host name (assuming that your network can resolve the host name to an IP address ). You can run the PING command on the host name to test whether the response is received and whether the IP address of the response is correct.

The numeric value after the server name is the port number of the Database Listener. The values listed above are the default values of the example. Make sure that you replace this value with the port number used by your database.

For a complete list of URL parameters, see the Microsoft SQL Server 2000 JDBC driver HTML Help, or seeOnline Guide. See "connection string attributes.

Code example for testing connection back to the top

The following code example tries to connect to the database and displays the database name, version, and available catalog. Replace the server attributes in the Code with your server values:

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 the code runs successfully, the output result should be similar to the following:

Connection Successful!Driver InformationDriver Name:SQLServerDriver Version: 2.2.0022Database InformationDatabase Name:Microsoft SQL ServerDatabase Version:Microsoft SQL Server  2000 - 8.00.384 (Intel X86)May 23 2001 00:02:52Copyright (c) 1988-2000 Microsoft CorporationDesktop Engine on Windows NT 5.1 (Build 2600: )Avalilable Catalogscatalog:mastercatalog:msdbcatalog:pubscatalog:tempdb

Back to the top of the page for troubleshooting the connection

The following is an error message that is frequently seen when you try to connect to the 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. if you set the Authentication Mode of SQL Server 2000 to Windows Authentication mode, this error message is displayed. Microsoft SQL Server 2000 JDBC drivers do not support connection using Windows NT authentication. You must set the SQL Server Authentication Mode to "mixed mode", which allows 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 is returned when you try to connect to SQL Server versions earlier than SQL Server 2000. Microsoft SQL Server 2000 JDBC driver only supports connection with SQL Server 2000. Back to Top

The information in this article applies:

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

Back to Top

Keywords:

Kbhowtomaster KB313100

Back to the top of the page, Microsoft and/or its suppliers will not make any declaration of the applicability of the files and information contained in the images published on the server for any purpose. All such documents and related figures are provided "in accordance with the sample" without warranty of any nature. Microsoft and/or its suppliers hereby declare that they shall not be liable for all warranties and conditions relating to such information, such warranties and conditions include all implied warranties and conditions regarding merchantability, conformity with specific purposes, ownership and non-infringement. In all circumstances, in any lawsuit arising from or relating to the use or operation of information on the server, microsoft and/or its suppliers, in view of any special, indirect, consequential damage caused by the loss of use, data or profit, or any damage caused by the loss of use, data or profits are not liable for any consequences.