Accessing Filemaker Pro Server 11 via ODBC/SQL

Anyone who has tried to access Filemaker Pro Server (and Server Advanced) data via ODBC, has probably run into the myriad of weird issues. In this post, I’ll try and catalogue some of them, to help anyone else forced into this desperate method of communication.

Getting Started

You  need to have the ODBC driver installed. Your server needs ODBC/JDBC access turned on. And your file also needs to have a user you can log in to via ODBC. When you configure the ODBC driver for FMP, make sure you run the test, to check your login.

Then try something simple like this, where SQL is a string literal.

conn = pyodbc.connect("DSN=odbcconnectionname;UID=odbc_user;PWD=odbc_password")
cursor = conn.cursor()
cursor.execute(SQL)
tmp = cursor.fetchall()

Syntax

The FMP11 SQL syntax has it’s own idiosyncrasies when compared to SQL server or MySQL. In fact, if you try and run pyodbc + fmp through SQLalchemy, you get spectacular segfaults in my experience. FMP’s Syntax is basically the same in later versions, and v13 is documented here.

Here’s a rundown of some key points that will strike any MySQL user as a bit odd:

  • Single quote only for string literals, double quotes mean you are referring to the name of a table.
  • It’s SQL standard, but you’ll find that you need to use braces, or DATE ‘YYYY-MM-DD’ to get FMPS to accept your dates, times and timestamps. Even when it does though, inserting such dates and times, completely disables the (FMP11s) ability to use formatting on the data. Even if you wipe the format, it won’t use any custom or preset formatting on a field displaying that data.
  • GETAS function you need to call on containers, supplying it with information such as “JPEG” for a jpeg image! (really, FMS should know better than you, what the data is).
  • Alternatively, you can use CAST instead of GETAS

Stupid Miscellany

So, some of this is weird:

Even if you use %s to insert strings into your SQL query (of course after sanitising them), you can get weird syntax errors, that are resolved by wrapping your code in str(). I’m pretty sure this is pyodbc. Here’s an example of the error:

pyodbc.ProgrammingError: (‘42000’, ‘[42000] [FileMaker][FileMaker] FQL0001/(1:1): There is an error in the syntax of the query. (8310) (SQLExecDirectW)’)

You also really have to have your head around all the weird FMPness. If you select calculation fields, or even worse summary fields, it needs to calculate values across joins and across the set. In my work so far, I’ve found that FMP is a lot better performer than I thought, when it’s just acting as a database, and I don’t ask it to return calculations or summaries. Now, it’s fairly safe to do calculations that don’t interrelate other tables. If you are dealing with big complicated interactions, better to replicate the login in your code. FMP tends to decide it’s going to lock tables until it can get all the calculated data, which causes requests to back up. It’s a terrible and ugly thing to see.

And even when it doesn’t do that, you can run into concurrency issues. If you have a search running on a cross-table calculation field, which uses a relationship with indexes, that is having data rapidly pumped into it on a big table, you’ll get all kind of server crash issues.

It seems like often, there’s not really a great deal of transparency why such a search didn’t lock the other table either, and it’s not a function of overflowing the cache, it happens whether you have 800MB of cache or 32MB.

This list is far from complete, and I intend to add to it, as I continue. I might also (if I can get permission) share a barebones ORM I’ve written.

2 thoughts on “Accessing Filemaker Pro Server 11 via ODBC/SQL

  1. When I try this:
    conn = pyodbc.connect(“DSN=myDBname;UID=admin;PWD=admin”)
    I always get this:
    pyodbc.Error: (‘IM002’, ‘[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnect)’)

    If I specity my driver:
    conn = pyodbc.connect(“DRIVER={FileMaker ODBC}; DSN=myDBname; UID=admin; PWD=admin”)
    I get:
    pyodbc.Error: (‘01000’, “[01000] [unixODBC][Driver Manager]Can’t open lib ‘FileMaker ODBC’ : file not found (0) (SQLDriverConnect)”)

    I’m on El Capitain with FM14.

    • Okay, so you’ve got an error, and now you’ve made a new one by deviating from the tutorial😉
      The problem is that your DSN isn’t working, or is named differently. The DSN already has a driver defined, so there’s no need to define it in here, unless you are doing it all from scratch.
      Have you set up an ODBC connection to filemaker?

      Fire up ODBC Manager (or install it then open it, if you don’t have it installed). You should see a list of “System DSN”. The DSN names, are in the “Name” column. Bear in mind, these DSN names are the ones you need to reference, if you named the ODBC connections differently from the databases, then those ODBC connection names are what you reference. The fact you say “myDBname” rather than “myDBODBCConnectionName” indicates that this is likely where you made a mistake.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s