ExecuteSQL: Using IN with Faux Dynamic Variables

Editor’s note: Today we have a guest article written by John Weinshel, whose knowledge of SQL in general, and FileMaker+SQL in particular, runs deep. John’s contributions in various online forums are always worth reading, and it’s a privilege to present his thoughts on this topic here.

The new ExecuteSQL() function in Filemaker 12 does not work dynamically (with the question mark) as expected with the IN function (nor with BETWEEN). For example, we might expect the following statement to return all the ID’s for contacts whose first name is either John, Mary, or Renee:

ExecuteSQL (
"
SELECT c.PKContact
FROM Contacts c
WHERE c.fname IN ( ? )
";
""; ""; " 'John','Mary','Renee' "
)

…but it doesn’t. The following does work, but it’s not dynamic:

ExecuteSQL (
"
SELECT c.PKContact
FROM Contacts c
WHERE c.fname IN ( 'John','Mary','Renee' )
";
""; ""
)

It occurred to me that we instead could:

  • Build the desired group using conventional Filemaker methods (that’s the dynamic part)
  • Pass those results to a variable
  • Hand-write the SELECT statement with that variable inside it, and assign it to another variable
  • Use ExecuteSQL with that variable as the argument to return the expected results. It works.

In today’s demo file, Using IN with faux dynamic variables, v2, the script following this idea first assembles a list of ID’s for invoices from the previous 100 non-weekend dates, passes those ID’s to a global field, and then GTRR’s (goes to related records) to those invoices using a TO from the global to the ID’s.

12-15-2012 1-44-53 PM

Here’s the first half of the “Gather Last 100 Non-Weekend Days” script:

12-15-2012 2-09-56 PM

At this point the “argument” for the IN function has been assembled as a SQL-friendly comma-delimited $DateList variable, like so:

12-15-2012 2-24-17 PM

And here’s the remainder of the script.

12-15-2012 11-50-48 PM

