Connecting to SQL Server 2000 and SQL Server 2005 DB using JAVA

After 'Connecting to Multiple DBs using torque',now lets get Connected to Sql Server db. Why always connecting to DB? Won't we have any other better topics than this. We do, but you will probably have to wait for that.

There is a reason why this topic is been chosen.Though connecting to MS SQL Server-2000 and SQL server2005 sounds same , but its not. And I even wont say that there are huge changes that we need to do for this.

There are few things that we definitely need to be aware of before connecting to either of these DBs.

a) We need three jar files to connect to Sql Server 2000.If I say 3 jars in this blog, then they are msutil.jar,mssqlserver.jar,msbase.jar. If we Use these three jar files, then the driver that we need to use is "com.microsoft.jdbc.sqlserver.SQLServerDriver"

b) Instead of using the 3 jars, we can use only one jar, sqljdbc.jar , to connect to SQL server 2000 DB. Now we need to use "com.microsoft.sqlserver.jdbc.SQLServerDriver" driver and NOT "com.microsoft.jdbc.sqlserver.SQLServerDriver" driver.

c) The most common mistake done by the developers is using sqljdbc.jar with "com.microsoft.jdbc.sqlserver.SQLServerDrive" driver. Without mercy this one results in "java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver" exception.


d) For Sql Server 2005 DB, Only sqljdbc.jar is enough to establish a connection with the DB. Here We need to use "com.microsoft.sqlserver.jdbc.SQLServerDriver" driver.


Using selectMethod=cursor

Consider a db-url for SQLserver DB that appears as below

db-url = jdbc:sqlserver://192.168.2.127:1433;databasename=developers_db;selectMethod=cursor

Can we establish a connection using the mentioned db-url, in both SQL server 2000 and SQL server 2005 DBs?
Ofcourse yes.

Using the 3 jars we can defintely use the db-url and connect to SQL server 2000 with no errors.

But, using "selectMethod=cursor" with sqljdbc.jar results in some exceptions which are mentioned as below

For SQLSERVER 2005 the exception would be

com.microsoft.sqlserver.jdbc.SQLServerException: sp_cursoropen/sp_cursorprepare: The statement parameter can only be a batch or a stored procedure with a single select, without FOR BROWSE, COMPUTE BY, or variable assignments.


and For Sql server 2000 the exception thrown would be

com.microsoft.sqlserver.jdbc.SQLServerException: sp_cursoropen/sp_cursorprepare: The statement parameter can only be a single select or a single stored procedure.



Looks like this is a known bug with sqljdbc.jar, and I guess its been fixed in the latest releases of the jar[but I am not sure of this]


Baseline

if sqljdbc.jar is used,
- use "com.microsoft.sqlserver.jdbc.SQLServerDriver" driver.
- avoid using "selectMethod=cursor" with the db-url.



I am adding a sample program along with this write-up, which may provide additional information about the topic.

There is no need to set any jar files in Environment variables or in any classpath. We will be loading the jar files dynamically.

---

void loadJarForSQLServerConnectivity(String pathToSQLServerJar) 
   throws IOException {
  try {

    URLClassLoader classLoader = (URLClassLoader) getClass().getClassLoader();
    URL url = new URL("file", null,pathToSQLServerJar);
    Class class1 = URLClassLoader.class;
    Method method = class1.getDeclaredMethod("addURL", URL.class);
    method.setAccessible(true);
    method.invoke(classLoader, url);

  } catch (Exception ex) {
    ex.printStackTrace();
  }
}

void getConnectionToSQLServer2000() {
  String dburl="jdbc:sqlserver://192.168.2.127:1433;DatabaseName=sqlserverdb_dev";
  String user = "sqlserverdb";
  String password = "sqlserverdb";
  Connection conn =null;
  try {
   loadJarForSQLServerConnectivity("C:/SQLSERVER2000_JARS/msbase.jar");
   loadJarForSQLServerConnectivity("C:/SQLSERVER2000_JARS/mssqlserver.jar");
   loadJarForSQLServerConnectivity("C:/SQLSERVER2000_JARS/msutil.jar");
 
   Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
   conn = DriverManager.getConnection(dburl,user,password);
   System.out.println("Established Connection with SQlServer-2000");

   } catch(Exception ex) {
      ex.printStackTrace();
   } finally {  
      closeConnection(conn); 
   }
} 

void getConnectionToSQLServer2005(){
 String dburl="jdbc:sqlserver://192.168.2.128:1433;DatabaseName=sqlserverdb_2005";
 String user = "sa";
 String password = "sa1";
 Connection conn =null;
 try {
    loadJarForSQLServerConnectivity("C:/SQLSERVER2005_JARS/sqljdbc.jar");

    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    conn = DriverManager.getConnection(dburl,user,password);
    System.out.println("Established Connection with SQlServer-2005");

 } catch(Exception ex) {
      ex.printStackTrace();
  } finally { 
      closeConnection(conn);  }
  }
 
void closeConnection(Connection conn){
  try {
    if(conn !=null ) conn.close();
  } catch(Exception ex) {
    conn = null;
  }
}