13

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.

Vladislav Povorozniuc
  • 2,149
  • 25
  • 26
Victor K.
  • 4,054
  • 3
  • 25
  • 38
  • I got this error when I changed from `R 3` over to `pqR`. All I can offer toward a solution is use `locate sqljdbc4.jar` to find whatever needs to be loaded. – isomorphismes Feb 13 '14 at 07:31

6 Answers6

5

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.

Michał Trybus
  • 11,526
  • 3
  • 30
  • 42
Meng Zhao
  • 401
  • 5
  • 9
4

The following code achieves your goal of connecting to R from Mac OS x. Download the Microsoft JDBC driver from Microsoft here

Link to Gist / Code in Github

# install.packages("RJDBC",dep=TRUE)
library(RJDBC)
drv <- JDBC("com.microsoft.sqlserver.jdbc.SQLServerDriver" , "/Users/johndacosta/Downloads/sqljdbc_4.0/enu/sqljdbc4.jar" ,identifier.quote="`")
conn <- dbConnect(drv, "jdbc:sqlserver://192.172.1.210:55158;databaseName=master", "sa", "password")
d <- dbGetQuery(conn, "select * from sys.databases where database_id <= 4 ")
summary(d)
john.da.costa
  • 4,682
  • 4
  • 29
  • 30
2

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.

Alex Vorobiev
  • 4,349
  • 21
  • 29
2

I had the exact same problem. This is a jTDS solution:

  1. Download jTDS 1.2.8 and unzip it. Say it is saved at ~/Downloads/jtds-1.2.8-dist/jtds-1.2.8.jar. Note: Other versions may not work!
  2. From R, set up driver: drv <- JDBC("net.sourceforge.jtds.jdbc.Driver", "~/Downloads/jtds-1.2.8-dist/jtds-1.2.8.jar").
  3. Set connection object: conn <- dbConnect(drv, "jdbc:jtds:sqlserver://servername:port;DatabaseName=databasename", domain="windows domain", user="user", password="pwd").

Here the field domain was messing things up. You can't put domain\username as your user. You have to define them separately according to the jTDS driver implementation.

Boxuan
  • 4,937
  • 6
  • 37
  • 73
0
 library(RJDBC) 
 cp <- c
 ( 
      "<usr path>/jdbc/mdb/log4j.jar", 
      "<usr path>/jdbc/mdb/commons_lang.jar", 
      "<usr path>/jdbc/mdb/commons_logging.jar" 
  ) 

 .jinit(classpath=cp) 

 drv <- JDBC("com.microsoft.sqlserver.jdbc.SQLServerDriver",
        "/Users/victor/Downloads/sqljdbc_3.0/enu/sqljdbc4.jar")
Baburao Kamble
  • 56
  • 1
  • Thanks Baburao. What is in this context? – Victor K. Jan 31 '13 at 19:55
  • I've found the only place on my hard drive that has `log4j.jar` etc: `/Users/victor/ARIS71/DownloadClient/170.194.12.195/jdbc/mdb/` (not sure what ARIS71 is). I added the lines you suggested, and still nothing works. The Microsoft library stopped working as well, although it worked just yesterday when I posted my update. I'm totally puzzled. – Victor K. Jan 31 '13 at 19:55
0

Same error happened to me earlier when I was trying to use RJDBC to connect to Cassandra, it was solved by putting the Cassandra JDBC dependencies in your JAVA ClassPath.

See this answer:

Community
  • 1
  • 1
Madcat
  • 379
  • 2
  • 7