Connecting to SQL Server from R using RJDBC

A few months ago I switched my laptop from Windows to Ubuntu Linux. I had been connecting to my corporate SQL Server database using RODBC on Windows so I attempted to get ODBC connectivity up and running on Ubuntu. ODBC on Ubuntu turned into an exercise in futility. I spent many hours over many days and never was able to connect from R on Ubuntu to my corp SQL Server.

Joshua Ulrich was kind enough to help me out by pointing me to RJDBC which scared me a little (I’m easily spooked) because it involves Java. The only thing I know about Java is every time I touch it I spend days trying to get environment variables loaded just exactly the way it wants them. But Josh assured me that it was really not that hard. Here’s the short version:

Download the RJDBC driver from Microsoft. There’s Win and *nix versions, so grab which ever you need. Unpack the driver in a known location (I used /etc/sqljdbc_2.0/). Then access the driver from R like so:

require(RJDBC)
drv <- JDBC("com.microsoft.sqlserver.jdbc.SQLServerDriver",
  "/etc/sqljdbc_2.0/sqljdbc4.jar") 
  conn <- dbConnect(drv, "jdbc:sqlserver://serverName", "userID", "password")
#then build a query and run it
sqlText <- paste("
   SELECT * FROM myTable
  ", sep="")
queryResults <- dbGetQuery(conn, sqlText)

I have a few scripts that I want to run on both my Ubuntu laptop and my Windows Server. To accommodate that I made my scripts compatible with both by doing the following to my drv line:

if (.Platform$OS.type == "unix"){
         drv <- JDBC("com.microsoft.sqlserver.jdbc.SQLServerDriver",
         "/etc/sqljdbc_2.0/sqljdbc4.jar")
} else {
         drv <- JDBC("com.microsoft.sqlserver.jdbc.SQLServerDriver",
        "C:/Program Files/Microsoft SQL Server JDBC Driver 3.0/sqljdbc_3.0
         /enu/sqljdbc4.jar")
 }

Obviously if you unpacked your drivers in different locations you’ll need to molest the code to fit your life situation.

EDIT: A MUCH better place to put the JDBC drivers in Ubuntu would be the /opt/ path as opposed to /etc/ which I used above. In Ubuntu the /opt/ directory is where one should put user executables and /etc/ should be reserved for packages installed by apt. I’m not familiar with all the conventions in Ubuntu (or even Linux in general) so I didn’t realize this until I got some reader feedback.

Be forewarned, RJDBC is pretty damn slow and it appears to no longer be in active development. For my use case, RODBC was clearly faster. But RJDBC works for me in Ubuntu and that was my biggest need.

8 Comments

  1. Vinh Nguyen says:

    I use RODBC with the FreeTDS drivers to access MS SQL Server in Ubuntu and Mac OS X. See http://blog.nguyenvq.com/2010/05/16/freetds-unixodbc-rodbc-r/ for a rough outline.

  2. JD Long says:

    Vinh, you’re the second person who’s pointed me back to FreeTDS. I clearly need to look into it in more detail again. I think part of my problem was that I am using SQL Server 2008. Support for SQL Srv 2008 may now be more feasible than it was when I was first trying to get this to work. Thanks for reading and thanks for the comment!

  3. Vinh Nguyen says:

    You have to make sure that FreeTDS is working (isql and tsql commands) before you even try it with R and RODBC. Let me know how it goes.

  4. Justin says:

    I used RJDBC a while back, and it really saved me some time. Glad other people are getting the word out:
    http://scwn.net/2008/08/04/rjdbc-to-the-rescue/

  5. JD Long says:

    Thanks for the link to your site, Justin!

  6. Eric MacAdie says:

    So what is the replacement for RJDBC?

  7. Andy says:

    I bumped into this while looking at something else in your blog. I agree with the others here who have written that FreeTDS works well. It works, but it is a pain to configure, since it involves configuring both FreeTDS and ODBC. Neither is particularly hard, but it seems like I do each configuration just often enough to forget some important detail. Grrr.

    jTDS (http://jtds.sourceforge.net/) is a OSS jdbc driver for SQL Server.

    If I am going to use a database on a regular basis, I will go the extra mile and configure FreeTDS and ODBC and connect via RODBC. But, because much of my work is short-term contracts where I go in, calculate something and then move on; I find jTDS to be a nice alternative.

  8. sk says:

    this post helped me lot – thanks!
    only the sqljdbc4.jar is really needed, maybe this is worth mentioning as well (for people being even more spooked by java than you). anyway – thanks again!

Leave a Reply