So, boiled down, what you're looking for is a way to pass a list of values into the
query properly quoted/delimited, right?
Your "ugly" isn't so ugly. You've taken a straight list and "prepped" it for the SQL
function. I do this all the time but I use SQLRunner plugin so the syntax is a little
Question: what if one of the values in your list has a single quote in it : D'Angelo
Your delimiting will fail then. You would have to do a little more robust parsing of
the list: "'" & Substitute ( $theList ; [ "'" ; "''" ] ; [ "¶" ; "', '" ] )...etc.... but of course that
may or may not work depending on the value makeup....
I also keep all my SQL statements as text in a table and abstract the call to SQL in case
that day comes when I change plugins or go native with FMP. Then at startup I load the
SQL text into $$globals and make my call: SQL ( Evaluate ( $$NAME_KEY_OF_SQL_STRING ) ; <my delimiters> )
A text string in the SQL table might look like this :
"SELECT field1, field2 FROM theTABLE WHERE field3=" & epSQLQuote ( $theComparator )
But my rules include that I prep things before sending it off to the SQL function so.....sorry, to finally
get to the point, I don't think it unusual to do some prepping in a calc LET before calling SQL.
For the list you might want to do a quick loop and "epSQLQuote" each list value so as to take care
of the random single quotes.
Set Variable ( $newList ; $newList & epSQLQuote ( GetValue ( $originalList ; $aLoopCounter ) ) & "¶" )
Do the increment thing and exit loop thing......
where epSQLQuote just happens to be the plugin function I use to manage SQL quotes.
I haven't found a "better" way than you suggested. Do be sure to escape any single quotes in your data, as William suggested. A city like Bois D'Arc would break your query.
I have tried some other approaches, though none are necessarily better. One would be to create a recursive custom function that takes a SQL query and a list of values as parameters. Each iteration of the function could pop the top value off the list and execute the SQL statement with a single argument. The end result would be a combination of the results from each query.
Another approach would be to build a semicolon separated set of values and a corresponding comma separated set of question marks. Then calculate the entire ExecuteSQL function as a string and pass it to an Evaluate function. The quoting can be a little tricky, but this approach will work with up to 997 arguments. Here's an example:
$placeholders = "?, ?, ?";
$values = "1; 2; 3";
$query = "select count(*) from sqltest where rec IN (" & $placeholders & ")";
$ex = "executesql ( \"" & $query & "\";\"\"; \"\"; " & $values & ")"
I'd love to have a better way, but at least we have a few options.
Thanks, William and Greg.
I guess I've just been spoiled over the years by the open source tool we use for data transforms, which seemingly magically "knows" what to do when a parameter is being used as part of an "in" statement, and apparently formats it correctly somewhere in the background. It's not a FileMaker tool per-se (we use it to move data from SQL to Filemaker), but as long as we just hand it a comma-delimited list of strings, it apparently takes care of the rest, because it's never failed.
It just seems like we're doing an awful lot of the SQL engine's work, having to "prep" the data for the SQL call. Given that most developers who are not currently versed in SQL are going to likely be expecting functionality to be equivalent for these kinds of lists, I just think there's room for improvement in FileMaker's handling of the passed parameter behind the scenes. I suspect as we all move further into ExecuteSQL, we'll find more places that we will hope for improvement, as well (UPDATE and DELETE, anyone?).
You both make an excellent point about escaping for single-quotes. The examples I gave here are generalized from code we're using to get at data over which we have very good control, so escaping single-quotes hadn't even dawned on me. We're actually trying to get at distinct subsets of data across multiple selected data sets, so having ExecuteSQL in combination with a checkbox list available to us is a huge step forward, no matter how much massage we have to do to get there.
Glad to have been of help.
I agree that over time the SQL functionality will most likely (hopefully) expand.
As mentioned I use a 3rd party SQL plugin and may or may not switch to the
built in function; it will depend upon benefit vs cost of switching, if I need to make
serious syntax changes to my SQL table.
Right now I limit myself to SELECT. I ran into some problems that I understand to
have been table locks when an UPDATE or DELETE is issued..... FMP just sits
there waiting I guess but never seems to recover. This is something that will have
to be handled by the FileMaker crew before expanding.
A question I have at this point is: are INSERTS liable to the same problems that
UPDATE AND DELETE have? INSERT is also very handy in the FileMaker setting.
I haven't had time to seriously test this yet.
We tend to shy away from plug-ins because of the size of our deployment (200+ seats) and the possibility that any of our solutions could one day be accessed from an iPad without modification. We are also multi-platform, which makes plugins even more (less) fun.
As far as I know (longtime user, never been certified), INSERT becomes an issue not due to record locking but because of the Manage Database process. We do a lot of writing to FileMaker via JDBC, and the process we use there can get snagged if someone is working in the Manage Database window for a database we're trying to write to. Forutnately, there are only two of us who ever go in there, so it's generally pretty easy to identify the problem when it comes up.
We occasionally experience issues with record locking on update and delete from the JDBC side, but for the most part, between data separation and preventing fields from being modified in Browse mode, record locking contentions are few and far between for us.
How does the separation model in and of itself prevent these problems?
Or do you mean that by using the globals method and a "Save" button
record locking is always at a minimum because it's under program control.....
there's never a user sitting with the cursor in a field playing a video game on the side...
I guess it's not the separation model that's making our lives easier. It's more the fact that most of the data we're updating or deleting is one-way data - for reporting only. So, when we place that data on the layout, all of the information is being denied Field Entry in Browse mode, and therefore never gets locked out.
It's a balance, for sure - but I've been in meetings where the 1:00 data update hits, and the data changes on the screen right before our eyes! Generally when that's happening, we're looking at related data while we're either sitting on an anchor record, or through a portal. Either way, all records coming from the other table are not allowing field entry or in Merge Fields.
Hope that makes sense. We're a bit into the weeds. I'd be happy to show you our solutions if you're going to DevCon. Our group will be easy to recognize with our Hancock Lumber shirts on!
I will keep an eye out; will be interesting to discuss the issues.
Did you try the Quote() function?
$cityList= Quote( Substitute ( chkListField; ¶; "','") );