In this post, find out the two ways in which you can go about using the ExecuteSQL function in FileMaker.

The ‘ExecuteSQL’ command is used in order to execute an SQL query statement for the occurrence of the specified table in a FileMaker Pro file. The ‘ExecuteSQL()‘ function first came into play in FileMaker Pro 12, released on 4th April 2012.

We aren’t going to talk about whether or not its useful (perhaps another time). For now, we will teach you the two ways to Execute SQL.

How to Execute SQL in FileMaker

Structured Query Language (SQL), pronounced “sequel”, is a type of programming language. The ‘ExecuteSQL‘ function can be used in FileMaker in the following ways:

I – Logical Function
II – Script Step

We will now explain both of them in more detail. 

1. Logical Function -> Execute SQL

The first is the logical function. Here are some points to be aware of.

• The logical function Executes an SQL Query statement (SELECT Query) for the specified table in a FileMaker database.
• It does not recognize the FileMaker relationship. You need to define the relationship in SQL query.
• If an error occurs at the time of execution, FileMaker returns a question mark (?).
• You should apply correct format of dates in SQL Query. You can use DATE Statement, or else Execute SQL treats dates as literal string.
• It does not support ODBC/JDBC date, time, and timestamp constants in braces.

Format

ExecuteSQL(sqlQuery; fieldSeparator; rowSeparator {;arguments...})

Parameters

Here is what each of those parameters mean.

• sqlQuery – The statement can include a ‘Union’ clause that combines the results of two queries. The statement can contain programmatically generated SQL (dynamic parameters) that indicate where optional arguments are to be used in the query. Use the question mark character (‘?’) to specify a dynamic parameter.
• fieldSeparator – The character string used as a separator between fields in the result. If an empty string is specified, the separator is a comma.
• rowSeparator – The character string used as a separator between records in the result. If an empty string is specified, the separator is a carriage return.
• arguments – One or more expressions that are evaluated and used as values for the dynamic parameters in the query statement.

Example

Suppose a database contains two tables, ‘Clients‘ and ‘Invoices‘, which are related through the ‘ClientID‘ field. The resulting table would look something like this:

Extra Notes

• The ‘Client::ClientID‘, ‘Invoice::ClientID‘, and ‘Invoice::TotalDue‘ fields are number fields.
• You can use the ExecuteSQL function to return a field value from a specific record without changing the current record or modifying the found set.
• ExecuteSQL ( “SELECT PhoneNumber FROM Client WHERE ClientID =1”; “”; “”  ) returns 123-456-7890 regardless of the current record, found set, or layout.

2. Script Step -> Execute SQL

Next is script step. Here is what you should keep in mind: 

• You can use this Script Step to manage data in an ODBC data source through an ODBC SQL interface.
• This provides more control over the FileMaker Pro interaction with ODBC data sources.
• You can Execute any SQL Statement on an ODBC data source.
• FileMaker Server, FileMaker WebDirect, and Custom Web Publishing support this script only if the Perform without dialog option is selected.
• If you use the ExecuteSQL script step to send ODBC data that contains Unicode strings, your ODBC Driver must support Unicode. Otherwise, the results may be inconsistent.

Format

Execute SQL [No Dialog; ODBC: <datasource name>; <native SQL or calculated SQL>]

Options

You have two options here:

• Perform without dialog prevents the ‘Specify SQL‘ dialog box, the ‘Select ODBC Data Source‘ dialog box, and the Password dialog box from displaying when the script step executes.
• ODBC Data Source displays the ‘Select ODBC Data Source‘ dialog box. Select a data source from the list, click ‘OK‘, and enter a valid user name and password.

Extra Notes

• Select ‘Save username and password‘ if you selected ‘Perform without dialog‘, or users may be unable to access your data source.
•  Calculated SQL text specifies a calculation to use as the SQL query.
•  SQL text lets you manually enter a SQL query statement.

Those were the two ways you can Execute SQL in FileMaker. We hope this blog post was useful. If you need more assistance, our guys are happy to help. Also, be sure to read our previous post on Fixing the FileMaker Server Admin Issue if you have come across that.