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.
Hi Mislav,
Love this new addition –
It is Clive Sheiles, we worked on a custom function some years back ‘Normal Distance’. Great to see you are still thumping the keys, I am also, although in a limited capacity these last few years.
Great to hear from you, Clive!
Little point in this function as I see it as the regular ExecuteSQL returns the error in the Data Viewer as well as in any calc is you use the sql.debug CF, which I’ve used for a decade.