5

I am trying to connect to R using RODBC. However, when I run

ch <- odbcConnect("rails_dev", uid="skline", pwd="d0gsleep")

I get this error: 

In odbcDriverConnect("DSN=rails_dev;UID=skline;PWD=d0gsleep") :
  [RODBC] ERROR: state IM002, code 0, message [iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be loaded

Any thoughts? I am new to this and at a loss for what to do next... thank you so much in advance!'

Note I am using Mac OSX

Spencer
  • 21,348
  • 34
  • 85
  • 121
  • I assume you're using ODBC Manager to set up the ODBC connection. Do you get an error when you use its Test functionality to test the connection with the uid and password you specify above? – Harlan Apr 27 '11 at 18:23
  • I have to be honest, I am a complete newb and don't even know how to use ODBC. Can you direct me to a walk-through as to how to set this up? – Spencer Apr 27 '11 at 18:26
  • 2
    ODBC will require you to have an ODBC driver for postgresql and something to configure it. Don't know if the driver exists. ODBC manager for OSX can be found here: http://www.odbcmanager.net/ but maybe you are better of using a direct binding like: http://rdbi.sourceforge.net/ – Eelke Apr 27 '11 at 18:41
  • `iODBC` seems to be the preferred driver manager for Mac OS X. As for generally what's going on: `ODBC` is one bridge to ⋲ {many databases}. In order to connect to e.g. MySQL db, it needs an ODBC-MySQL driver. – isomorphismes Jan 15 '14 at 08:39

2 Answers2

2

EDIT: The answer below is outdated, as some of the mentioned packages aren't available on CRAN any longer. I leave it here for reference.

More recent info can be found in the following question:

How to connect R with PostgreSQL on OSX 10.10.2?

For connection to PostgreSQL on Mac, you can use the package RpgSQL. Be sure to read the installation notes, as your PostgreSQL installation has to be set up for using JDBC. This is by far the easiest way. If you install PostgreSQL, you fire up the Stack Builder at the end of the installation process to do so (see the installation guide). Alternatively, you can use the Stack Builder (included in the postgreSQL installation) after installation to add extra components. Under Database drivers you should find postgresql JDBC.

The rdbi package mentioned in the comments, is removed from CRAN and afaik not maintained any more.

If you really need to use ODBC, you need a driver for PostgreSQL, and it has to be known to the ODBC implementation on your computer. The driver Jim M. gave you is one option, another one is the drivers of Openlinksw or the "official" one on pgFoundry. Normally, you should be able to download an ODBC driver from Stack Builder like you do for a JDBC driver : under the heading database drivers you have a postgresql ODBC as well.

For the newest versions of OSX, you need to install your own odbc manager apparently. Next to the one Eelke mentioned in the comments, you have the one from Apple as well here. In this manager, you can specify the driver location etc, and set up the connection to PostgreSQL through ODBC. How to do this, is dependent on the manager you use and is explained in the help files. It's always interesting to read the relevant section in the FAQ of iODBC and the sections on ODBC on Mac.

On a sidenote : In R, You can check which DSNs can be found by using the command odbcDataSources() of the RODBC package. If Postgresql is not mentioned in there, you know you have to look at your setup first.

Community
  • 1
  • 1
Joris Meys
  • 106,551
  • 31
  • 221
  • 263
  • Looks like packages given there are outdated now. Is there any better way now to have R and PostgreSQL connected? I've raised new question with details - http://stackoverflow.com/q/29317192/604388 – LA_ Mar 28 '15 at 12:34
  • @LA_ This is indeed a bit outdated. Thanks for noticing, I'll add the update to the answer – Joris Meys Mar 31 '15 at 14:49
0

Depending on the database you want to connect to, Actual Technologies has drivers for Mac OSX for open source databases (MySQL, PostgreSQL), Oracle, as well as Microsoft Access, etc, and costs about $35. Setup in ODBC administrator is straightforward whether you want to create a connection for all users on the machine (System DSN) or for user-specific level as there is a wizard that will walk through basic steps.