Previous Versions of FileMaker Pro and ODBC
Filemaker Pro has had the flexibility to hook up with and use ODBC as early as model 7. Using the Execute SQL script step, you could possibly specify a ODBC DSN (Database Source Name) and execute any SQL assertion which you can construct with a FileMaker Pro calculation which might embrace discipline information.
Limitations of this performance included the next:
- Communication is just one approach.
- You should be properly versed in SQL.
- It should be executed by way of a script.
In databases I had created in FileMaker Pro 7 the place information synchronization with a MySQL database was required, I used to be pressured to first delete all information in every MySQL desk earlier than repopulating it with reside information from the FileMaker database.
FileMaker Pro 9 and ODBC
In FileMaker Pro 9, ODBC connections are handled like a FileMaker Pro exterior information supply, previously often known as file references. The information supply acts much like a reference to a different FileMaker Pro database file. Tables could be added to the connection graph, layouts could be created exhibiting information from a MySQL (or any ODBC succesful database) desk. Calculations could be made in the context of an information supply desk. You may even add calculation and abstract fields to the tables to be used inside FileMaker Pro.
Through a structure, you possibly can then add, take away or modify the exterior information as in the event that they have been in a FileMaker Pro desk. No SQL required. No scripts required. Theoretically, you could possibly create a whole FileMaker Pro database primarily based solely on MySQL information tables with no tables outlined throughout the FileMaker Pro file.
You can now use FileMaker Pro as a pleasant front-end to any MySQL, MS SQL, Oracle, Access or any ODBC succesful database. The solely factor you can’t do from inside FileMaker Pro is create tables and fields.
What is ODBC?
ODBC, an acronym for Open Database Connectivity, is a normal protocol for speaking with databases. It permits customers to arrange a DSN, or Database Source Name, that can be utilized by any ODBC conscious software on a selected laptop to ship queries and obtain information from a specified information supply. It is sort of like having a shortcut or alias in your desktop linking to a file on a server, besides the shortcut is a DSN and as an alternative of a file on a server, it hyperlinks to a database.
By organising a DSN, you’re assigning an arbitrary title, or shortcut, that will probably be acknowledged by your laptop as a pointer to a server and database. The scope of a DSN could be restricted to a selected laptop consumer or to a pc system. A single DSN cannot be used on a number of computer systems. If database connectivity is required on a number of computer systems, it’s essential to arrange a DSN on every laptop.
ODBC Setup Overview for FileMaker Pro 9
There are four primary steps for getting FileMaker Pro 9 to work with different databases.
- Install an ODBC Driver.
- Set up the DSN.
- Add the DSN information supply to the FileMaker Pro database.
- Add the desk(s) to the connection graph.
Each of those steps is defined in element beneath.
Installing the MySQL ODBC Driver for Mac OS X
There are plenty of MySQL ODBC drivers obtainable for Mac OS X, lots of which have easy bundle installers. I’ve solely managed to get one to work correctly with FileMaker Pro: Actual ODBC Driver for Open Source Databases. It comes with a $30 price ticket.
MySQL.com has a free, open-source driver obtainable, however I couldn’t get it to work correctly for FileMaker due, apparently on account of a bug in Mac OS X’s iODBC driver supervisor. If you have an interest in the main points, I filed a bug report. Hopefully, they are going to have a piece round quickly.
Both drivers come with a simple to make use of, customary OS X bundle installer. Download, double click on, click on Next just a few occasions, and you’re executed.
Setting Up the DSN
Once the driving force is put in, you possibly can create a DSN utilizing the Mac OS X ODBC Administrator program positioned at Applications/Utilities/ODBC Administrator. FileMaker Pro 9 solely helps system information sources and doesn’t help consumer information sources. So, you will need to add a User DSN:
- Select the User DSN tab.
- Click the lock and authenticate to permit modifications.
- Click Add.
- Choose the suitable driver (in all probability “Actual Open Source Databases”).
- Click Continue.
- Enter a reputation for the info supply. This could be no matter is smart to you, but when two or extra computer systems are being arrange to make use of the identical supply, the names should be equivalent.
- Click Continue and enter the MySQL database server deal with.
- Enter MySQL database server login info and click on proceed.
- Click the drop down arrow subsequent to the Database discipline and choose the database to which this DSN ought to hyperlink. If an inventory of databases on the MySQL server seems in this drop down, a profitable connection was made.
- Click Finish or Continue should you want to evaluation the settings and take a look at the connection.
Using the Data Source in FileMaker
In the FileMaker Pro database, carry out the next steps:
- Open the Manage External Data Sources window by way of the File->Manage->External Data Sources… menu merchandise.
- Click New…, so as to add an information supply.
- Enter a reputation, select ODBC, then click on the Specify… button.
- Choose the DSN information supply you lately created and click on OK.
- If you want, enter a consumer title and password for the MySQL server, or you could depart it set to require entry by customers. Since you should utilize calculations to specify consumer credentials, you possibly can simply customise the authentication course of, together with passing the consumer’s FileMaker Pro database consumer title and password to the MySQL server. Keeping customers synchronized between databases is a subject worthy of its personal article.
Add a Table to the Relationship Graph
The remainder of the method is sort of easy. When you go to the connection graph and click on the Add Table button, you will note your new information supply in the Data Sources drop down menu. Select it and add the specified desk as you’ll with any FileMaker Database. You can then embrace it in any relationships, create new layouts with it and add Calculation and Summary fields beneath the Fields tab.