SUMMARY This article describes how to connect to SQL Server 2000 using Microsoft SQL Server 2000 JDBC drivers.
Note: For installation instructions for the Microsoft SQL Server 2000 JDBC driver, see the Microsoft SQL Server 2000 Driver for JDBC Installation Guide.
Once you have installed the Microsoft SQL Server 2000 JDBC driver, you can connect to your 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 database connections using the connection URL.
One way to connect to the database is the GetConnection method for the DriverManager class through the JDBC driver manager. When using this method, the easiest way is to use a string parameter containing the URL, the username, and the password. The following sections in this article describes how to load the Microsoft SQL Server 2000 JDBC driver from the JDBC program.
Back to top Set ClassPath Variables Microsoft SQL Server 2000 JDBC Driver. JAR file must be listed in the ClassPath variable. The ClassPath variable is a Java Virtual Machine (JVM) for searching for search strings for the JDBC driver on your computer. If the driver does not listed in the ClassPath variable, the following error message will appear when you try to load the driver: java.lang.classNotFoundException: COM / Microsoft / JDBC / SQLSERVER / SQLSERVERDRIVER Setting the system classpath variable, add the following: • / 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 configuration 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 top Registration Driver Registration driver is to inform the JDBC driver manager which driver loaded. When loading the driver using the Class.Forname function, you must specify the name of the driver. The following is the name of the Microsoft SQL Server 2000 JDBC driver: com.microsoft.jdbc.sqlser.sqlserverdriver The following code example demonstrates how to register the driver: Driver D = (Driver) Class.Forname ("com.microsoft.jdbc.sqlserver. SQLServerDriver "). NewInstance (); Back to top Pass connection URL must pass the database connection information in the form of a connection URL. The following is the template URL of the Microsoft SQL Server 2000 JDBC driver. Use the value of your database to replace the following: JDBC: Microsoft: SQLServer: // ServerName: 1433 The following code example demonstrates how to specify the connection URL: Con = DriverManager.getConnection ("JDBC: Microsoft: SQLServer: // localhost: 1433" "UserName", "password"); 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 perform a test by performing a ping command for the host name, verify that the response can be received, and whether the IP address of the response is correct. The digital value behind the server name is the port number of the database. The values listed above in this article are example default values. Be sure to replace this value with the port number used in your database.
For a complete list of connecting the URL parameters, see the Microsoft SQL Server 2000 JDBC driver HTML help, or see the online guide. See "Connecting String Properties" section.
Back to top Code examples Used to Test Connections Example The following code example attempts to connect to the database and displays the database name, version, and available catalog. Please replace the server properties in 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 = 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:" rgetstring 1));}}}}} RS = null; closeconnection ();} else system.out.println ("error: no activity");} 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 {connection {connection {connection {connection = New connection (); mydbtest.displaydbproperties ();}}
If this code runs successfully, its output 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:)
Avalilarog: MSDBCATALOG: MSDBCATALOG: PUBSCATALOG: Pubscatalog: Tempdb Back to top Basic information about exclude connection faults Below below is an error message that attempts to connect to SQL servers: java.sql.sqlexception: [Microsoft] [SQLServer 2000 Driver for JDBC] [ SQL Server] Login Faled for User 'User'.reason: NOT Associated with a trusted SQL Server Connection. This error message will occur if you set the SQL Server 2000 authentication mode to "Windows Authentication Mode". The Microsoft SQL Server 2000 JDBC driver does not support connectivity using Windows NT validation. You must set the SQL Server's 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 when you try to connect This error message will appear when the SQL Server version of SQL Server 2000 is available. The Microsoft SQL Server 2000 JDBC driver only supports connecting to SQL Server 2000.
