3 Replies Latest reply on Oct 25, 2016 10:31 PM by astelix

    Setting up FMP 14 as a datasource for R

    jfrench

      Hello All,

       

      I am trying to use FMP 14 as a data source in R. I have ROBDC installed and active, and the fmp_xdbc listener is active. I have set my DSN up and turned on ODBC sharing in FMP.

       

      When I run the command to open the channel:

       

      channel <- odbcDriverConnect(connection = 'MR_DSN')

       

      I receive the following errors:

       

      Warning messages:
      1: In odbcDriverConnect(connection = "MR_DSN") :
      [RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
      2: In odbcDriverConnect(connection = "MR_DSN") :
      [RODBC] ERROR: state 01S00, code 0, message [Microsoft][ODBC Driver Manager] Invalid connection string attribute
      3: In odbcDriverConnect(connection = "MR_DSN") : ODBC connection failed

       

      I have made sure the listener process is active and functioning, but nothing works. Has anyone had success setting this up? Any help would be greatly appreciated.

       

      Thanks!

       

      -JF

        • 1. Re: Setting up FMP 14 as a datasource for R
          knussear

          Hi JF

           

          I have connections working for filemaker using the following - DSN_NAME is set up using the FM drivers

           

          library(RODBC)

          channel1 <- odbcConnect(dsn='DSN_NAME', uid = "ruser", pwd = "ruserpassword!")

          ## view tables

          sqlTables(channel1, errors = FALSE, as.is = TRUE)

          ## view columns in table named 'Table1'

          sqlColumns(channel1, 'Table1', errors = FALSE, as.is = TRUE, special = FALSE)

          ## get data

          dat <- sqlQuery(channel1, rows_at_time=2, query = "select * from Table1")

           

          Hope this helps

           

          Ken

          • 2. Re: Setting up FMP 14 as a datasource for R
            astelix

            library(RODBC) #

             

            # You can use "odbcConnect" for connection with an DNS

            con <- odbcConnect(dsn="Test", uid = "Admin")

             

            # or you can use odbcDriverConnect for dnsless connection. You must give it a connection string as a parameter

             

            constr <- paste("driver={FileMaker ODBC}",

                           "server=127.0.0.1",

                           "database=Test",

                           "uid=Admin",

                           sep=";")

             

            con<- odbcDriverConnect(cnstr)

             

            With an ODBC connection there may be issues on Windows if R and FilemakerPro are not both 32 bit or both 64 bit.

            On  Mac there may be problems  if your RODBC is compiled with unixODBC (which is the case on my machine) and your Filemaker ODBC Driver is installed with iODBC-Manager or ODBC Manager. In this case you must setup a unixODBC DNS. This doesn't work wth the ODBC Administrator and has to be do be done by hand - or you use the dnsless version.

            1 of 1 people found this helpful
            • 3. Re: Setting up FMP 14 as a datasource for R
              astelix

              And sometimes there are the simple things:

               

              Filemaker has to be running, the database must be loaded and shared. But I think you know that. Its not like a connection to MS Access, more like a SQL Server connection.