Trouble with SQL Server JDBC

First of all, it's been a wile since my last post. There is no good reason, but I have been busy, real busy.
I have a new job, new town, a lot of new responsibilities and challenges. But enough with the excuses. Anyone can make excuses but only a few have what it takes to do something.

Now back to my post topic. Today at work I encountered a problem, and it was a SQL Server kind of a problem. JDBC problem to be accurate! I'm doing a Spring RESTful  web service that uses Hibernate ORM as a persistence layer and SQL Server 2008 as my database. I'm also using Maven as my build tool, and I started to add dependencies and got to SQL Server. My first awakening was that there is no official maven dependency for SQL Server! Once again Microsoft has no love for the Java people. There are a couple of work-arounds for solving this problem but for some reason I wasn't able to get it working. The easiest one that was suppose to work was using the open source jdbc driver for SQL Server, but it didn't seem to do the trick for me.

I ended up doing the following thing, maybe no the best thing to do but it got the job done. I excluded the sql server jdbc dependency form the pom file, and added the SQL 4 JDBC as a external library. Now the surprising part for me - you have to install the JDBC driver on you computer (I run Windows 7 on my office computer. Actually, now when I remember when I installed PostgreSQL I remember there was a option to install the JDBC driver for it, but for the life of me I can't remember if I checked it!), and it's not a simple next-next-next kind of installation.

Anyway I had the problem of installing the JDBC driver. I followed this blog post to the letter:
http://www.harlansmith.net/2012/01/installing-microsoft-jdbc-for-sql.html

You have to unpack it, then run the following in your cmd:
set CLASSPATH=.;JDBC_install_path\sqljdbc_version\language\sqljdbc4.jar

Remember:You can set only one driver for the classpath. It can be sqljdbc.jar OR sqljdbc4.jar (I recommend 4 - because at first I used the sqljdbc.jar and got the error that I need to use sqljdbc 4 with 1.7 JDK)

And don't forget to copy the sqljdbc_auth.dll to your System32 folder. If you follow the blog post that I mentioned then you will see a sample code to see if the driver is working. Try it out, just don't forget to add sqljdbc4.jar to your Build Path. And that is it, so I thought.

For some reason the sample code worked, but when I was deploying the war file to Tomcat I got the error that the underlying database could not be accessed (or something like that). It was the end of the work day so I had no patience to go on one more discovery adventure. I ended up setting the ODCB for the SQL Server I wanted to connect to. And than the JDCB url was not in the format of: jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]

But in a format of:
jdbc:odbc:odbc_name

 And it worked! Not the best solution, but finally I could finally test my web server. Perhaps I made a simple writing error when defining my jdbc url, so it didn't work, but I will try that our tomorrow, because tomorrow is another day. Perhaps tomorrow I will be smarter about the whole SQL Server JDCB thing.