4

I'm trying to create a connection between R (3.3.3) Using RStudio (1.0.143) and Filemaker Pro Advanced 15 (15.0.3.305). I'm trying to create the connection using RODBC (1.3-15).

So far I:

Created a toy FM Pro database for testing

  • User id: Admin
  • Password: password

Followed these instructions for creating a DSN

Created a DSN for my toy FM Pro database called test_r

enter image description here

Successfully tested the connection to test_r

enter image description here

Unsuccessfully attempted to connect to the DSN in RStudio in the following two ways:

fm_connection <- odbcConnect(dsn="test_r", uid="Admin", pwd="password")

Which returns the following error:

[RODBC] ERROR: state IM002, code 0, message [unixODBC][Driver Manager]Data source name not found, and no default driver specifiedODBC connection failed

AND

constr <- paste("driver={FileMaker ODBC}",
               "server=127.0.0.1",
               "database=test_r",
               "uid=Admin",
               "pwd=password",
               sep=";")

fm_connection <- odbcDriverConnect(constr)

Which returns the following error:

[RODBC] ERROR: state 01000, code 0, message [unixODBC][Driver Manager]Can't open lib 'FileMaker ODBC' : file not foundODBC connection failed

However, you can see that the driver is there:enter image description here

Finally, I've unsuccessfully tried using these (and other) references to resolve this issue:

  1. https://cran.r-project.org/web/packages/RODBC/vignettes/RODBC.pdf
  2. https://community.filemaker.com/thread/165849

Nothing seems to work so far. I'm not tied to RODBC, but I do need a solution that works for Mac OS. Any help is appreciated!

Brad Cannell
  • 3,020
  • 2
  • 23
  • 39
  • Try to create a User DSN instead of System DSN. FileMaker ODBC is a connection to a FileMaker Database, make sure test_r is open. – Nicolai Kant Apr 30 '17 at 08:03
  • @NicolaiKant thanks for the suggestions. Created the User DSN. The Filemaker data was open. Same error. – Brad Cannell Apr 30 '17 at 18:37
  • Are you using 32 bit R install by any chance, as this might cause a problem. – Nicolai Kant Apr 30 '17 at 22:43
  • @NicolaiKant 64-bit – Brad Cannell May 01 '17 at 16:17
  • IS ODBC sharing on in FileMaker and is the ODBC user included in sharing? – Nicolai Kant May 02 '17 at 11:25
  • @NicolaiKant Yes – Brad Cannell May 02 '17 at 14:21
  • For odbcDriverConnect try to specify the full path to the driver – Nicolai Kant May 02 '17 at 16:59
  • @NicolaiKant that's interesting. Now the error message I get is: [RODBC] ERROR: state 01000, code 0, message [unixODBC][Driver Manager]Can't open lib '/Library/ODBC/FileMaker ODBC.bundle' : file not foundODBC connection failed – Brad Cannell May 02 '17 at 23:05
  • Could this be Access Privileges on that file? – Nicolai Kant May 03 '17 at 07:14
  • @NicolaiKant Not sure. I'm not sure how to check access privileges either. But, I got ahold of a Dell laptop at work today. Using the same process / code that I describe above, I was connected to my FM database in 5 minutes or less. No problems. It's really weird / ironic that I would have a much easier time connecting to FM Pro (an Apple subsidiary) on a PC, but not a Mac. – Brad Cannell May 03 '17 at 15:35
  • Don't think it is FileMaker problem, I used their ODBC on a Mac a few times with no problems. I think, the issue is in RODBC or R studio. – Nicolai Kant May 03 '17 at 23:24
  • Hi @BradCannell, maybe you've already solved this problem a while ago, but could you take a look at my answer below and let me know if it solves/would have solved your issues? – Bobby Oct 01 '17 at 10:54
  • Hi @Bobby, sorry for the delay. I haven't had a chance to try it out yet. I'll try to get back around to it soon. – Brad Cannell Oct 02 '17 at 14:42
  • Hi @BradCannell. Thanks very much. I know it can be tedious. I'll be presenting at a conference on this topic in a couple of days and it would be nice to have you feedback. – Bobby Oct 03 '17 at 16:54

2 Answers2

1

Here are some imortant troubleshooting steps for MacOS. I've had the same error in R and therefore I think there's a good chance that this your issue. The setup of ODBC can be rather complicated since several software components with multiple versions are involved. You've already verified that ODBC sharing is on in this particular FileMaker database.

Verify your installation of unixodbc:

ODBC Manager is actually optional. It manages the ini files described below. But after installing unixodbc, you can also edit these ini files in a text editor without ODBC Manager.

To install Homebrew, execute this command.

/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"

Then, install unixodbc. This provides for ODBC connectivity at the system level.

brew update brew install unixodbc

Verify the driver:

The driver should be installed here:

/Library/ODBC/FileMaker\ ODBC.bundle/Contents/MacOS

That folder will contain these two files:

SetupToolTemplate   fmodbc.so

Here's the full driver path:

/Library/ODBC/FileMaker\ ODBC.bundle/Contents/MacOS/fmodbc.so

Verify the config files:

The folder

/Library/ODBC

should contain these files:

FileMaker ODBC.bundle   odbc.ini        odbcinst.ini

Additionally, the unixodbc should contain only the latest version. If you have an earlier version, delete it. Now, only 2.3.4 is present.

/usr/local/Cellar/unixodbc/2.3.4

also contains 

odbc.ini        odbcinst.ini

Mirror the odbc.ini and odbcinst.ini files:

As described above, there are two copies of each of these files in two different locations. Make sure that the contents of both are equal. That means both copies of odbcinst.ini will define the drivers. And both copies of odbc.ini will contain the connections. Maybe this isn't 100% necessary, but it's what I needed to do.

Test with isql:

B:etc bobby$ isql -v DSNname admin password
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+

If you still have any issues after completing these steps, please share additional details so that I can update the answer.

Bobby
  • 1,585
  • 3
  • 19
  • 42
1

I got this to work using odbc instead of RODBC some new R code:

con <- DBI::dbConnect(odbc::odbc(),
                      driver = "/Library/ODBC/FileMaker ODBC.bundle/Contents/MacOS/FileMaker ODBC",
                      server = "127.0.0.1",
                      database = "/Users/bradcannell/Dropbox/Filemaker Pro/Notes/test_r.fmp12",
                      uid = "Admin",
                      pwd = "password")
Brad Cannell
  • 3,020
  • 2
  • 23
  • 39