SQL settings

The SQL settings section allows you to configure database connection string and query. The following options are available:

• Value from
• Connection string
• Query
• Rollback query on transaction stop

Below, see detailed description for Query and Rollback query options. For details on Value from and Connection string options, please see this page.

---

Query #

SQL settings > Query

The Query section is where you define the SQL query or stored procedure that will be executed against the database. When expanded, the following fields are available by default: Query type, Query, Query parameters, and Timeout (sec).

Query type #

SQL settings > Query > Query type

This option specifies the type of SQL query that will be executed. Depending on your choice, different fields and options will become available:

• Query (default): Select this to execute a standard SQL query. When this option is selected, the Query and Query parameters fields become available.
• StoredProcedure: Select this option to execute a stored procedure in the database. When selected, the Stored procedure name and Stored procedure parameters fields become available.

---

Options when Query type is Query: #

Query #

SQL settings > Query > Query type: Query > Query

Displayed when Query type is Query.

This field is where you specify the actual SQL query to be executed. You must enter a valid SQL query as a string. For example:

SELECT * FROM Employees WHERE Department = 'Sales'

Query parameters #

SQL settings > Query > Query type: Query > Query parameters

Displayed when Query type is Query.

Defines SQL query parameters.

To configure query parameters:

1. Expand the Query parameters section.
2. Click on Add SqlQueryParameter.
3. Expand SqlQueryParameter [1].

The following fields are available by default: Parameter name, Database type, and Parameter mode. Depending on the selected database type and parameter mode, additional options may appear.

Parameter name #

SQL settings > Query > Query type: Query > Query parameters > SqlQueryParameter [1] > Parameter name

Displayed when Query type is Query.

Used to identify the parameter within the SQL query. It should match the placeholder in the SQL query. For example, if your query is:

SELECT * FROM Employees WHERE Department = @Department

The parameter name should be Department.

Database type #

SQL settings > Query > Query type: Query > Query parameters > SqlQueryParameter [1] > Database type

Displayed when Query type is Query.

Defines the database type of the parameter in the SQL query. Common options include: AnsiString, Binary, Currency, Date, Guid, Int16, Object, String, Uint16, Xml, DateTimeOffset, etc. Depending on the selected type, additional configuration options might appear, such as Expand placeholders for types like AnsiString, Guid, String, and Xml.

Parameter mode #

SQL settings > Query > Query type: Query > Query parameters > SqlQueryParameter [1] > Parameter mode

Displayed when Query type is Query.

Determines how the value for this parameter is provided:

• Static: Choose this option if the parameter will always have the same value. When selected, the Value field appears.
• Dynamic: Choose this if the parameter’s value should change depending on property values on an M-Files object defined in the Value from setting. When selected, the Dynamic parameter type and Parameter default value fields appear.

Value #

SQL settings > Query > Query type: Query > Query parameters > SqlQueryParameter [1] > Parameter mode: Static > Value

Displayed when Query type is Query and Parameter mode is Static.

Enter the specific value that will be used for the parameter in the SQL query.

Dynamic parameter type #

SQL settings > Query > Query type: Query > Query parameters > SqlQueryParameter [1] > Parameter mode: Dynamic > Dynamic parameter type

Displayed when Query type is Query and Parameter mode is Dynamic.

This field specifies the type of dynamic data that will be used to populate the parameter. The available options include:

• Expression: When selected, the Parameter value field becomes available for further configuration.
• Property: When selected, the Parameter value section appears, with fields for Property and Attribute for further configuration.
• File: When selected, the File section appears, with the Mode, Filename wildcard, and File representation options for further configuration.
• Filename: When selected, the File section appears, with the Mode and Filename wildcard fields for further configuration.
• EmailAddress: The Parameter value section includes Type and Value fields for further configuration.

Parameter value #

SQL settings > Query > Query parameters > SqlQueryParameter [1] > Parameter mode: Dynamic > Dynamic parameter type: Expression > Parameter value

Displayed when Parameter mode is Dynamic and Dynamic parameter type is Expression.

Specifies the parameter value. Enter the dynamic expression which will be resolved from an M-Files object defined in the Value from setting.

Property #

SQL settings > Query > Query parameters > SqlQueryParameter [1] > Parameter mode: Dynamic > Dynamic parameter type: Property > Parameter value > Property

Displayed when Parameter mode is Dynamic and Dynamic parameter type is Property.

Specifies the property from an M-Files object defined in the Value from setting, that will be used to populate the SQL query parameter. When multi-select lookup property is selected, only the first lookup value is used.

Attribute #

SQL settings > Query > Query parameters > SqlQueryParameter [1] > Parameter mode: Dynamic > Dynamic parameter type: Property > Parameter value > Attribute

Displayed when Parameter mode is Dynamic and Dynamic parameter type is Property.

Controls what aspect of the lookup-based property should be used in the SQL query parameter – whether to use the ID, Name, or ExternalID of the referenced property value. It is not applicable for text-based properties.

• ID: Uses the unique identifier of the referenced property value.
• Name: Uses the name or title of the referenced property value.
• ExternalID: Uses an external identifier if the lookup value is imported from external system.

File #

SQL settings > Query > Query parameters > SqlQueryParameter [1] > Parameter mode: Dynamic > Dynamic parameter type: File > File

