Connect to a FileMaker .fmp12 database using DatabaseLink?

Questions
, , ,
1

Using DatabaseLink, Is it possible to connect to and query a FileMaker .fmp12 database? If so, how?

2

Murray, according to this link in FileMaker community the poster states that it can be done. I don’t have access to FileMaker and I am assuming Mac OS on your end, but it jives with general JDBC connections.

3

A complete solution to my problem is presented in answers at the cited link in FileMaker Community. The following are resolutions of the particular issues I was having. I’m including them here for the sake of easier access.

  1. Make sure that the fmp (FileMaker Pro) database tables’ names include no spaces. (If necessary, replace any such space with an underscore by using FileMaker’s File > Manage > Database menu item, where with the Tables tab open, make the correction in the Table Name field and click the Change button.)

  2. In FileMaker, turn on ODBC/JDBC Sharing. (Open the database in File Maker. Use the File > Shairing > Enable ODBC/JDBC menu item and click the On radio button for ODBC/JDBC Sharing. If necessary, change the allowed access to the open file, e.g., select "Specify users by privilege set, click the Specify button, and check On for Full Access to the Admin account. The Admin account is one that FileMaker has by default, and by default it has no password. You may change that.)

  3. Before attempting to connect from Mathematica, make sure the FileMaker app is running and that the target database is open.

  4. Now in Mathematica…

    Needs["DatabaseLink`"]
Needs["JLink`"]
conn = OpenSQLConnection[
   JDBC["com.filemaker.jdbc.Driver", 
      "jdbc:filemaker://localhost/FMdatabase Name"], "Name" -> "FMP", 
      "Username" -> "Admin", "Password" -> ""]

Here FMdatabase Name is the name of the .fmp12 database file name, not the table name (and so spaces are OK here). In OpenSQLConnection: the value of option "Name" should be literally "FMP"; the value of "Username" should be literally "Admin" unless you changed the user name within FileMaker; and the value of "Password" should be the empty string unless you gave that user a password from within FileMaker.

The output from evaluating OpenSLQConnection[…] expression should be an SQLConnection[…] object (which you’ve assigned to variable conn (or whatever).

Now you may act upon the database. If it’s just a simple (flat) database, you’ll have just one table, as in:

    SQLTableNames[conn]
 {FMdatabase_Name}             (* note the underscore that you put in the name within FileMaker *)

    SQLSelect[conn, "FMdatabase_Name"]

etc. When done, of course evaluate:

    CloseSQLConnection[conn]
4

This routine doesn’t seem to work for FileMaker Pro Advanced v17 and Mathematica 12- any ideas?

5

It’s working for me with FileMaker Pro Advanced 17 and Mathematica 12 (under macOS Mojave 10.14.4).

Did you install the jdbc driver that is included in the FileMaker Pro Advanced .dmg? It’s in the Extras > xDBC > JDBC Client Driver folder.

Moreover, to connect from Mathematica, you already need to have FileMaker Pro Advanced running, of course, and to have the particular .fmp12 database open, too, so far as I can determine.

See FileMaker Pro 17 Advanced Help

6

Ah..I forget to position the driver in Math’a Java folder. I had to download it from the Filemaker Downloads page since it was absent from my FM17 Extras folder. All’s well now. Your input very helpful.

7

That’s not where the FileMaker docs say to put the jdbc driver, but if it works there, good!

8

Just for the record, its where Wolfram wants it - otherwise OpenSQLConnection reports driver error. Here’s the JavaClassPath[] with the driver fmjdbc.jar in position:

{“/Applications/Mathematica.app/Contents/SystemFiles/Java/”,
“/Applications/Mathematica.app/Contents/SystemFiles/Java/WolframSSH.
jar”, “/Applications/Mathematica.app/Contents/SystemFiles/Java/
WolframSSHKeyGen.jar”,
“/Applications/Mathematica.app/Contents/SystemFiles/Java/fmjdbc.jar”,
“/Applications/Mathematica.app/Contents/SystemFiles/Autoload/
PacletManager/Java/”,…}
{“/Applications/Mathematica.app/Contents/SystemFiles/Java/”,
“/Applications/Mathematica.app/Contents/SystemFiles/Java/WolframSSH.
jar”, “/Applications/Mathematica.app/Contents/SystemFiles/Java/
WolframSSHKeyGen.jar”,
“/Applications/Mathematica.app/Contents/SystemFiles/Java/fmjdbc.jar”,
“/Applications/Mathematica.app/Contents/SystemFiles/Autoload/
PacletManager/Java/”,

9

As I reported, OpenSQLConnection is working for me when fmjdbc.jar is in /Library/Java/Extensions and yet is not in /Applications/Mathematica.app/Contents/SystemFiles/Java/.