Oracle \ Sun Java’s Interaction with MS SQL Server – Quick Prototype – Test out Connectivity along with Stored Procedure Invocation

Needed to review Oracle\Sun Java’s Interaction with MS SQL Server:

And so did the following:

  1. Downloaded Microsoft JDBC Driver. JDBC Version 3 is the latest out
    there and so downloaded that version.
  2. TextWrangler – Text Editor
  3. Created SQL Account on the DB Server. Granted minimum required access – SQL login Access – Specific Database Access – Permission to run Stored Procedure
  4. Settled on an “un-well” known TCP Port to facilitate SQL Server Remote Access.
    Opened Firewall Rules to support it.

Now that the Environment is a bit setup, took the Easy Street and combed the .Net
for HelloDB code.

But, kept getting the error pasted below:

———————————————————————————

java.lang.ClassNotFoundException: com.microsoft.jdbc.sqlserver.SQLServerDriver
at java.net.URLClassLoader$1.run(URLClassLoader.java:202)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:190)
at java.lang.ClassLoader.loadClass(ClassLoader.java:306)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
at java.lang.ClassLoader.loadClass(ClassLoader.java:247)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:169)
at DBConnect.getConnection(DBConnect.java:70)
at DBConnect.getConn(DBConnect.java:46)
at helloDB.main(helloDB.java:21)
Error Trace in getConnection() : com.microsoft.jdbc.sqlserver.SQLServerDriver

——————————————————————————-

Threw everything at the Problem:

  • Copied Microsoft’s JDBC Driver (sqljdbc4.jar) locally
  • Tweaked the CLASSPATH environment variable. As the Client is a Unix box
  • Learned to play “nice” with BASH
    • Used Export and unset to set & reset variable
  • Tried passing in the jar files via the -cp argument list
    • java -cp ./ helloDB
    • …. Note -cp ./ means that Java should check the local directory for Jar files
  • Copied the files to OS’s native location for JDBC Jar files

Finally gave up with trying to use Microsoft JDBC Driver. Downloaded JTDS changed a couple of lines:

1) Changed the name of the “Driver” class

from:

Microsoft’s MS SQL Server Driver:


Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");

to:
jDTS Driver:

Class.forName("net.sourceforge.jtds.jdbc.Driver");

2) Changed JDBC base URL

from:
Microsoft’s MS SQL Server JDBC Driver:
jdbc:microsoft:sqlserver://

to:
jDTS Driver:
jdbc:jtds:sqlserver://

jDTS worked well, and without much changes either.

And, so now it is getting late and I am getting tired.

Did what I did not want to do, copied the files to a MS Windows box.

Really no additional setup needed as the Database Connectivity Software
are self-contained in the Jar files.

Reviewed the CLASSPATH and Invoked the App. The error message read:

 

——————————————————————————

java.lang.ClassNotFoundException: com.microsoft.jdbc.sqlserver.SQLServerDriver

at java.net.URLClassLoader$1.run(Unknown Source)
at java.net.URLClassLoader$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Unknown Source)
at DBConnect_v2000.getConnection(DBConnect_v2000.java:70)
at DBConnect_v2000.getConn(DBConnect_v2000.java:46)
at helloDB.main(helloDB.java:23)
Error Trace in getConnection() : com.microsoft.jdbc.sqlserver.SQLServerDriver

——————————————————————————

Goggled for that error message:

and found http://blogs.msdn.com/b/jdbcteam/archive/2007/06/15/java-lang-classnotfoundexception-com-microsoft-jdbc-sqlserver-sqlserverdriver.aspx

MS Doc clearly states that:

The class name has changed between the SQL Server 2000 JDBC driver and the SQL Server 2005 JDBC driver.  This particular class “com.microsoft.jdbc.sqlserver.SQLServerDriver” is the class name for the SQL Server 2000 JDBC driver.  The SQL Server 2005 JDBC driver class name is “com.microsoft.sqlserver.jdbc.SQLServerDriver“.
Note the change: from “microsoft.jdbc.sqlserver” to “microsoft.sqlserver.jdbc”

In addition, the SQL Server 2005 JDBC driver has a different URL prefix from the SQL Server 2000 JDBC driver.  The SQL Server 2000 JDBC driver uses an URL prefix of “jdbc:microsoft:sqlserver://”, while the SQL Server 2005 JDBC driver uses an URL prefix of “jdbc:sqlserver://“.
Note the removal of “microsoft” from the URL prefix.


—————————————————————————————————

So now we know what works:

Changed the name of the “Driver” class

from:

Microsoft’s MS SQL Server Driver (v2000):

Class.forName(“com.microsoft.jdbc.sqlserver.SQLServerDriver“);

to:

Microsoft’s MS SQL Server Driver (v2005):

Class.forName(“com.microsoft.sqlserver.jdbc.SQLServerDriver“);

(
please be extra careful with this one as the Class name looks the same – but the fact is that a couple of entries are inter-changed –  jdbc.sqlserver is now sqlserver.jdbc
)

2) Changed JDBC base URL

from:

Microsoft’s MS SQL Server JDBC Driver (v2000):

jdbc:microsoft:sqlserver://

to:

Microsoft’s MS SQL Server JDBC Driver (v2005):

jdbc:sqlserver://

         (
             please be a bit careful with this one as the the middle entry
             for microsoft is dropped
        )

————————————————————————

References:
1) Using the JDBC Driver
http://msdn.microsoft.com/en-us/library/ms378526.aspx
2) How to Install Microsoft SQL Server JDBC
http://www.frank4dd.com/howto/database/howto-install-Microsoft-jdbc.htm
References (jDTS)

1) The jTDS Project
http://jtds.sourceforge.net/faq.html#urlFormat
References (Setting the Classpath)

1) Setting the Classpath in Java
http://introcs.cs.princeton.edu/java/15inout/classpath.html
References (WebObjects)

1) WebObjects 5 : How to use Oracle JDBC Drivers
http://support.apple.com/kb/TA26798?viewlocale=en_US

2) How do I install JDBC Driver on 10.5
https://discussions.apple.com/thread/1487101?start=0&tstart=0

References (MalcolmHardie Solutions)

1) Microsoft SQL Server and SQLEditor
http://www.malcolmhardie.com/support/sqleditor/mssqlserver.html

References (Java for OS X Lion)

1) Java for OS X Lion Update 1
http://support.apple.com/kb/DL1421
2) Java and the Mac OS
http://introcs.cs.princeton.edu/java/15inout/mac-cmd.html
References (Java for OS X Lion – Issues )

1) Java for OS X Lion Update 1
http://support.apple.com/kb/DL1421

References (Adobe)

1) Install the Java runtime (JRE) | Mac OS 10.7 Lion
http://kb2.adobe.com/cps/909/cpsid_90908.html

References (sqljdbc.jar)

1) How to get Started with Microsoft JDBC
http://support.microsoft.com/kb/313100
2) Working with a Connection
http://msdn.microsoft.com/en-us/library/ms378956(v=sql.90).aspx

1) Running standalone Groovy Scripts
http://www.vasanth.in/

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s