Connect R to Filemaker Pro 15 on Mac

Question

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

Successfully tested the connection to test_r

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:

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!

Answer 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.