Earlier versions of FileMaker Pro and ODBC
Filemaker Pro has had the ability to connect to and use ODBC since version 7. Using the ExecuteSQL script step, you could specify an ODBC DSN (database source name) and execute any SQL statement you can create with a FileMaker Pro calculation that might include field data.
Limitations of this functionality include the following:
- Communication is only one way.
- You must be well versed in SQL.
- It must be done through a script.
In the databases I had created in FileMaker Pro 7 where data synchronization with a MySQL database was required, I was forced to first delete all the data in each MySQL table before repopulating it with live data from the FileMaker database.
FileMaker Pro 9 and ODBC
In FileMaker Pro 9, ODBC connections are treated as an external data source to FileMaker Pro, formerly known as a file references. The data source acts similar to a reference to another FileMaker Pro database file. You can add tables to the relationships graph, you can create layouts that display records from a MySQL table (or any MySQL-compatible database). ODBC). Calculations can be performed in the context of a data source table. You can even add calculation and summary fields to tables for use in FileMaker Pro.
Through a layout, you can add, delete, or modify external records just as if they were in a FileMaker Pro table. No SQL required. Hyphens are not required. Theoretically, you could create an entire FileMaker Pro database based solely on MySQL data tables with no tables defined within the FileMaker Pro file.
Now you can use FileMaker Pro as a friendly interface to any MySQL, MS SQL, Oracle, Access, or ODBC compliant database. The only thing you can’t do from FileMaker Pro is create tables and fields.
What is ODBC?
ODBC, short for Open Database Connectivity, is a standard protocol for communicating with databases. It allows users to configure a DSN, or Database Source Name, which can be used by any ODBC-compliant application on a particular computer to send queries to and receive data from a specific data source. It’s like having a shortcut or alias on your desktop that links to a file on a server, except the shortcut is a DSN and instead of a file on a server, it links to a database.
By configuring a DSN, you are assigning an arbitrary name, or shortcut, that your computer will recognize as a pointer to a server and database. The scope of a DSN can be restricted to a particular computer user or computer system. A single DSN cannot be used on multiple computers. If database connectivity is required on multiple computers, it is necessary to configure a DSN on each computer.
ODBC Configuration Overview for FileMaker Pro 9
There are 4 basic steps to getting FileMaker Pro 9 to work with other databases.
- Install an ODBC driver.
- Configure the DSN.
- Add the DSN data source to the FileMaker Pro database.
- Add the table(s) to the relationships graph.
Each of these steps is explained in detail below.
Installing the MySQL ODBC Driver for Mac OS X
There are several MySQL ODBC drivers available for Mac OS X, many of which have simple package installers. I’ve only gotten one to work properly with FileMaker Pro – Real ODBC Driver for Open Source Databases. It comes with a price tag of $30.
MySQL.com has a free, open-source driver available, but I couldn’t get it to work correctly for FileMaker, apparently due to a bug in Mac OS X’s iODBC driver manager. If you’re interested in the details, I’ve filed a report from mistake. Hopefully, they’ll have a job around soon.
Both drivers come with a standard, easy-to-use OS X package installer. Download, double click, click Next a few times and you’re done.
DSN Configuration
Once the driver is installed, you can create a DSN using the Mac OS X ODBC Administrator program located in Applications/Utilities/ODBC Administrator. FileMaker Pro 9 only supports system data sources and does not support user data sources. So you need to add a User DSN:
- Select the User DSN tab.
- Click the lock and authenticate to allow changes.
- Click Add.
- Choose the appropriate driver (probably “Real Open Source Databases”).
- Click Continue.
- Enter a name for the data source. This may be what makes sense to you, but if two or more computers are configured to use the same font, the names must be identical.
- Click Continue and enter the address of the MySQL database server.
- Enter the MySQL database server login information and click continue.
- Click the dropdown arrow next to the Database field and select the database this DSN should be linked to. If a list of databases on the MySQL server appears in this dropdown, you know a successful connection was made.
- Click Finish or Continue if you want to review the settings and test the connection.
Using the data source in FileMaker
In the FileMaker Pro database, complete the following steps:
- Open the Manage External Data Sources window via the File->Manage->External Data Sources… menu item.
- Click New… to add a data source.
- Enter a name, choose ODBC, then click the Specify… button.
- Choose the DSN data source you just created and click OK.
- If desired, enter a username and password for the MySQL server, or you can leave it set for users to enter. Because you can use calculations to specify user credentials, you can easily customize the authentication process, including passing the user’s FileMaker Pro database username and password to the MySQL server. Keeping users in sync between databases is a topic worthy of its own article.
Add a table to the relationships graph
The rest of the process is quite simple. When you go to the relationship graph and click the Add Table button, you’ll see your new data source in the Data Sources dropdown. Select it and add the desired table as you would any FileMaker database. You can then include it in any relationship, create new layouts with it, and add Calculation and Summary fields in the Fields tab.