Best Practices for hardening field names in ExecuteSQL

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.

ExecuteSQL (
"SELECT " & GFN ( Customer::firstName )
& " FROM " & GTN ( Customer::id )
& " WHERE " & GFN ( Customer::lastName ) & " = ?" ;

"" ; "" ; "Mark"

)

And I think it's best to apply the same principle to complex queries too, just to keep it consistent and predictable.

8 Likes

The following is part of the Typinator set that I got from Matt Petrowsky or filemakerstandards.org:

Let ( [ ~sql = "
	SELECT t1.~field
	FROM ~table1 t1
	JOIN ~table2 t2
	ON t1.~field = t2.~field
	WHERE ~field LIKE '%~value%'
	AND ~field=?
	ORDER BY ~field";

	$sqlQuery = Substitute ( ~sql ;
		[ "~table1" ; SQLTableName ( Table1::fieldName ) ];
		[ "~table2" ; SQLTableName ( Table2::fieldName ) ];
		[ "~field" ; SQLFieldName ( Table1::fieldName ) ];
		[ "~value" ; Table::field ]
	);

	$sqlResult = ExecuteSQL ( $sqlQuery ; "" ; "" ;
    	$value;
    	$value[2];
    	$value[$n]
	)
];
	//Substitute ( $sqlQuery ; "	" ; "" ) &¶& // sql preview
	If ( $sqlResult = "?" ;
		Let ( ~debug = False ; If ( ~debug ; SQLDebugResult ( $sqlResult ) ; False ) );
		$sqlResult
	)
)

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.

2 Likes

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.:

Let ( [

_json = "{}"

/*** Colors ***/
; _table = JSONSetElement ( "{}" 
; ["table" ; Quote ( GetTableSQL ( GetFieldName ( Colors::UUID ) ) ) ; JSONString]
; ["UUID" ; Quote ( GetFieldSQL ( GetFieldName ( Colors::UUID ) ) ) ; JSONString]
; ["Name" ; Quote ( GetFieldSQL ( GetFieldName ( Colors::Name ) ) ) ; JSONString]
; ["Type" ; Quote ( GetFieldSQL ( GetFieldName ( Colors::Type ) ) ) ; JSONString]
)
; _json = JSONSetElement ( _json ; "Colors" ; _table ; JSONObject )

/*** Resources ***/
; _table = JSONSetElement ( "{}" 
; ["table" ; Quote ( GetTableSQL ( GetFieldName ( Resources::UUID ) ) ) ; JSONString]
; ["UUID" ; Quote ( GetFieldSQL ( GetFieldName ( Resources::UUID ) ) ) ; JSONString]
; ["ResName" ; Quote ( GetFieldSQL ( GetFieldName ( Resources::ResName ) ) ) ; JSONString]
; ["ResnameShort" ; Quote ( GetFieldSQL ( GetFieldName ( Resources::ResnameShort ) ) ) ; JSONString]
)
; _json = JSONSetElement ( _json ; "Resources" ; _table ; JSONObject )

] ;

_json

)

and the i.e. fill my virtual list for the value list like this:

Let ( [

$$tempArray[6] = Let ( _query = Substitute ( "SELECT _UUID, _Name FROM _Colors WHERE _Type=?"
; ["_Colors" ; JSONGetElement ( $$queryJson ; "Colors.table" )]
; ["_UUID" ; JSONGetElement ( $$queryJson ; "Colors.UUID" )]
; ["_Name" ; JSONGetElement ( $$queryJson ; "Colors.Name" )]
; ["_Type" ; JSONGetElement ( $$queryJson ; "Colors.Type" )]
) ; ExecuteSQL ( _query ; "||" ; "" ; "Event" )
)

] ;

0

)

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?

Also, what about this part?

Quote ( GetTableSQL ( GetFieldName ( Resources::UUID ) ) )

Should everyone always use "GetFieldName"? If not couldn't that just all get wrapped into one custom function instead of 3 calls?

JSGetFieldName ( Resources::UUID )

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:

// scroll to see whole calc
Let ( [
	$sqlQuery =
		"SELECT "
			& SQLTableAndField ( Person::id ) & ", "
			& SQLTableAndField ( Person::name ) & ", "
			& SQLTableAndField ( Person::dob ) & ", "
			& SQLTableAndField ( Company::name )
		& "¶FROM "
			& SQLTable ( Person::id )
		& "¶LEFT JOIN "
			& SQLTable ( Company::id )
		& "¶ON "
			& SQLTableAndField ( Person::id_Company )
			& " = "
			& SQLTableAndField ( Company::id )
		& "¶WHERE "
			& SQLTableAndField ( Company::name )
			& " = ? "
		& "¶ORDER BY "
			& SQLTableAndField ( Company::name )
			& ", " & SQLTableAndField ( Person::name ) & " DESC"
		& "¶FETCH FIRST 10 ROWS ONLY"
	;
	$sqlParameter1 = "ACME Widgets" ;
	$sqlResult = ExecuteSQL (
		$sqlQuery ;
		SQLFieldSeparator ; // Custom Function
		SQLRowSeparator ; // Custom Function
		$sqlParameter1
	) ;
	$error = If ( $sqlResult = "?" ;
		ErrorApp ( 17 ; "" ) // Custom Function. This will make logging and debugging failed sql queries much easier
			& # ( "sqlQuery" ; $sqlQuery ) 
			& # ( "sqlParameter1" ; $sqlParameter1 ) ;
		$error
	)
] ;
	$sqlResult
)

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.

3 Likes

Just delete the lines you don't need or add what you need.

3 Likes

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.

3 Likes

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.

2 Likes

This is also my favorite, because the SQL code is native.

1 Like

I have a text snippet in Alfred (also experimenting with Raycast currently) where if I type "fmSQL" it types this for me:

Let ( [
	¢Table = SQLTable ( Table::Field ) ;
	¢Field1 = SQLField ( Table::Field ) ; 
	¢Field2 = SQLField ( Table::Field ) ; 
	¢SQL = 
		"SELECT " & ¢Field1 &
		" FROM " & ¢Table & 
		" WHERE " & ¢Field2 & " = ?" 
	] ; 
	ExecuteSQL ( 
		¢SQL ; 
		"" ; "" ; 1 
	)
 )

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.

1 Like