Microsoft SQL Server 2005 JDBC Driver (oracle GRID)

  • Published on Sep 12 2007
  • # 2,625
  • mssql

Microsoft SQL Server 2005 JDBC Driver java test program.

Enterprise Manager Grid Control System Monitoring Plug-in for Microsoft SQL Server

I have been busy deploying Microsoft SQL Server in our Oracle Enterpise GRID Control using the ‘Oracle Enterprise Manager Grid Control
System Monitoring Plug-in for Microsoft SQL Server’ http://www.oracle.com/technology/products/oem/extensions/plugin-ms_sql.html.

The final step in the configuration is the configuration of your SQL Server target in the GRID Control ‘configure target’ page for that SQL Server. One of the fields required is ‘JDBC URL’ (and ‘JDBC Driver’).

The Microsoft SQL Server 2005 JDBC Driver comes with two sample programs in sqljdbc_1.2\enu\help\samples\connections (connectDS.java and connectURL.java). Since one of the prerequisities I read on metalink is ‘make sure remote JDBC connections with your SQL Server work’ I left the Oracle GRID console and tried to get this sample program to connect to my SQL Server succesfully.

It took my quit some time to get the right connection values (my SQL Server listened on TCP port 1204 instead of 1433), and because I got a little tired of having to compile the provided java test pogram every time I wanted to try new connection details I decided to rewrite this program into a program that ‘asks’ for the connection values. This way I can provide the entries connection details at run time (instead of at compile time).

jdbcMSSql.java
import java.io.Console;
import java.sql.*;
 


public class jdbcMSSql {
 
  public static void main(String[] args) {
 
    // prepare console 
    Console c = System.console();
    if (c == null) {
              System.err.println("No console.");
              System.exit(1);
    }

    // read from console
    String servername = c.readLine("Enter servername  : ");
    String portnumber = c.readLine("Enter portnumber  : ");
    String database   = c.readLine("Enter database    : ");
    String username   = c.readLine("Enter username    : ");
      // !! readPassword 'needs' JDK 1.6
    char [] password   = c.readPassword("Enter password    : ");
 
    // Construct connetionurl 
    String conUrl = "jdbc:sqlserver://" +
      servername + ":" + portnumber + ";databaseName=" + database +";" +
      "integratedSecurity=false;";
 
    // Declare the JDBC objects.
    Connection con = null;
    Statement stmt = null;
    ResultSet rs = null;

    try {	    
      // Establish the connection.
      Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
      con = DriverManager.getConnection( conUrl, username, new String(password));
 
      stmt = con.createStatement();
      rs = stmt.executeQuery( "SELECT @@servername, @@spid");

      // Iterate through the data in the result set and display it.
      while (rs.next()) {
        System.out.println("Connection test OK:  '" +
        rs.getString(1) + "' (pid is '" + rs.getString(2) + "').");  
      }
    }

    // Handle any errors that may have occurred.
    catch (Exception e) {
      e.printStackTrace();
    }
 
    finally {
      if (con != null) try { con.close(); } catch(Exception e) {}
    }
  }
}
 

Compile and Execute

To compile this you must of course have the JDBC driver class in you %CLASSPATH% ($CLASSPATH) environment variable. See example below.

Compile jdbcMSSql.java
C:\\oracle\\jdbc-test>echo %CLASSPATH%
.;C:\\oracle\\agent10g\\sysman\\jlib\\sqljdbc_1.2\\enu\\sqljdbc.jar

C:\\oracle\\jdbc-test>javac jdbcMSSql.java
Execute jdbcMSSql.class
C:\\oracle\\jdbc-test>java jdbcMSSql
Enter servername  : REMIDIAN01
Enter portnumber  : 1204
Enter database    : master
Enter username    : sa
Enter password    :
Connection test OK:  'REMIDIAN01\\MSSQL01' (pid is '51').

C:\\oracle\\jdbc-test>

Oracle GRID SQL Server Target Configuration

Now that I have the correct connection details I can go back the Oracle GRID console and provide the correct connection details in the Target Configuration page for the SQL Server target:

JDBC URL : jdbc:sqlserver://remidian:1204;integratedSecurity=false;instanceName=MSSQL01
JDBC Driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
 
 
 

N.B. – JDK 1.6 required

Program requires JDK 1.6 for the ‘console.readPassword’ is new in 1.6, read here http://blogs.sun.com/DaveB/en_US/entry/new_improved_in_java_se1. When you have < 1.6 simply change:

// char [] password   = c.readPassword("Enter password    : ");
String password   = c.readLine("Enter password    : ");

// con = DriverManager.getConnection( conUrl, username, new String(password));
con = DriverManager.getConnection( conUrl, username, password);
Java version banner
C:\\oracle\\jdbc-test>java -version
java version "1.6.0_02"
Java(TM) SE Runtime Environment (build 1.6.0_02-b06)
Java HotSpot(TM) Client VM (build 1.6.0_02-b06, mixed mode, sharing)

C:\\oracle\\jdbc-test>

2 thoughts on “Microsoft SQL Server 2005 JDBC Driver (oracle GRID)

  1. Jeff says:

    Good stuff!

  2. Reid Mancusi says:

    Hiya, I assumed you will be able to help me. I have a Microsoft SQL .ldf and .mdf file which I need to restore. Any opinions on how to achieve this? Regards

Leave a Reply

Helpful? - leave your note below so I can brag

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>