FM 12 ExecuteSQL: Dynamic Parameters, pt 2

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.

“IN” Operators

I’ve been evangelizing dynamic parameters as if they are always superior, but Stephen Dolenski recently pointed out on Friday Night Chat that when it comes to the “IN” operator, the dynamic approach may end up being more restrictive than the standard one.

As you may know, the IN operator provides a way to avoid long chains of “or” tests. For example, instead of this…

ExecuteSQL(
" SELECT MAX ( sales )
FROM customers
WHERE state = 'WA' or state = 'OR' or state = 'ID'  "
; "" ; ""
)

…if you use the IN operator, the query can be streamlined, thus…

ExecuteSQL(
" SELECT MAX ( sales )
FROM customers
WHERE state IN ('WA','OR','ID')  "
; "" ; ""
)

…and with dynamic parameters, it looks like this:

ExecuteSQL(
" SELECT MAX ( sales )
FROM customers
WHERE state IN ( ? , ? , ? )  "
; "" ; "" ; "WA" ; "OR" ; "ID"
)

When you just have a few choices, it’s not a big deal to go the dynamic route, but what if you are constructing the IN arguments “on the fly”, and you aren’t sure how many arguments there will be? I’ll come back to that in just a sec, but first I want to introduce a custom function that transforms a return-delimited list into an IN-friendly comma-separated list.

Based on what you saw in part 1, where dynamic parameters could do no wrong, you might think that if you had a value list, Northwest, consisting of WA¶OR¶ID, that this would work…

Let (
x = FormatListForIn ( ValueListItems ( Get ( FileName ) ; "Northwest" ) ) ;
ExecuteSQL (
" SELECT MAX ( sales )
FROM customers
WHERE state IN ( ? )  "
; "" ; "" ; x )
)   //   end let

…but it does not. With dynamic parameters, each item must be enumerated separately — thank you Ralph Lilienkamp for helping me understand this — so you can’t use the result of the custom function ('WA','OR','ID') as the corresponding argument for a single “?”. Meanwhile, this non-dynamic construction purrs along like a contented kitten:

Or course this is a simple use-case, and your mileage may vary, but I thought it was worth sharing, and I want to close by thanking Stephen Dolenski for bringing it up, and also Ernest Koe for helping me clarify my thinking about this.

 

5 thoughts on “FM 12 ExecuteSQL: Dynamic Parameters, pt 2

  1. Eran Kendler

    Thank for wonderful info – I’m having fun and tearing hair out using this. Wondering if you know the answer to this :

    While above works great for text fields, when trying to using the IN statement on a number field e.g.
    WHERE ClientID IN … it doesn’t seem to work, while it does with text field e.g.
    WHERE ClientCity IN…

    Thanks

    Reply
    1. Kevin Frank Post author

      Hi Eran,

      My next blog posting will cover this in more detail… if the underlying field type is numeric, then the CF you’re using to assemble the IN string needs to NOT wrap the values in single quotes.

      I’ll email you a beta of the demo file for my next article and perhaps that will help (but the final one will be better!)

      Regards,
      Kevin

      Reply
  2. john renfrew

    Kevin,
    Thanks for all the legwork on this, really giving a flying start to the rest of us.

    Would you like to do something on the difference between = and LIKE at some point…?

    Reply
    1. Kevin Frank Post author

      I appreciate your comment John. LIKE strikes me as a fairly straight-forward operator… % for multiple characters, _ for one character. Works well as a dynamic parameter… did you have anything particular in mind?

      The W3 Schools as usual have some helpful information, if you filter out the portion that doesn’t apply to FM’s SQL implementation…

      http://www.w3schools.com/sql/sql_like.asp

      http://www.w3schools.com/sql/sql_wildcards.asp

      …for example, ignore the part about [charlist] on the wildcard page. Or if you figure out a way to get it to work, please let me know.

      Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s