[1]:http://www.mysql.com/
[2]:http://www.oracle.com/
[3]:http://www.microsoft.com/sql/default.mspx
On the off chance you’ve been hiding under an old Microsoft Access box for the last couple weeks, let me be the first to tell you that FileMaker now supports direct links to SQL databases. In other words, you can use table from a [MySql][1], [Oracle][2], or [Microsoft SQL Server][3] database right in FileMaker with no synchronization, no imports, and *no messy SQL programming*. This is A Big Dealâ„¢ and many of you are justifiably very excited about it.
And you probably have one burning question: “How the heck do I set this thing up?” The sad truth is that, for reasons well beyond FileMaker’s control, setting up a connection to a SQL database is the opposite of simple. Here’s the skinny on how to get your FileMaker database talking to a SQL database in as few steps as possible.
## What is SQL?
External SQL Sources (ESS for short) is a technology by which FileMaker can talk to a so-called SQL database. Think of it this way: FileMaker is like a nice SUV: powerful, elegant, and full of amenities. A SQL database, on the other hand, is more like an Abrams Tank. Sure, it’s a heck of a lot more powerful, but do you really want to ride in one every day? The truth is, SQL databases *are* a lot faster, leaner, meaner, and more oomphy than FileMaker. But they’re sole focus is data: They don’t know a thing about user interface, scripting, printing, PDFs, or any of the other high-level features you use every day.
To illustrate, here is what MySql looks like:
Um… ok.
To do anything with a SQL database, you typically write *queries* in a programming language called Structured Query Language, or **SQL**. So when we say FileMaker supports SQL databases, we mean it can automatically formulate the SQL commands necessary to get data out of and in to a SQL database on your behalf. From your perspective, it works just like any other FileMaker table. But behind the scenes, FileMaker is speaking a whole different language.
## The parts
When your FileMaker database starts using ESS, the number of moving parts in your little system goes up significantly. Instead of a simple `.fp7` file, you now have several pieces to consider:
* The SQL database server
* The ODBC driver
* The Data Source Name (or DSN)
>Note: Before you dive in, think about where you want to *put* all this stuff. If you’re using ESS in a standalone database, you’ll install the ODBC driver and set up the Data Source Name on *your computer* (or the local computer from which you’ll be accessing the data). This applies if you’re just testing, too. On the other hand, if you’re using a shared database, and every connected user needs access to the SQL database, install the driver and Data Source Name *on your FileMaker Server*. That way it will be accessible to everyone with the minimum fuss.
You’ll learn about each of these right…about…now.
### The SQL database server
All SQL databases are hosted with special server software (this *is* the SQL database, in fact…there is no client-only database like you get with FileMaker Pro or FileMaker Pro Advanced). FileMaker supports MySQL 5.0, Oracle 9g and 10g, and Microsoft SQL Server 2000 and 2005. You must have one of these servers up and running before you can get anywhere with ESS.
> Note: If you *don’t* have one, and you really want to get familiar with this technology, don’t fear! MySQL 5.0 is free free free. Just slide over to [the MySQL web site](http://dev.mysql.com/downloads/mysql/5.0.html) and download the MySQL Community Server 5.0 or later. It’s easy to install right on your own computer. Just make sure you choose the right download for your operating system.
### The ODBC Driver
In the early days of SQL databases, you had to write special computer code for each and every database. Even though the SQL language itself is fairly standardized, the actual communication protocols used to send those queries to the database were totally different in each system. This was, for obvious reasons, not entirely ideal. So the Open Database Connectivity system was born. You probably know this technology by its more common abbreviation: ODBC. ODBC says, “Listen, you talk to me, and I’ll talk to the database on your behalf.” Now, companies like FileMaker only have one set of programming interfaces to use.
> *If everything is so darned standard, why does FileMaker care what database system I use? Why can’t it talk to Sybase, Postgres, or Oracle 9 3/4?* While ODBC is a solid standard, you’ll note above, I said *SQL* is “fairly standardized.” Unfortunately, to get the kind of deep and automatic integration ESS provides, you need something a little better than fair. FileMaker needs to know about the intricate nuances of each database it supports so it can produce just the right SQL.
But how does ODBC know how to talk to each individual database system? It uses special *drivers*. You need an ODBC driver designed specifically for the database you’re talking to. If you’re on Windows, this is a breeze. Each of the database servers FileMaker supports has a free ODBC driver ready for you to download and install. If you use MySQL, you can get the driver [here](http://dev.mysql.com/downloads/connector/odbc/3.51.html). For the others, consult your documentation. The drivers should be available from your software CDs or from the vendor’s web site.
Mac OS X users have to work a little harder (or, better put, they have to spend a little dough.) Most vendors don’t provide a Mac OS X compatible ODBC driver. (The notable exception is MySQL: They pretend to offer a Mac OS X driver on their web site. But it doesn’t work well at all. It isn’t supported by FileMaker. Or anybody else for that matter.)
Instead, Mac users should head right over to [Actual Technologies](http://actualtechnologies.com/filemaker.php). This company has high quality, inexpensive drivers available for every database FileMaker supports (and a few others it doesn’t support). The link above takes you right to the page on their site where you can find exactly what you need to get going.
### The data source name
The ODBC driver represents the potential for your computer to talk to a SQL database. But it doesn’t know *which* database to talk to. So your next step is to configure it to point to your SQL database server. Since you might have *several* of these, your ODBC system lets you add as many configurations as you want. Each configuration is called a Data Source Name, or DSN. This funny name stems from the fact that the DSN is like a *name* for your *data source*. FileMaker will use that name to refer to the particular configuration. That way, you can *change* the configuration later and FileMaker won’t mind. (For example, if you move your database server, or rename the database itself.)
These three pieces come together to give your FileMaker database direct access to the back end SQL database. The rest of this article will focus on how to set up the DSN and make the connection in FileMaker.
## Creating a DSN
The DSN setup process is entirely different from Mac OS X to Windows. So skip ahead to the section that is right for you.
### Creating a DSN on Windows
You manage your ODBC data sources in the Administrative Tools control panel. Getting there varies from one version of windows to the next. But you start by choosing Start -> Control Panel.
– If you use Windows Vista, look for a category called System and Maintenance. Open it up and click the link for Administrative Tools. If you don’t see System and Maintenance, you will instead find an Administrative Tools icon right in the Control Panel window. Open it now.
– If you use Windows XP, look for a category called Performance and Maintenance. Open it up and click the link for Administrative Tools. If you don’t see the Performance and Maintenance category, look for an Administrative Tools icon in the Control Panel window itself. Either way, open Administrative Tools now.
Once you’ve opened the Administrative Tools window, look for something called Data Sources. This is where you configure data sources. It looks a heck of a lot like this:
To create a DSN, first switch to the System tab (FileMaker only works with so-called System DSN’s). Then click Add. You’ll see a list of all the ODBC drivers installed on your computer. Find the driver you installed in the list, select it, and click Finish. Setup from this point forward is the same on Mac and Windows, so skip ahead to Configuring your DSN below.
### Creating a DSN on Mac OS X
On Mac OS X, you manage your ODBC data sources in an application called ODBC Administrator. You can find it in the Utilities folder, which itself is in your Applications folder. Open it, and you’ll see something like this:
Before you can do anything in this program, you need to unlock it. Click the inconspicuous padlock icon in the bottom left-hand corner (you may have to enter an Administrator password). Then click Add. You’ll be asked to select an ODBC driver from a list. Pick the one you want, then click OK. In the window that pops up, **make sure you choose System when asked to pick a DSN type.**
At this point, you have created a DSN, but you haven’t finished configuring it. The exact steps for configuration vary from driver to driver (and platform to platform), but in general, you’ll need to provide this information:
– The host name or address of the SQL database server.
– The username and password needed to connect to the database.
– For some database systems, the name of the database you’ll be connecting to.
You may be asked to provide other information, but you can usually accept the answers it suggests. When you get to the last screenful of options, you’ll get the chance to test the connection. Click this button and make sure it tells you the test was successful. This is how you know you’ve made the right choices.
## Accessing a DSN in FileMaker
You have just two steps left. First, you need to tell FileMaker about the DSN you created. Then you’re ready to start accessing tables and defining relationships.
### Create an external data source
Thankfully, the hard part is over. Now you just need to tell FileMaker about your DSN, and point-and-click your way to SQL database bliss. First, open your FileMaker database. Then choose File -> Manage -> External Data Sources. The new Edit Data Source dialog box appears. Give your data source a name, and turn on the ODBC radio button. When you do, the window will instantly change to look like this:
Next, click the Specify button across the window from DSN. FileMaker shows you a list of system DSNs on your computer. Choose the one you want, then click OK.
Next, you need to decide how your usernames and passwords will be handled. If you choose “Prompt user for user name and password,” FileMaker will ask the user of your FileMaker database to enter a SQL database username and password. If that’s not your cup of tea, you can choose “Specify user name and password” instead, and key in the username and password to use. FileMaker will then automatically log in to the SQL database with these credentials when it needs to access the data.
> Note: Both the “User name” and Password boxes have a Specify button beside them. You can use a FileMaker calculation to derive the exact values if necessary.
The last section in this window is a bit of an enigma. These settings let you control which tables FileMaker is willing to show you when you start to work with this data source. Normally, you see all tables accessible through the DSN. If you want to see Views (which are kind of like stored queries that provide specialized results), turn on the Views checkbox under Filter by Type. Some database systems have special tables created by the system itself. To see these, turn on “System tables.”
You can also filter using some confusing criteria called “Catalog name,” and “Schema name.” The exact meaning of these boxes varies from one database system to the next. Here are the details:
– For Oracle:
– Catalog: Not used
– Schema: The name of the user who owns the tables
– For Microsoft SQL Server:
– Catalog: The name of the database
– Schema: The name of the collection of tables/views/etc
– For MySQL:
– Catalog: Not used
– Schema: The name of the user who owns the tables
To limit FileMaker to only the tables in a particular “catalog,” whatever that means to your database, enter the appropriate name in the “Catalog name” box.
Finally, you can restrict FileMaker to just one table by typing the table name in the same-named box.
> Note: You might wonder why you should bother restricting the tables in these ways. The answer is speed. The less FileMaker has to wade through to do its job, the faster it will run.
Once you’re finished making choices, click OK. You now have a link between FileMaker and your SQL database.
### Accessing a table through ESS
With the external data source defined, FileMaker now shows you SQL tables just like it would tables from another FileMaker Pro database. Choose File -> Manage -> Database and switch to the Relationships tab. Then click the Add Table button (
). In the window that pops up, choose your new external data source from the Data Source pop-up menu. After a short pause, you should see a list of available tables. Choose one and click OK.
The SQL table appears on your relationships graph just like any other table. You can drag lines to and from it to create relationships. When you leave the Manage Database window, you can make new layouts based on this table. You can perform finds using find mode, add new records, delete records, and edit the records that are there. In short, this table feels pretty normal.
There are many nuances to ESS development that you’ll need to familiarize yourself with as you go along. We’ll cover *using* ESS in a future article. But hopefully at this point you’ve got ESS up and running, and you’re ready to experiment.
I’m having a devil of a time trying to get the Mac ODBC connection working. I’m using a static IP for my website. I’m using either (I’ve tried both) the master username/password for connecting to pHpMySQLAdmin and the ftp site in general, or the master username/password for each of the MySQL dbs that reside on the server. Port is set a 3306 (but I’ve tried 21). Nothing but errors.
Anyone have a clue what I might try next?
TIA,
Bart
@bart: here are a few things to check.
First, make sure you have network access enable for MySQL. On the machine with your MySQL database server, open your
my.cnffile. It is probably in/etc/my.cnfor/etc/mysql/my.cnf. Find a line that saysbind-address = 127.0.0.1and make sure it is commented out (ie: there is a#at the beginning of the line).Second, make sure your accounts in MySQL are configured to allow remote logins. If you’re using the MySQL command line to create accounts, the syntax is like this:
The key is the
%in there. Often, a MySQL account is set up with@localhostwhich means you can only log in from the server machine itself. In this case you need an account that allows connection from a remote machine. The%says to allow logins from any other computer.Let me know if this helps.
Thanks, Geoff.
However, these dbs are located remotely on my host’s server (Bluehost.com), and of course they’re accessible. AAMOF, on the Windows side I routinely connect directly to these dbs, as well as the ones I have on my own Apache server using localhost and setup using WAMP, by using Navicat SQL Manager.
I haven’t tried using the ODBC connect on Windows (I’m told that the Windows ODBC SQL driver works fine).
It’s almost like I have a firewall setup on the Mac, and not being a Mac guru…perhaps I have! :-))
Bart
I probably should’ve also mentioned that I’m using the ActualTechnologies ODBC driver.
Finally!!
Required to tunnel into my host’s MySQL db repository, I needed something to effect that. First, I used Terminal.app, which worked fine by following the instructions provided by ActualTechnologies (ODBC plugin vendor). But the problem was trying to run this every time I started the Mac or FMP9.0 insofar as connecting with the same command line each time. So, I investigated further thanks to Geoff’s suggestion, and found AlmostVPN_1.5.1, which allows me to do just that. I’ve configured it to run the command line each time the Mac opens and stay neatly tucked away in the Menu Tray. And from there I can start and stop it whenever I need to, which suits my needs pretty well.
Now having connected to some of my hosted MySQL dbs I’ve found out just how powerful this functionality is in terms of speed of rendering x number of records. Much faster than FMP. I think we’re going to have some fun with this one!
Thanks for all the help, Geoff, and Jonathan Monroe of ActualTechnologies, too.
This is a great article thanks for taking the time to post such a priceless guide..
Im currently having some different issues, whilst I have MYSQL setup and can actually pull tables into FM9A I permanently get incorrect information especially if I create a new field set in mysql, it seems filemaker is caching it ???
I also get
ODBC Error: [MYSQL][ODBC 3.51 Driver][mysqld-5.041-community-nt]Unknown column ‘mytest’ in ‘field list’
however, its there !
But.. its a start and im connected, so I cant wait for your next article
@steve: I don’t know if I understand exactly what you’re seeing. A few things to bear in mind:
If you add new columns in MySQL, you need to go to File -> Manage -> Database, switch to the Fields tab, pick your MySQL table, and click the Sync button. Otherwise FileMaker will be confused about the columns available.
It is possible (although unlikely) that an account will have access to one column but not another. To see specifics, from the MySQL command line, enter this command:
You will see a list of specific privileges. Make sure the user has privileges to see the new columns you have added.
Also, when you say you get incorrect information, do you mean the data in the fields in FileMaker does not match the data in the same columns in MySQL?
@bart: awesome; glad it is working now
ahh … That sync button sneeked in there, maybe I was getting a little too ambitous.
altough, that presents a different issue.
Time to start from the beginning and see what prevails
Heres a screenshot of some of the errors im getting, maybe its the way ive set it all up, not sure yet, its a new toy and im keen 🙂
http://www.sws-solutions.co.uk/miscfiles/mysqlerror.gif
As you can see, it gets id and then strangely gets code as ,code which is where i think the error is coming from.
sorry for flooding your blog… cant edit my last post, but also wanted to include this next image, where it succesfully pulls fields and sync works, except as you can see, it skips every other field from myqsql.
http://www.sws-solutions.co.uk/miscfiles/mysqlerror2.gif
Im starting to think its more of an ODB driver problem rather than filemaker but not sure.
Dont worry, I dont expect answers, but thought you may be interested to see.
Do anyone know if there are any ODBC drivers for Pervasive SQL? Or, if there is a workaround for FMP 9?
We have a vertical scheduling package for a non-profit mental health clinic which uses Pervasive SQL as it’s engine. We use FMP as a forms server for clinicians, but I would love to add the ability for them to see their coming schedule live, using FMP as the front end.
It’s a variant of the Btreive format, which does have ODBC driver support.
Should add, am running a winXP environment.
@steve: I think you may be able to turn on query logging in your Data Source Name setup. If that is possible, you could see exactly what FileMaker is sending and receiving. Email me (use the Contact button at the top of this page) with those queries and maybe I can help make sense of this. I’m stumped at this point.
@ken: In this case, the driver is not the only concern. FileMaker has to know a lot about the database it connects to for ESS to work. It only supports MySQL 5.0, MS SQL Server 2000/2005 and Oracle 9g/10g. No other database will work.
If you can find a driver, you can still use the ODBC Import and Execute SQL script steps (which have been in FileMaker for along time) but you’ll have to write your own SQL and you won’t get live access to the data. Bummer.
Great article – got access to 3 diff SQL Server databases in no time at all …
I have more questions though about how to handle the new “tables” in FM –
in particular, and unsurprisingly, the only types of fields that you can create are calcs and summaries – which, with my limited FM knowledge, begs the question of how you automate the creation of one to one records in a second FM table (to contain your FM-specific/additional data) ie as new entries are made into the underlying SQL database?
I may be missing the point but hopefully you can cover in the next article real soon (please!)
@simon: would it be good enough to do this:
Create a new FileMaker table with an ID field of the same type as your SQL table. Then create a relationship between the SQL table and the new table by matching the IDs. Finally, turn on “Allow creation of related records” in this relationship so that from the SQL table, Filemaker will auto-create related record in the new table as needed.
Once this is set up, you can put field from the new table on your layouts (the layouts tied to the SQL table). As soon as you type in these fields, or use a script to
Set Field, etc… FileMaker will make a new one-to-one record for you. Searching will work as well.This technique is not perfect, but it should work in a lot of cases to add FileMaker specific data to the SQL table. Does that address your question?
I have connected to the MSSQL database successfully.
But I couldn’t connect to MySQL Database. I am getting the error “ODBC datasource you have selected is not supported.”
What I have to specify in Schema Name text box in Filemaker 9.
But I have configured MYSQL datasource successfully.
@kannappan: Are you sure you’re using MySQL 5.0? It will not work with 4. You can leave the Schema Name box empty (and you probably should in most typical cases).
May be a stupid question, but does the configuration outlined above have to exist on each client that will use the Filemaker db? Even if it is served by Filemaker Server?
Geoff,
Thanks for speedy response – got a trial going and can see what you mean – I can also see that on opening a window you could run a script – just not sure how to translate this from the prototype to my real appn.
Many thanks
@Ross – Great question. If the file is served, you only have to setup the ODBC connection on the FileMaker server itself. This is a Sys-Admins Dream! No more having to duplicate the ODBC connection on each and every box…. It saves loads of time.
@steve: I had the same problem with half the field names becoming prefixed with a comma, and the other half not displaying at all. The problem was solved by reverting to the 3.51.14 version of the MySQL ODBC driver; don’t use the latest version 3.51.17.
In my case, the ODBC connection was on a Windows XP Professional installation (UK), but I don’t know whether that is relevant here.
@brian: That is a great find. Can anybody else confirm the same? I have seen lots of reports of this “comma-in-the-field-name” problem.
With MySQL Connector 3.51.17 installed on Windows Server, the DSN doesn’t give the option for selecting a database. Thus, FileMaker throws an error stating the database ‘ ‘ is invalid.
With MySQL Connector 3.51.14 installed, the DSN does give the option, however there are now either “comma-in-the-field-name” errors OR the following:
“ODBC Error: [MySQL][ODBC Driver][mysqld-5.0.45]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mydatabase`.`mytable` LIMIT 0' at line 1”
What I find particularly interesting is the missing “`” before mydatabase. I have experienced trouble importing data from MS Access Databases without these.
I would like to see a continuation of the topic
@maximus: I’m finally getting rolling on a real system built with ESS. Once I have this under my belt, I plan to revisit this topic and share my experiences.
Geoff
Why does *nobody* ever talk about data going the *other* way: getting data from Filemaker *to* a(n) SQL database? It seems impossible that this can be a “one way street,” but a friend of mine and I have been trying for two weeks to have edits to a Filemaker DB get reflected in a remote SQL DB that is feeding web pages, and it is so esoteric as to be next to impossible. The one time we came close it was like watching paint dry it was so slow.
Is there no way to do this without repeatedly exporting the entire DB every time there is an edit or change to a found Filemaker set of data?
It seemed from all the hype like this would be easy, but so far it’s entirely defeated us.
Denriddy
@Ned
In case you didn’t get your answer. This article focuses on the manipulation of data in a SQL database…via a FileMaker interface. Data stored in SQL, but changed using FileMaker.
Going the other way, Data in FileMaker and replicated in SQL is an entirely different beast. It is definitely possible. I am not sure it’s easy, not having worked with it too much, but possible.
Unless anyone else has any other ideas, I would look at the SQL commands from FileMaker. You may be able to set either SEND, POST, OR EXPORT commands to get your data from FMP to SQL. Exact procedure will depend on the version of SQL you are using.
There are a number of plugins also available to automate the migration of data between the two. Trying Google-ing data migration with FileMaker and SQL. That should get you moving in the right direction.
I agree with Ned – this is exactly what Ive been trying to do using scripting, EXECUTE SQL -> calculated SQL text – with syntax thats looks like “INSERT INTO contact_test (last_name) VALUES (” & people::fname &”)” – where the first part is the destination table and fields within MYSQL and the values are coming from my local FM table.
One way or another I cant get the exact syntax correct. I have it sending the SQL to MYSQL but I always get a MYSQL error coming back – it has something to do with the use of &’s and single and double quotes.
Once I get it right I will make another post – likewise if anyone has got it right please let us know.
Max:
If you look at the result of your calculation in the data viewer, you’ll see output like this:
This is almost perfect, but you need to quote the first name. The formula to do that would look like this (hard to read!):
Which yields:
Unfortunately, a single quote next to a double quote in the FileMaker calculation dialog can be exceptionally hard to see, which makes writing calculations like this error prone. To solve this, we use a handful of custom functions. For example:
Now your calculation can look like this:
Which, to my eye, is much easier to read (and therefore less error prone).
Hope it helps,
Geoff