FileMaker 21.1’s New SQL Function: ExecuteSQLe

FileMaker 21.1 introduces a new SQL function: ExecuteSQLe. It’s almost identical to the extant ExecuteSQL function, but it returns additional information when an error occurs.

To see how it works, let’s first look at a valid SQL query:

Let ( [ 
	sql = "SELECT FirstName, LastName FROM Contacts" ; 
	result = ExecuteSQLe ( sql ; "" ; "" ) 
] ; 
	List ( 
		"# VALID SQL" ; 
		sql ; 
		result ; 
	)
)

# VALID SQL
SELECT FirstName, LastName FROM Contacts
John,Smith

Now let’s look at an invalid SQL query using the old ExecuteSQL function:

Let ( [ 
	sql = "SELECT this_field_does_not_exist, LastName FROM Contacts" ; 
	result = ExecuteSQL ( sql ; "" ; "" ) 
] ; 
	List ( 
		"# ERROR (OLD FUNCTION)" ; 
		sql ; 
		result ; 
	)
)

The ExecuteSQL result is a question mark. It doesn’t give information about the error; it just lets you know that an error occurred.

# ERROR (OLD FUNCTION)
SELECT this_field_does_not_exist, LastName FROM Contacts
?

Finally, let’s look at the same invalid SQL query using the new ExecuteSQLe function:

Let ( [ 
	sql = "SELECT this_field_does_not_exist, LastName FROM Contacts" ; 
	result = ExecuteSQLe ( sql ; "" ; "" ) 
] ; 
	List ( 
		"# ERROR (NEW FUNCTION)" ; 
		sql ; 
		result ; 
	)
)

The result now tells us that an error occurred (because it begins with “? ERROR”) and the cause of the error (“column does not exist”).

# ERROR (NEW FUNCTION)
SELECT this_field_does_not_exist, LastName FROM Contacts
? ERROR: FQL0007/(1:7): The column named "this_field_does_not_exist" does not exist in any table in the column reference's scope.

Backward Compatibility

Why didn’t Claris simply update the existing ExecuteSQL function? Because of backward compatibility. There is likely to be a lot of code out there that relies on the result of an invalid SQL query to be “?”. If the ExecuteSQL function all of a sudden started returning “? ERROR…”, that code would break. With the new function, developers are given control over how and when to switch from the old to the new.