Archive | May, 2012

FM 12 ExecuteSQL: Robust Coding, part 2

Posted on May 21, 2012 by

In part 1, I listed six scenarios that could potentially cause ExecuteSQL code to break:

…and managed to get through the first five before running out of steam. This time, we’re going to examine #6 on the list, and then look at a few miscellaneous odds ’n’ ends, and today’s demo file is ExecuteSQL Sandbox, v2, if you’d like to follow along.

Changing a Field Type

Why would changing a field’s type cause a problem? Well, first off, as I mentioned last month, if you are using that particular field as a predicate in a JOIN, the query will break, and your reward will be the the dreaded “?”.

I don’t have a fix for this, just some advice: Don’t change a field’s type if you’ve used it as a JOIN predicate in a working SQL statement. (The only reason I’ve needed to change a field type recently was to fix a broken JOIN where the field types were mismatched.)

Continue Reading →

FM 12 ExecuteSQL: Robust Coding, part 1

Posted on May 13, 2012 by

[Note: Some of the material in this article, and in today's demo file ExecuteSQL Sandbox, previously appeared in my March 2011 article, Custom Functions for Internal SQL.]

The other day a colleague remarked, “You know, it’s going to be interesting to see if you SQL guys are still so excited about ExecuteSQL when something gets renamed and your code breaks.” He had a good point: code is “brittle” if it works initially, but then subsequently breaks as a result of a seemingly innocuous action.

Here are some things that can cause ExecuteSQL code to break:

  1. Renaming a table occurrence (TO)
  2. Renaming a field
  3. Using a “reserved word” as a field or TO name
  4. Having a problematic character in a field or TO name
  5. Executing the code in a foreign country (!)
  6. Changing a field’s type (e.g., from text to number or vice versa)

…and today we’re going to look at some defensive measures we can employ to prevent problems when these things occur.

Using the “orders” table in the demo file…

Continue Reading →

FM 12 ExecuteSQL: Dynamic Parameters, pt 2

Posted on May 8, 2012 by

This is a quick follow-up to part 1, with a couple more observations about dynamic parameters.

Embedded Apostrophes

Here’s one I can’t believe I forgot to mention the other day:  A major ExecuteSQL headache that dynamic parameters can alleviate is the dreaded “embedded apostrophe” problem. In case you aren’t familiar with it, if your text string contains an embedded apostrophe, in standard SQL you must escape it by prepending another apostrophe, for example compare these two “standard” (non-dynamic) queries:

As you might expect, you don’t have to worry about this if you instead use a dynamic parameter… just quote the search term the way you would any FileMaker text string (i.e., in double quotes) and go about your business.

Continue Reading →

FM 12 ExecuteSQL: Dynamic Parameters, pt 1

Posted on May 2, 2012 by

I have a confession: when I first read about dynamic parameters in the Help entry for ExecuteSQL, my initial reaction was, “Why do they have to make it so darn complicated?” 

I have another confession: I am now singing a very different tune. I’ll get to the reason for this in a minute, but first let’s compare two ExecuteSQL statements, which were discussed last month in FM 12 ExecuteSQL, part 1.

Continue Reading →