ExecuteSQL

Executes an SQL query statement for the specified table occurrence in a FileMaker Pro file.

Format 

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

Parameters 

sqlQuery - an SQL SELECT statement. 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. The field separator is not displayed after the last field in the result.

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. The row separator is not displayed after the last row in the result.

arguments - one or more expressions separated by semicolons (;) that are evaluated and used as values for the dynamic parameters in the query statement.

Data type returned 

text

Originated in version 

12.0

Description 

ExecuteSQL enables you to execute SQL SELECT statements containing dynamic parameters to safely query FileMaker Pro databases in order to avoid security vulnerabilities through injection attacks.

ExecuteSQL does not recognize relationships created in FileMaker Pro, which gives you flexibility to define relationships in SQL statements and retrieve data from any table, independent of the layout context.

ExecuteSQL cannot be used with SQL statements that modify data or the database schema (such as the Insert Into or Delete Table commands).

When you use multiple dynamic parameters in sqlQuery, the first question mark character is replaced by the first arguments parameter, the second by the second, and so on.

If an error occurs during query parsing or execution, ExecuteSQL returns ?. To receive a more detailed error message, use the ExecuteSQLe function.

Notes 

  • To apply the correct formatting to dates in an SQL query, use the DATE statement. If you do not use the DATE statement, ExecuteSQL treats dates as literal strings.
  • FileMaker Pro returns date, time, and number data in Unicode/SQL format, not in the locale of the operating system or the file.
  • ExecuteSQL accepts only the SQL-92 syntax ISO date and time formats with no braces. ExecuteSQL does not accept the ODBC/JDBC format date, time, and timestamp constants in braces.
  • FileMaker SQL uses the Unicode binary sort order, which is different from the FileMaker Pro sort order used with language sorting or with the default language-neutral sort order.
  • For more details about SELECT statement syntax, supported SQL statements, expressions, and Catalog functions, see FileMaker ODBC and JDBC Guide and FileMaker SQL Reference.
  • The ExecuteSQL function uses SQL SELECT statements to query tables in FileMaker data sources, not ODBC data sources. To execute SELECT and other supported SQL statements against ODBC data sources, use the Execute SQL script step.

Example 1 

Suppose a database contains two tables, Employees and Salaries, which are related through the EmpID field.

List displaying employees and salaries

Note  The Employees::EmpID, Salaries::EmpID, and Salaries::Salary 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 Department FROM Employees WHERE EmpID = 1"; ""; "" ) returns Development regardless of the current record, found set, or layout.

Example 2 

From Example 1, suppose you want to add a field to the Employees table to display the percentage of an employee's salary relative to the total salaries in a department. Though you could use a calculation in FileMaker Pro to generate this value, you can use the ExecuteSQL function to specify this query using dynamic parameters. The example below uses table aliases for the Employees table (E) and the Salaries table (S) when specifying fields (S.Salary, E.EmpID, and S.EmpID).

Define a calculation field (Percent Dept Salaries) in the Employees table, then use the ExecuteSQL function to specify a query statement in the following calculation:

Copy 
Round ( 
   100 * Salaries::Salary / ExecuteSQL ( 
      "SELECT SUM (S.Salary) 
      FROM Employees E 
      JOIN Salaries S 
      ON E.EmpID = S.EmpID 
      WHERE E.Department = ?"; 
      ""; ""; Employees::Department 
   ) ; 
2 )

Note  Because this calculation needs to get the employee's salary from the Salaries table (Salaries::Salary), you must create a relationship between the Employees::EmpID and Salaries::EmpID fields in the relationships graph (see Creating and changing relationships). In contrast, the ExecuteSQL function doesn't use the relationships graph. So in this example, the JOIN clause establishes the relationship used by the SELECT statement in ExecuteSQL.

For each employee record, the Percent Dept Salaries field calculates the percentage of the employee's salary relative to the sum of the salaries for the employee's department.

Employees
EmpID Last Name Department Percent Dept Salaries
1 Smith Development 52.97
2 Ogawa Development 47.03
3 Durand Quality 48.86
4 Garcia Quality 51.14
5 Mehmet Documentation 100
6 Ferrini Marketing 100

Related topics