FileMaker 11 Internal SQL Changes, part 2

Welcome back for a second action-packed episode, as we continue exploring changes to the FileMaker 11 internal SQL parser. We ended part 1 by looking at the INSERT/SELECT construction which, as you may recall, provides the SQL equivalent of a FileMaker “add new records” import from one table to another.

Actually, when you use INSERT/SELECT, if you wish, the source and target tables can be the same table (or, rather, table occurrence), which once in a while can come in handy, and is something you cannot do via a regular FileMaker import. For that matter, you can also use INSERT/SELECT to map a single source field to multiple target fields, which is another thing a regular FileMaker import cannot do… but I digress.

1.  The specific behavior change I want to examine now involves importing records from one table to another table via INSERT/SELECT. In FM 10 and earlier, you could “promiscuously” mix field types: for example, given two tables, customers and visitors, which are identical except the zip field in customers is a text field, whereas in visitors it is a number field, the following works in 10 but not in 11…




In FM 11 each source field must be either a) of the same type (date, text, number, etc.) as its corresponding target field, or b) induced to mimic its corresponding target field via the CAST function, like so:

You can learn more about the CAST function here, or on pages 40-41 of the FileMaker 11 OBDC and JDBC Guide.

2.  Next I need to clear up some confusion from part 1, because I expressed something incorrectly. When I said “Numbers can no longer be quoted”, I should have instead said…

Numeric field data can no longer be quoted.

…because in FM 11 it’s the field type that determines whether or not a string of data is considered a number. For example, in FM 10 and earlier the following works, assuming “sales” is a number field.

But in FM 11 it simply will not work until you remove the single quotes. Conversely, in FM 11, make the target field “zip”, which is (of course) a text field, and then not only can you quote the 99999, you must quote it because text field data must be quoted in FM 11.

It doesn’t matter that you and I would consider 99999 to be a number — as far as the FM 11 SQL parser is concerned it’s just another text string. Not surprisingly, if you try the above example in FM 10 and earlier, you can quote the 99999 or not — either way it will work.

Bottom line: in FM 11 the field type (text or number) determines quotes or no quotes.

3.  What if the field type is date, time or timestamp? These have gotten stricter as well, and the next examples  specifically concern date fields. First off, though, I should mention that SELECT-ing a date has not changed:  in both FM 10 (and earlier) and in FM 11, this query…

SELECT exp_date FROM customers WHERE id = '001769'

…returns a date such as 2011-11-30 (i.e., in the format YYYY-MM-DD). But when it comes to WHERE clauses, this composite view from the FMA 10 and FMA 11 data viewers shows the usual story of FM 11 being stricter than its predecessors.

(In case you’re wondering, “doSQL” is a custom function I wrote to invoke whichever SQL plug-in I happen to be using at the moment, thereby making my SQL calls more portable. See below for the definition.)

Let’s take a closer look at the third statement in the screen shot above, the one with the + signs… you can format a date that way ('YYYY+MM+DD') in a WHERE clause, but will it work to push a date via INSERT or UPDATE? Once again, it depends…

The highlighted expression works in FM 10 and earlier, but in FM 11 you’re going to need to use DATE '2008-01-04' instead, so rather than having to mentally keep track of which date syntax works under what circumstances, I recommend simplifying your life by always using DATE 'YYYY-MM-YY'; the following works perfectly in any version of FileMaker that recognizes SQL plug-ins:




Or if you’re adding a new record…

4.  Here’s a statement that broke in 11, and it took me a while to figure out why…

SELECT _id
FROM customers
WHERE name_last = 'Smith'


What’s going on? It turns out that if a field name begins with (not ends with or contains, just begins with) an underscore, then it needs to be treated as if it were a reserved word, i.e., escaped via double quotes, like so:

SELECT "_id"
FROM customers
WHERE name_last = 'Smith'

5. Finally, here’s a subtle change, because it doesn’t obviously break things. Given a small table with six records…

…this composite view from the FMA 10 and FMA 11 data viewers, shows…

To return the same results in both 10 and 11, use this instead:

SELECT name_last
FROM customers
WHERE flag <> 1 or flag IS NULL

Well, if you’ve made it this far, and have also read part 1 in this series, you now know at least as much as I do about changes to the SQL parser in FM 11. If I were to attempt to characterize those changes in just a few words, I would say, “The SQL parser has become less forgiving — it’s more SQL-like, and less FileMaker-like.” However, this is the world we live in, and I’m grateful to have SQL as a tool in my FileMaker tool kit.

I want to close with some good news for anyone who, like me, must support SQL not only in FM 11, but in various earlier versions of FM as well: if your SQL code is FM 11-compliant, it will be backward-compatible for FM 10 and earlier.

---

Update 11/28: as per a reader request, here is the definition of the “doSQL” custom function: