| Previous Versions of FileMaker Pro and ODBC | | | | package installers. I have only managed to get one to |
| Filemaker Pro has had the ability to connect to and | | | | work properly with FileMaker Pro: Actual ODBC Driver |
| use ODBC as early as version 7. Using the Execute | | | | for Open Source Databases. It comes with a $30 |
| SQL script step, you could specify a ODBC DSN | | | | price tag. |
| (Database Source Name) and execute any SQL | | | | has a free, open-source driver available, but I could not |
| statement that you can build with a FileMaker Pro | | | | get it to work properly for FileMaker due, apparently |
| calculation which could include field data. | | | | due to a bug in Mac OS X's iODBC driver manager. If |
| Limitations of this functionality included the following: | | | | you are interested in the details, I filed a bug report. |
| - Communication is only one way. | | | | Hopefully, they will have a work around soon. |
| - You must be well versed in SQL. | | | | Both drivers come with an easy to use, standard OS |
| - It must be done through a script. | | | | X package installer. Download, double click, click Next a |
| In databases I had created in FileMaker Pro 7 where | | | | few times, and you are done. |
| data synchronization with a MySQL database was | | | | Setting Up the DSN |
| required, I was forced to first delete all data in each | | | | Once the driver is installed, you can create a DSN |
| MySQL table before repopulating it with live data from | | | | using the Mac OS X ODBC Administrator program |
| the FileMaker database. | | | | located at Applications/Utilities/ODBC Administrator. |
| FileMaker Pro 9 and ODBC | | | | FileMaker Pro 9 only supports system data sources |
| In FileMaker Pro 9, ODBC connections are treated like | | | | and does not support user data sources. So, you must |
| a FileMaker Pro external data source, formerly known | | | | add a User DSN: |
| as file references. The data source acts similar to a | | | | - Select the User DSN tab. |
| reference to another FileMaker Pro database file. | | | | - Click the lock and authenticate to allow changes. |
| Tables can be added to the relationship graph, layouts | | | | - Click Add. |
| can be created showing records from a MySQL (or | | | | - Choose the appropriate driver (probably "Actual |
| any ODBC capable database) table. Calculations can | | | | Open Source Databases"). |
| be made in the context of a data source table. You | | | | - Click Continue. |
| can even add calculation and summary fields to the | | | | - Enter a name for the data source. This can be |
| tables for use within FileMaker Pro. | | | | whatever makes sense to you, but if two or more |
| Through a layout, you can then add, remove or modify | | | | computers are being set up to use the same source, |
| the external records as if they were in a FileMaker | | | | the names must be identical. |
| Pro table. No SQL required. No scripts required. | | | | - Click Continue and enter the MySQL database |
| Theoretically, you could create an entire FileMaker Pro | | | | server address. |
| database based solely on MySQL data tables with no | | | | - Enter MySQL database server login information and |
| tables defined within the FileMaker Pro file. | | | | click continue. |
| You can now use FileMaker Pro as a friendly | | | | - Click the drop down arrow next to the Database |
| front-end to any MySQL, MS SQL, Oracle, Access or | | | | field and select the database to which this DSN should |
| any ODBC capable database. The only thing you can | | | | link. If a list of databases on the MySQL server |
| not do from within FileMaker Pro is create tables and | | | | appears in this drop down, you know a successful |
| fields. | | | | connection was made. |
| What is ODBC? | | | | - Click Finish or Continue if you wish to review the |
| ODBC, an acronym for Open Database Connectivity, | | | | settings and test the connection. |
| is a standard protocol for communicating with | | | | Using the Data Source in FileMaker |
| databases. It allows users to set up a DSN, or | | | | In the FileMaker Pro database, perform the following |
| Database Source Name, that can be used by any | | | | steps: |
| ODBC aware application on a particular computer to | | | | - Open the Manage External Data Sources window |
| send queries and receive data from a specified data | | | | through the File->Manage->External Data |
| source. It is kind of like having a shortcut or alias on | | | | Sources... menu item. |
| your desktop linking to a file on a server, except the | | | | - Click New..., to add a data source. |
| shortcut is a DSN and instead of a file on a server, it | | | | - Enter a name, choose ODBC, then click the Specify... |
| links to a database. | | | | button. |
| By setting up a DSN, you are assigning an arbitrary | | | | - Choose the DSN data source you recently created |
| name, or shortcut, that will be recognized by your | | | | and click OK. |
| computer as a pointer to a server and database. The | | | | - If you wish, enter a user name and password for the |
| scope of a DSN can be restricted to a particular | | | | MySQL server, or you may leave it set to require |
| computer user or to a computer system. A single DSN | | | | entry by users. Since you can use calculations to |
| can not be used on multiple computers. If database | | | | specify user credentials, you can easily customize the |
| connectivity is required on multiple computers, it is | | | | authentication process, including passing the user's |
| necessary to set up a DSN on each computer. | | | | FileMaker Pro database user name and password to |
| ODBC Setup Overview for FileMaker Pro 9 | | | | the MySQL server. Keeping users synchronized |
| There are 4 basic steps for getting FileMaker Pro 9 to | | | | between databases is a topic worthy of its own |
| work with other databases. | | | | article. |
| | | | Add a Table to the Relationship Graph |
| 1. Install an ODBC Driver. | | | | The rest of the process is quite simple. When you go |
| 2. Set up the DSN. | | | | to the relationship graph and click the Add Table |
| 3. Add the DSN data source to the FileMaker Pro | | | | button, you will see your new data source in the Data |
| database. | | | | Sources drop down menu. Select it and add the |
| 4. Add the table(s) to the relationship graph. | | | | desired table as you would with any FileMaker |
| Each of these steps is explained in detail below. | | | | Database. You can then include it in any relationships, |
| Installing the MySQL ODBC Driver for Mac OS X | | | | create new layouts with it and add Calculation and |
| There are a number of MySQL ODBC drivers | | | | Summary fields under the Fields tab. |
| available for Mac OS X, many of which have simple | | | | |