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.
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()
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
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’, ‘ [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.