I've seen many different ways to write SQL statements in Filemaker. I tend to just write it by hand and if I have to change a fieldName use FMPerception to find all instances of the field and manually change the script.
I've seen other people who use functions or complex LET statements to manage their SQL, but the feel convoluted and difficult to modify if adding a join or changing the sort order.
How do ya'll do it? What's your favorite balance of ease of programability vs hardening against field name changes?
How would you call this SQL call?
ExecuteSQL ( "SELECT (LASIDClean_a) FROM LearnersImported
WHERE
TestUser_Toggle = 1
"
; "" ; "" )
And would you treat it differently for a more complex one like this with a join and an "ORDER BY":
ExecuteSQL ( "SELECT DISTINCT(LearnersAdditional.LASIDClean) FROM EngagementActions LEFT OUTER JOIN LearnersAdditional ON LearnersAdditional.LASIDClean = EngagementActions.LASIDClean
WHERE
MessageTypeAsText = ?
AND Date_Engagement BETWEEN ? AND ?
AND LearnersAdditional.GLZ = ?
ORDER BY LearnersAdditional.LASIDClean
"
; "" ; "" ; "CaseConference" ; Get(CurrentDate) - 7 ; Get(CurrentDate); "Core")
Best practice is to not hardcode field names, and instead, generate sql queries dynamically, usually by wrapping the field and table names in custom functions that do this for you.
Here's a community thread with some links to GFN (get field name) and GTN (get table name) CFs, though I personally like to name the CFs more explicitly. Claris Community (English)
Your end result may look something like this, and it should automatically tolerate field and table name changes.
The Custom Functions SQLTableName and SQLFieldName get the table name part or the field name part of a full field name.
A great use case for text expansion tools like Typinator.
I'll go for the substitution way too, maintains the query readable. I started to fill a global var $$queryJson with the needed SQL field- and table names i.e.:
I, too, use a let statement every time I use ExecuteSQL. Like @Otmar, I also quote every field and table name. It's not just a matter of hardening against field name changes. It's also a matter of reducing potential name issues in SQL.
I work a lot on solutions worked by others so can't rely on a stable naming convention. I can't afford to balance ease of programming vs safe programming, at least not in this case.
We again use our own custom functions (one of our rare use of custom functions these days). They are named SQLField, SQLTable and SQLDate (to put a FileMaker date into ISO standard format).
Unlike some here we don’t quote the field names as mostly we can control these. However, should we come across a field with spaces or a leading underscore in their name then Quote ( SQLField ( Field Name ) ) works fine as well.
We always use Let () to create the SQL statement which allows the statement to be evaluated, checked/debugged as needed.
This is great. I'm not a developer so forgive me if my questions are naive:
I 100% understand the solution @jwilling proposes. I also understand @mipiano and see the value, although do you have to customize it when there's no join and no order by?
I'm struggling to see the value in what @Otmar does. As a non-coder it feels like a lot of extra code that can break at any given point with a lot of redundant typing, and therefore a lot of potential for errors. That might just because I'm not a coder and make more basic mistakes than others, but in general unless there's a speed issue or a clarity issue I always strive for the least code to make something work, simply because less code = less chance I'll have an error in it.
I imagine if you work in Javascript every day and live in JSON files this could be intuitive, but I'm wondering if a noob like me would get value out of a model like this, and what that value would be?
I do something similar to @mipiano 's code snippet in production code. The example I posted earlier is slimmed down for illustration. Production code would look more like this:
The value in $sqlQuery will look something like this:
SELECT "Person"."id", "Person"."name", "Person"."dob", "Company"."name"
FROM "Person"
LEFT JOIN "Company"
ON "Person"."id_Company" = "Company"."id"
WHERE "Company"."name" = ?
ORDER BY "Company"."name", "Person"."name" DESC
FETCH FIRST 10 ROWS ONLY
The main point here is that all field and table names should be wrapped in a CF that automatically quotes and qualifies them properly. Storing the various values in $local vars just makes logging/debugging easier, but isn't required.
GetFieldName is important. It allows the custom function to get the actual field name dynamically. Any static code reference to a nameable object is going to break - unless your names are equally static.
I'm struggling to see the value in what @Otmar does. As a non-coder it feels like a lot of extra code that can break at any given point with a lot of redundant typing, and therefore a lot of potential for errors.
The idea is to fill this json object once at solution start, so the db-structure hasn't to be queried over and over again. The queries are then just like the 2nd code snippet. If it happens I need another field/table, I just add it to the global json-var and write the query.
The GetFieldName is the main thing for make queries "unbreakable", then we need either the table or the field name, and to make spaces or sql-unfriendly field names working we put them in quotes.
As we can send field references to CFs it should be possible to pack the 3 in 1 CF.
SQLTable and SQLField are CFs that use GFN, select either the TO or the field and quote it all in one. Then I just update the placeholder fields at the top, and the 1 for the optional ? query parameter. I'm not often doing joins, but I'd probably create a new version for that, if needed.