Tim Dietrich

Custom Software Developer

Home Services Portfolio Blog About Contact Newsletter

FileMaker's SQL Reserved Keywords

I'm developing a new client system that will be used to collect personal references. The table that those references are stored in is named "References." (Clever, eh?)

Everything was going well, until I reached a point where I needed to query the table. I was using ExecuteSQL with a SELECT statement that looked like this: 

SELECT Reference_ID, Reference_Name, Relationship
FROM References
WHERE Applicant_ID = 1021

It is a simple SELECT statement - no joins, no subqueries. Easy, right? And yet, ExecuteSQL returned the dreaded "?" result, indicating that there was something wrong with the SQL statement.

After double and triple-checking the field names, and confirming that the table name really was spelled correctly, I narrowed the problem down to the table name itself. FileMaker simply didn't like "References" as a table name.

And then it dawned on me: "References" is one of FileMaker's SQL reserved keywords. And sure enough, after wrapping the table name in quotes, the query worked:

SELECT Reference_ID, Reference_Name, Relationship
FROM "References"
WHERE Applicant_ID = 1021

I've been burned by reserved keywords before, but usually with field names. "References," to me anyway, wasn't an obvious reserved keyword. In the future, when a SELECT statement that should be working isn't, I'll be sure to look more carefully at the table and field names, to be sure that I'm not using any of the reserved keywords.

If you're looking for a list of FileMaker's Reserved SQL keywords, grab a copy of the FileMaker 12 ODBC and JDBC Guide. There's a list of "Reserved SQL keywords" in Chapter 7 (page 55).