Frank4DD, @2008-2010

Introduction

JDBC is a set of classes and interfaces written in Java that allows Java programs to access a database. Microsoft provides a JDBC type 4 driver. Depending on the driver version, it requires a certain level of Java, the latest 3.0 drivers support Java 5 and 6.

Download driver package

At the time of testing, I got a package called sqljdbc_3.0.1301.101_enu.tar.gz. I found that latest driver here We can verify the content of the file: 

fm@susie:~> zcat sqljdbc_3.0.1301.101_enu.tar.gz | tar tf - | grep jar
sqljdbc_3.0/enu/sqljdbc.jar
sqljdbc_3.0/enu/sqljdbc4.jar

Install the driver package

In order to work with the driver, Java must be able to find it when called. We can achieve this by adding the drivers zip file location to the Java classpath, or by simply placing the driver files into the Java standard directory for extensions, for JAVA JRE this is the $JAVA_HOME/jre/lib/ext (i.e. /usr/lib64/jvm/jre-1.6.0-ibm/lib/ext/) directory. We need to copy the extracted sqljdbc.jar and sqljdbc4.jar.

Use the driver to access MS SQL Server through Java

The following example code JdbcTestMssql.java can be used to quickly access and test the JDBC connection. 

vi JdbcTestMssql.java
//JdbcTestMssql.java
import java.sql.Connection ;
import java.sql.DriverManager ;
import java.sql.ResultSet ;
import java.sql.Statement ;
import java.sql.SQLException;

class JdbcTestMssql {
   public static void main (String args[]) {
      try {
         Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
      }
      catch (ClassNotFoundException e) {
         System.err.println (e) ;
         System.exit (-1) ;
      }
      try {
         // open connection to database
         Connection connection = DriverManager.getConnection(
         //"jdbc:sqlserver://localhost:1433;databaseName=dbname;user=dbuser;password=dbpwd;"
         "jdbc:sqlserver://192.168.98.125:1433;databaseName=master;user=sa;password=pass;");

         // build query, here we use table "sys.databases"
         String query = "SELECT * From sys.databases" ;

         // execute query
         Statement statement = connection.createStatement () ;
         ResultSet rs = statement.executeQuery (query) ;

         // return query result
         while ( rs.next () )
            // display content from column "name"
            System.out.println ("MS-SQL Query result: " + rs.getString ("name")) ;
         connection.close () ;
      }
      catch (java.sql.SQLException e) {
         System.err.println (e) ;
         System.exit (-1) ;
      }
   }
}

Compile and test run

fm@susie:~> javac JdbcTestMssql.java
fm@susie:~> java JdbcTestMssql
MS-SQL Query result: master
MS-SQL Query result: tempdb
MS-SQL Query result: model
MS-SQL Query result: msdb

Should this test fail, typical reasons are:

Additional Literature: Microsoft SQL Server JDBC Driver