Message Boards Message Boards

0
|
8580 Views
|
8 Replies
|
1 Total Likes
View groups...
Share
Share this post:

Connect to a FileMaker .fmp12 database using DatabaseLink?

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

POSTED BY: Murray Eisenberg
8 Replies
Posted 5 years ago

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

POSTED BY: Colin Grace

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 https://fmhelp.filemaker.com/help/17/fmp/en/index.html#page/FMP_Help/odbc-jdbc.html

POSTED BY: Murray Eisenberg
Posted 5 years ago

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.

POSTED BY: Colin Grace

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

POSTED BY: Murray Eisenberg
Posted 5 years ago

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/",

POSTED BY: Colin Grace

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

POSTED BY: Murray Eisenberg

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]
POSTED BY: Murray Eisenberg

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.

POSTED BY: Hans Michel
Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Reply Preview
Attachments
Remove
or Discard

Group Abstract Group Abstract