--- title: "SQL settings: Query and Rollback query" description: "# SQL settings The **SQL settings** section allows you to configure database connection string and query. The following options are available: - [**Value from**](https://extensionkit.unitfly.com/documentation/sql-integration-value-from-connection-string/#value-from-sql-settings) - [**Connection string**](https://extensionkit.unitfly.com/documentation/sql-integration-value-from-connection-string/#connection-string-sql-settings) - [**Query**](#query-sql-settings) - [**Rollback query on transaction stop**](#rollback-query-sql-settings) Below, see detailed description for…" url: "https://extensionkit.unitfly.com/documentation/sql-integration-query-rollback-query/" updated: "2025-05-01" category: Configurations / SQL Integration / Simple Rule Configuration --- # SQL settings: Query and Rollback query # SQL settings The **SQL settings** section allows you to configure database connection string and query. The following options are available: - [**Value from**](https://extensionkit.unitfly.com/documentation/sql-integration-value-from-connection-string/#value-from-sql-settings) - [**Connection string**](https://extensionkit.unitfly.com/documentation/sql-integration-value-from-connection-string/#connection-string-sql-settings) - [**Query**](#query-sql-settings) - [**Rollback query on transaction stop**](#rollback-query-sql-settings) Below, see detailed description for **Query** and **Rollback query** options. For details on **Value from** and **Connection string** options, please see [this page](https://extensionkit.unitfly.com/documentation/sql-integration-value-from-connection-string/). --- ## 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**](#query-parameters-sql-settings) 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-type-sql-settings), [Query](#query-sql-settings), [Query parameters](#query-parameters-sql-settings), and [Timeout (seconds)](#timeout-sql-settings). If **StoredProcedure** is selected as the Query type, the [Stored procedure name](#stored-procedure-name-sql-settings) and [Stored procedure parameters](#stored-procedure-parameters-sql-settings) fields are available.