21 thoughts on “ExecuteSQL: Using IN with Faux Dynamic Variables

  1. Bruce Robertson

    Nice example! But I’m not sure why the escaping is necessary.

    This statement seems to work fine:

    ExecuteSQL( $innerSQL; “”; “”)

    Reply
  2. Bruce Robertson

    Set Variable [ $InnerSQL; Value:
    Substitute(
    “SELECT i.n_PKInvoice
    FROM Invoices i
    WHERE i.d_Date IN ( DateList )”
    ; “DateList”; $dateList )
    ]
    Set Variable [ $OrdIDList; Value:ExecuteSQL( $innerSQL; “”; “”) ]

    Reply
  3. Dan Smith

    Interesting timing, as I just ran into the same issue last night! Here is how I formatted the sql statement:

    Set Variable [ $InnerSQL; Value:
    “SELECT i.n_PKInvoice
    FROM Invoices i
    WHERE i.d_Date IN ( ” & $dateList & ” )”
    ]
    Set Variable [ $OrdIDList; Value:ExecuteSQL( $innerSQL; “”; “”) ]

    I don’t understand the advantage of using the method suggested in the article.

    Reply
  4. Kevin Frank Post author

    Thanks Bruce and Dan for pointing out that the technique could be streamlined — the article and demo have been revised accordingly.

    Reply
  5. Koen Van Hulle

    The IN clause expects a list of parameters itself. So if you want to use parameterized SQL queries with an IN condition, you need to exact amount of placeholders (question marks) as values you intend to pass.

    The correct query in your first example should be:

    ExecuteSQL (
    “SELECT c.PKContact FROM Contacts c WHERE c.fname IN ( ? , ? , ? )”;
    “”; “”;
    “John”; “Mary” ; “Renee”
    )

    Making use of parameters in the executeSQL function is not the equivalent of the substitute functions.

    If you want to make use of dynamic parameters in your second example, your script could look like this:

    Set Variable [$i; Value:0]
    Set Variable [$j; Value:0]
    Loop
    Set Variable [$Date; Value:Get ( CurrentDate )- $j]
    If [DayOfWeek ( $Date ) ≠ 7 and DayOfWeek ( $Date ) ≠ 1]
    Set Variable [$dates [$i]; Value:$date]
    Set Variable [$i; Value:$i + 1]
    End If
    Set Variable [$j; Value:$j + 1]
    Exit Loop If [$i ≥ 100]
    End Loop
    #

    Set Variable [$InnerSQL; Value:

    SELECT i.n_PKInvoice
    FROM Invoices i
    WHERE i.d_Date IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
    “]
    Set Variable [$OrdIDList; Value:ExecuteSQL ( $innerSQL ; “” ; “” ;
    $dates[0]; $dates[1] ; $dates[2] ; $dates[3] ; $dates[4] ; $dates[5] ; $dates[6] ; $dates[7] ; $dates[8] ; $dates[9] ;
    $dates[10]; $dates[11] ; $dates[12] ; $dates[13] ; $dates[14] ; $dates[15] ; $dates[16] ; $dates[17] ; $dates[18] ; $dates[19] ;
    $dates[20]; $dates[21] ; $dates[22] ; $dates[23] ; $dates[24] ; $dates[25] ; $dates[26] ; $dates[27] ; $dates[28] ; $dates[29] ;
    $dates[30]; $dates[31] ; $dates[32] ; $dates[33] ; $dates[34] ; $dates[35] ; $dates[36] ; $dates[37] ; $dates[38] ; $dates[39] ;
    $dates[40]; $dates[41] ; $dates[42] ; $dates[43] ; $dates[44] ; $dates[45] ; $dates[46] ; $dates[47] ; $dates[48] ; $dates[49] ;
    $dates[50]; $dates[51] ; $dates[52] ; $dates[53] ; $dates[54] ; $dates[55] ; $dates[56] ; $dates[57] ; $dates[58] ; $dates[59] ;
    $dates[60]; $dates[61] ; $dates[62] ; $dates[63] ; $dates[64] ; $dates[65] ; $dates[66] ; $dates[67] ; $dates[68] ; $dates[69] ;
    $dates[70]; $dates[71] ; $dates[72] ; $dates[73] ; $dates[74] ; $dates[75] ; $dates[76] ; $dates[77] ; $dates[78] ; $dates[79] ;
    $dates[80]; $dates[81] ; $dates[82] ; $dates[83] ; $dates[84] ; $dates[85] ; $dates[86] ; $dates[87] ; $dates[88] ; $dates[89] ;
    $dates[90]; $dates[91] ; $dates[92] ; $dates[93] ; $dates[94] ; $dates[95] ; $dates[96] ; $dates[97] ; $dates[98] ; $dates[99]
    )]

    The advantage is that you don’t have to convert your dates to an SQL-format. FileMaker will do that for you. The disadvantage is the you cannot add dynamically the number of arguments you need.

    Reply
    1. Kevin Frank Post author

      > The correct query in your first example should be…

      Hi Koen,

      I don’t presume to speak for John, but it seems to me that while you’re technically correct, the point of the first example was to show a “broken” hypothetical construction… something that a number of FM/SQL developers *wish* were valid.

      I appreciate you taking the time to share a possible alternate approach to the problem, which as you point out, eliminates the need to cast the date into a SQL-friendly format.

      Regards,
      Kevin

      Reply
  6. Bruce Robertson

    Thanks Kevin, for the update.

    I’d like to comment on comparing Dan Smith’s method (concatenated IN parameter using script variable) vs my suggested method (substitution).

    Both methods work. I happen to prefer the substitute-for-placeholder method because it simplifies writing the expression. You can’t make an error when writing the concatenation because there isn’t any concatenation to write.

    I generally prefer this when doing applescript work as well, especially when combining applescript and do shell script statements.

    In Dan’s case, you must properly set up the concatenation when writing the SQL expression. Not a big deal, especially in this case, where is the concatenation is simple.

    In other cases, there may be multiple places where a variable is being interjected.

    Reply
    1. Kevin Frank Post author

      Hey Bruce, thanks for the clarification. There are so many tradeoffs when it comes to transparency vs. robustness/elegance/whatever… I can certainly see the appeal of your approach.

      Reply
  7. Bruce Robertson

    For instance compare:
    Set Variable[ $expression ; Substitute( “blah blah blah (‘replaceMe’)”; “replaceMe”; $someVar)]

    vs being very very careful with single quotes in this expression:

    Set Variable[ $expression ; “blah blah blah (‘” & $someVar & “‘)”]

    Reply
    1. Beverly Voth Post author

      Yes! the Substitute is a nice touch. Thanks Bruce. Using the … single-quote, double-quote, & something & double-quote, single-quote … , it is hard to tell where the singles and doubles really are without a huge zoom level. LOL

      “Building” the query is often necessary as in John’s example and queries I’ve worked on. It’s not always possible to know how many “?” are needed, as Koen points out. So I just build the query with the “dynamic-but-not-parameter” method.

      There was a request on one of the lists to make a query based on which “find” fields had data. I can easily do this with web applications as you can place “if” statements around the SELECT lists of expressions and the same around the parameters. The SQL query builds itself on-the-fly and has the added security to prevent injection attacks. I suppose you can “test” the find values for ExecuteSQL, too, if desired.

      Here’s the code that could have used Bruce’s idea to make it easier to read:

      Let (

      [ $query = ”
      SELECT A.AddressID
      FROM AddressSurname A
      WHERE A.AddressID > 0 ”
      ; $s = If ( IsEmpty ( Enquiries::Surname ) ; “” ; ” AND UPPER(A.Surname) LIKE ‘” & Upper(Enquiries::Surname) & “%’ ” )
      ; $f = If ( IsEmpty ( Enquiries::Firstname ) ; “” ; ” AND UPPER(A.Firstname) LIKE ‘” & Upper(Enquiries::Firstname) & “%’ ” )
      // repeat for each field in your search form
      ; $result = $query & $s & $f
      ]; ExecuteSQL ( $result ; “” ; “” )
      )

      This takes values from global fields on a form and “builds” the query if a field is not empty. the results were then used to filter a portal (based on the record IDs “found”). you can see that if NO find criteria are used, the portal should show ALL related records.

      Reply
  8. Jason Young

    What do you think about wrapping the whole thing in Evaluate so you can dynamically build your argument string? I don’t have a problem dynamically building the “?,?,?” string in the statement, it’s the argument part of the function that’s the issue with IN. Evaluate gets you around this.

    Reply
    1. John Weinshel

      Hi Jason,

      I actually had originally used Evaluate. I ought to have picked a better sample test, as the issue of SQL date formats is a distraction from the issue at hand. I had used a CF re-format the dates into the cs_SQLDate calc. Quoting was another distraction.

      The heart of the original posting:

      Set Variable
      [

      $InnerSQL;

      SELECT i.n_FKInvoice
      FROM Invoices i
      WHERE i.cs_SQLDate IN(” & $DateList & “)”
      ]

      Set Variable
      [
      $SQLStatement;

      ExecuteSQL
      (” & “”” & $InnerSQL &
      “”” &
      “;” &
      “””” & “;” & “””” & “)”
      ]

      Set Variable
      [
      $OrdIDList;
      Evaluate($SQLStatement)
      ]

      Reply
  9. Larry Schunk

    I am wondering if anyone can help me with what I think is simple, but cannot figure out. In this Let statement for an ExecuteSQL I am trying to pass in the AccountName, I am sure the problem has to do with the AccountName having a space in it Like John Smith I need to get the proper quotes or whatever around $name, but having tried everything I can think of no joy.

    Let (

    [ $name = Get ( AccountName ) ;
    $myquery = “SELECT FullName FROM a_plants_A_EMPLOYEES Where FullName = $name ”

    ; $myresult = ExecuteSQL ( $myquery
    ; “” ; “” )
    ]; $myresult
    )

    Reply
    1. Kevin Frank Post author

      Try this:

      Let ( [
      $name = Get ( AccountName ) ;
      $myquery = “SELECT FullName FROM a_plants_A_EMPLOYEES Where FullName = ?” ;
      $myresult = ExecuteSQL ( $myquery ; “” ; “” ; $name )
      ];
      $myresult
      )

      Reply
  10. Jason Young

    And since we’re case sensitive in the WHERE maybe this:

    Let ( [
    $name = Get ( AccountName ) ;
    $myquery = “SELECT FullName FROM a_plants_A_EMPLOYEES Where UPPER ( FullName ) = ?” ;
    $myresult = ExecuteSQL ( $myquery ; “” ; “” ; UPPER ( $name ) )
    ];
    $myresult
    )

    Reply
  11. Larry Schunk

    I did try that and it returns nothing, but there should be a result because

    Let ( [

    $myquery = “SELECT FullName FROM a_plants_A_EMPLOYEES Where FullName = ‘John Smith” ” ;
    $myresult = ExecuteSQL ( $myquery ; “” ; “” )
    ];
    $myresult
    )

    Returns John Smith …. ????

    Reply
    1. Larry Schunk

      Here is what worked thanks to Jason Young:

      Let ( [
      $name = Get ( AccountName );
      $myquery = “SELECT FullName FROM a_plants_A_EMPLOYEES Where UPPER ( FullName ) = ? ” ;
      $myresult = ExecuteSQL ( $myquery ; “” ; “” ; UPPER ($name) )
      ];
      $myresult
      )

      Reply
  12. Barbara Cooney

    I’m so late to the game on ExecuteSQL it’s embarrassing. However, I wanted to say that this post helped me tremendously, so thanks to all.

    One thing that I learned is how to convert a List ( ) to a $var that can be used as a paramenter as you do above. Your example builds the $var in a loop. I ended up with this:

    “‘” & Substitute ( TO::myIDs ; ¶; “‘, ‘” ) & “‘” // that’s ” ‘ ” all smushed together

    where myIDs = List ( relationship::id)

    Reply
  13. Tom Theriault

    Hi Beverly;

    Not sure how to find the right place to post so I just clicked “SQL” then a “Header” topic.

    Sorry,

    Real quick, can I color the SQL select output list in the “Field” …

    ExYX < Red
    EBSE < Green
    EXVV < Blue
    ETXG < whatever

    Is this possible ??

    Thank you.

    Tom :-)

    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