Displayed when Parameter mode is Dynamic and Dynamic parameter type is File.

This setting specifies the file that will be used to populate the SQL query parameter. It’s important to note that this setting is not used when the variable is an array item.

Mode #

SQL settings > Query > Query parameters > SqlQueryParameter [1] > Parameter mode: Dynamic > Dynamic parameter type: File / Filename> File > Mode

Displayed when Parameter mode is Dynamic and Dynamic parameter type is File or Filename.

Determines how to select the file or filename for the SQL query parameter. Depending on the selected option, additional fields are available. The available options are:

• CustomObject: Select any custom object from the vault. If this option is selected, the Custom Object section becomes available, allowing you to define Search conditions for finding the object and set a Search timeout (sec) to limit the search duration.
• SourceObject: Select the current source object. This is useful when the SQL query parameter should be derived from the object that triggered the rule.
• Reference: Select any reference of the source object. When this option is selected, the Reference expression field appears.

Filename wildcard #

SQL settings > Query > Query parameters > SqlQueryParameter [1] > Parameter mode: Dynamic > Dynamic parameter type: File / Filename > File > Filename wildcard

Displayed when Parameter mode is Dynamic and Dynamic parameter type is File or Filename.

Specifies a filename wildcard for selecting a file or filename from an object. The wildcard allows you to define patterns for file names, making it easier to select files dynamically. The default wildcard is *, which matches any file name.

File representation #

SQL settings > Query > Query parameters > SqlQueryParameter [1] > Parameter mode: Dynamic > Dynamic parameter type: File > File representation

Displayed when Parameter mode is Dynamic and Dynamic parameter type is File.

This setting defines how the file’s content will be represented in the SQL query:

• Base64 (default): The file content is encoded in Base64 format.
• String: The file content is treated as a string.

Parameter value #

SQL settings > Query > Query parameters > SqlQueryParameter [1] > Parameter mode: Dynamic > Dynamic parameter type: EmailAddress > Parameter value

Displayed when Parameter mode is Dynamic and Dynamic parameter type is EmailAddress.

This section allows you to configure the value for an email address-based parameter. When expanded, the Type and Value fields become available.

Type #

SQL settings > Query > Query parameters > SqlQueryParameter [1] > Parameter mode: Dynamic > Dynamic parameter type: EmailAddress > Parameter value > Type

Displayed when Parameter mode is Dynamic and Dynamic parameter type is EmailAddress.

This setting defines the source of the email address value. The options available are:

• Dynamic (default): This option allows you to manually enter an email address or dynamically extract it using a placeholder expression. When selected, the Value field appears for entering the email address.
• User: Select this option to choose an email address from the list of users in M-Files.
• Group: Select this option to choose an email address from a list of user groups in M-Files.

Parameter default value #

SQL settings > Query > Query type: Query > Query parameters > SqlQueryParameter [1] > Parameter mode: Dynamic > Parameter default value

Displayed when Query type is Query and Parameter mode is Dynamic.

This setting defines a default parameter value to use when a value is not set in the vault. It can be null, or specified explicitly.

• Null (default): When selected, the SQL parameter will default to null if no value is provided from the vault.
• Specify: If selected, the Explicit default value field appears, where you can define a static value to use as the default. Depending on the selected Database type, different options are suggested or can be entered.

Explicit default value #

SQL settings > Query > Query type: Query > Query parameters > SqlQueryParameter [1] > Parameter mode: Dynamic > Parameter default value: Specify > Explicit default value

Displayed when Query type is Query, Parameter mode is Dynamic, and Parameter default value is Specify.

Specifies default static value that will be used if no value is set from the vault.

Expand placeholders #

SQL settings > Query > Query type: Query > Query parameters > SqlQueryParameter [1] > Expand placeholders

Displayed when Query type is Query.

This option controls whether to expand placeholders within text values.

• No (default): Placeholders in the text value will not be expanded, meaning that any placeholders (e.g., {Username}, {Date}) will be passed as literal strings in the SQL query.
• Yes: If selected, placeholders within the text value will be dynamically replaced with their corresponding property values from an M-Files object defined in the Value from setting.

Timeout (seconds) #

SQL settings > Query > Timeout (seconds)

This setting specifies the maximum amount of time, in seconds, that the system will wait for the SQL query to execute before timing out. The default timeout is set to 30 seconds.

---

Options when Query type is Stored procedure: #

Stored procedure name #

SQL settings > Query > Query type: StoredProcedure > Stored procedure name

Displayed when Query type is StoredProcedure.

Enter the name of the SQL stored procedure that will be executed.

Stored procedure parameters #

SQL settings > Query > Query type: StoredProcedure > Stored procedure name

Displayed when Query type is StoredProcedure.

This section allows you to define the parameters that the stored procedure requires. These parameters are configured similarly to the SQL query parameters, with fields for Parameter Name, Database Type, Parameter Mode, and other options. For details, see the Query parameters section.

---

Rollback query on transaction stop #

SQL settings > Rollback query on transaction stop

Rollback SQL query to be executed in case of an error. NOTE: Rollback query will only be executed if Rule error handling behaviour is set to Stop.

When expanded, the following fields are available by default: Query type, Query, Query parameters, and Timeout (seconds).

If StoredProcedure is selected as the Query type, the Stored procedure name and Stored procedure parameters fields are available.