Connecting to MS SQL Server from R on Mac/Linux

Question

I'm trying to connect to a Microsoft SQL Server from R on Mac/Linux, and I have problems with RJDBC. When I've downloaded both Microsoft's driver and JTDS, but none of the following lines work:

library(RJDBC)

drv <- JDBC("com.microsoft.sqlserver.jdbc.SQLServerDriver",
            "/Users/victor/Downloads/sqljdbc_3.0/enu/sqljdbc4.jar") 

drv1 <- JDBC('net.sourceforge.jtds.jdbc.Driver',
           "/Users/victor/Downloads/jtds-1/jtds-1.3.0.jar")

Each one returns the following error message:

Error in .jfindClass(as.character(driverClass)[1]) : class not found

I suspect the issue may be with the wrong Java version: my default java is

$ java -version
java version "1.7.0_11"
Java(TM) SE Runtime Environment (build 1.7.0_11-b21)
Java HotSpot(TM) 64-Bit Server VM (build 23.6-b04, mixed mode)

but I also have Java 1.6 installed on my machine. I have SquirrelSQL installed and it connects to MS SQL Server without any problem using JTDS driver; the Java version that SquirrelSQL runs under is 1.7.0.11.

I have run R CMD javareconf with the following result:

$ R CMD javareconf
Java interpreter : /usr/bin/java
Java version     : 1.7.0_11
Java home path   : /Library/Java/JavaVirtualMachines/jdk1.7.0_11.jdk/Contents/Home/jre
Java compiler    : /usr/bin/javac
Java headers gen.: /usr/bin/javah
Java archive tool: /usr/bin/jar
Java library path: 
JNI linker flags : -framework JavaVM
JNI cpp flags    : -I$(JAVA_HOME)/include

Updating Java configuration in /Library/Frameworks/R.framework/Resources
Done.

and removed/installed again RJDBC and rJava packages, and still nothing works.

I guess I'm stuck right now, as I'm not very familiar with Java/RJDBC and their interaction. Searching google found several people with similar problems but no solution.

Any tips on how to make JDBC behave, or any other way to connect to MS SQL Server from R, will be highly appreciated!

UPDATE 1. Well, the first statement seems to work now - I'm getting a connection and can query the database without any problems. Not sure what fixed the problem - may be I needed to restart my mac/R session. The second statement still doesn't work, with the same error message.

Answer 1

I had this problem with jtds-1.3.1 on Linux. The problem disappeared when I tried switching to jtds-1.2.7. It appears there is something in jtds-1.3.* which makes it incompatible with RJDBC.

Answer 2

I've been struggling for a while on this. Here's what I found.

1. Download from here -- Microsoft JDBC driver for SQL server
2. Unzip the file, where you will find sqljdbc4.jar.

3. Use:

   drv <- JDBC("com.microsoft.sqlserver.jdbc.SQLServerDriver", "<wherever sqljdbc4.jar is>")
   

This should work.

If i'm right, the idea is that for the PATH variable in drv function, one will need to specify the PATH where the JDBC driver is located (so if none, then downloaded it FIRST). Otherwise, one shall receive the common class not find error.