March 9, 2010 is a date I recall quite clearly: not only was it the release date for FileMaker 11; it was also the day a bunch of my FileMaker SQL code broke, due to changes in the FM 11 internal SQL parser.
If you’re new to this subject, or perhaps a bit rusty, I have written about internal SQL (a.k.a. FQL) on various occasions over the last year, and there are a number of important points that I won’t discuss today, because they have already been mentioned in one or more of these articles:
- Filtered Relations, part 3
- FileMaker’s Internal SQL Engine, part 1
- FileMaker’s Internal SQL Engine, part 2
- Custom Functions for Internal SQL
What follows is by no means exhaustive, but merely what I’ve documented. There is no comprehensive source of information about FQL, although chapter 7 of the FileMaker 11 OBDC and JDBC Guide is a good place to start (just remember that not everything you read there will apply to FQL).
Also, at the outset I should mention that if you’re going to work with FQL in FM 11, make sure to use a plug-in that is 11-compatible. If you use an outdated (pre-11) plug-in, many of your queries will work just fine, but sooner or later something won’t (e.g., see #4 below).
Okay, let’s take a look at some changes to the FM 11 SQL parser…
1. The Reserved Word list has changed significantly. The total number of words on that list has dropped from 320 to 258, with 82 terms removed and 21 terms added. Click the image at the right to see the full list for both 10 and 11, with differences in red.
2. Numbers can no longer be quoted, although this practice was optionally allowed in FM 10 and earlier as per this screen shot…
If you try to quote a number in 11, you will get a ? for your trouble
Note: I am using the doSQL plug-in in these examples, but the SQL code would be the same, regardless of which SQL plug-in I chose to use.
3. Here’s one I blundered into purely by accident: The FM 11 parser doesn’t tolerate a trailing comma at the end of a SET clause (I never intended for one to be there in the first place, but somehow there it was, and FM 10 didn’t complain).
4a. INSERT/SELECT is one of the things that “breaks” if you use an outdated plug-in with FM 11. Normally INSERT creates one record, but INSERT/SELECT functions much like a standard import in FileMaker — it creates records in a target table based on records in a source table that match specified criteria. E.g.,
INSERT INTO reminders (id_contact, name_first, name_last)
SELECT id, name_first, name_last
FROM contacts
WHERE State = 'NY'
4b. However, even if you use an 11-compliant plug-in, there is one INSERT/SELECT behavior that has changed (and not for the better in my opinion). The same statement with an ORDER BY clause works in FM 10 and earlier, and simply is not allowed in FM 11.
INSERT INTO reminders (id_contact, name_first, name_last)
SELECT id, name_first, name_last
FROM contacts
WHERE State = 'NY'
ORDER BY name_last, name_first
This is a shame, because sorting the source data before importing is a very reasonable thing to want to do, but there you have it.
In part 2, we’ll examine some more changes to FileMaker’s internal SQL parser. In the mean time, if you have any you’d like to discuss, I invite you to post comments here.
Good stuff Kevin…as always.
Here are a couple of little changes I’ve noticed:
FM 10 and earlier supported end-of-line comments using “–“. This no longer works in FM 11:
SELECT * FROM contacts — end-of-line comments don’t work in FM 11
In FM 10, a FROM clause was optional for SELECT statements:
SELECT 2 + 2 /* doesn’t work in FM 11 */
Thanks Greg, I wasn’t aware of either of these.