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.
Here’s the first half of the “Gather Last 100 Non-Weekend Days” script:
At this point the “argument” for the IN function has been assembled as a SQL-friendly comma-delimited $DateList variable, like so:
And here’s the remainder of the script.
Nice example! But I’m not sure why the escaping is necessary.
This statement seems to work fine:
ExecuteSQL( $innerSQL; “”; “”)
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; “”; “”) ]
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.
Thanks Bruce and Dan for pointing out that the technique could be streamlined — the article and demo have been revised accordingly.
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.
> 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
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.
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.
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 & “‘)”]
That’s a convincing example.
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.
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.
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)
]
Thanks John and all the others who contributed to this. Works like a dream for me, easily adaptable,
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
)
Try this:
Let ( [
$name = Get ( AccountName ) ;
$myquery = “SELECT FullName FROM a_plants_A_EMPLOYEES Where FullName = ?” ;
$myresult = ExecuteSQL ( $myquery ; “” ; “” ; $name )
];
$myresult
)
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
)
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 …. ????
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
)
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)
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 :-)