ExecuteSQL Function

0

The ExecuteSQL function is a function that originated in FileMaker 12 that allows the user to perform an SQL query statement from within a field or calculation.

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

If an error occurs during query parsing or execution, FileMaker Pro returns ?.

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.

buy xalatan 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 that are evaluated and used as values for the dynamic parameters in the query statement.

Function Return

The ExecuteSQL function returns ‘text’ data.

Where can ExecuteSQL be used?

Some examples of where ExecuteSQL can be used:

  • Calculation fields
  • Auto enter calculation value
  • Validation calculations
  • Script step calculation values
  • Tooltips
  • Conditional formatting
  • Data viewer
  • Custom menus
  • Security
  • Web viewer
  • Charts
  • Portal filters
  • Script parameters

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 the FileMaker ODBC and JDBC Guide and the FileMaker SQL Reference.
FavoriteLoadingAdd to favorites

Leave a Reply