A few years back I had used
freeTDS to be able to connect from my Mac to the
Microsoft SQLserver that is here in the lab. Since then I have switched computers
and upgraded operating systems, etc., and need to redo it. My goal here is to be
able to use the RODBC package to slurp data from the SQL tables into R.
I have a whole collection of notes from the last time I did this. It looks like there are some nice instructions here for connecting to postgreSQL. Which might be a good resource.
Let’s try it:
This can be done via homebrew, and maybe I will try that first, though last time I built it from source by hand. In the terminal:
brew update brew install unixodbc
That seemed very easy.
Last time I built this by hand, too, but it looks like we should be able to brew it today:
brew install freetds --with-unixodbc
The –with-unixodbc is esential, as otherwise you won’t get the file
That required a fair bit of crunching and had some caveats, but it looks like it completed without too
It looks like there are two different files that need to be modified to set up access to a particular
server or data base. They are
After the installations above, this file exists and has a section that looks like this:
# A typical Microsoft server [egServer70] host = ntmachine.domain.com port = 1433 tds version = 7.0
I modified that (use any text editor) to look like this:
# Big Creek Pit Tag data on Microsoft SQL-server at lab [bcpittags] host = 220.127.116.11 port = 1433 tds version = 8.0 client charset = UTF-8
This file was present on my system after I installed
freetds. Apparently one can put a comparable
file into their home directory if desired, but I am fine sticking it in at the system level. Here is
what I put in it:
[bcpittags] Driver=/usr/local/lib/libtdsodbc.so Trace=No Server=18.104.22.168 Port=1433 TDS_Version=8.0 Database=bcpittags
I tried connecting with my old username and password, and it failed, but then I tried setting up and accessing Doug Jackson’s data base with his username and password and it worked.
So, my account must have gotten deactivated (or the password changed) in the last upgrade to
pinniger. I found that Rundio can connect to
bcpittags from my machine using
his name and password (censored):
isql -v bcpittags daver ******** +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+
Hooray! Now, all that remains is checking to see that we can get to it from R.
First, I got the RODBC package. I had to build it from source:
install.packages("RODBC", type = "source")
Then, I tried this:
library("RODBC") # create a connection bcp <- odbcConnect("bcpittags", uid="daver", pwd="********") # see what it looks like: bcp #> RODBC Connection 1 #> Details: #> case=nochange #> DSN=bcpittags #> UID=daver #> PWD=****** # try to run a query lcbd <- sqlQuery(bcp, "SELECT * FROM MS_DC_genotyped_HDX_last_capt_and_beach_detect_UPDATED") # did we get something? dim(lcbd) #>  919 14
Cool! It is working!