# EXECSQL ## Overview The **EXECSQL** workflow application allows you to execute one or multiple SQL queries in a process. EXECSQL lets you retrieve information via `SELECT` queries, for example, in order to use the results in process conditions. ## How it works * EXECSQL requires the `CONNECTION_NAME` parameter, which corresponds to the connection name, and the `QUERY` parameter, which corresponds to the query to execute.
* You can specify one or multiple commands per EXECSQL action. For this, the parameters must be prefixed by `CMDx_`, where `x` corresponds to the command number (e.g. `CMD1_`).
* The supported query types are `SELECT`, `INSERT`, `UPDATE`, `DELETE`, `SCALAR`, and `PROCEDURE`.
* The application supports SQL transaction management.
* You can use a global connection for multiple commands. In this case, you must not prefix the `CONNECTION_NAME` parameter with `CMDx_`. It is not possible to use a global connection and a local connection (e.g. `CONNECTION_NAME` and `CMD2_CONNECTION_NAME`).
* You can use a global transaction for multiple commands. In this case, you must not prefix the `TRANSACTION` parameter with `CMDx_`. It is not possible to use a global transaction and a local transaction (e.g. `TRANSACTION` and `CMD2_TRANSACTION`). It's necessary to define a global connection to be able to define a global transaction.
* Application logs are available. These can be specified by setting the value of the `ExecSqlLogLevel` parameter in the `web.config` file to `0` to deactivate logs, `1` for error logs, `2` for information logs, or `3` for debug logs; the default value is `0`. ## Required parameters

Parameter Type Direction Description

Parameter Type Direction Description

CONNECTION_NAME

TEXT

Name of the connection to use

The connection name must be defined in the WorkflowGen web.config file.

QUERY TEXT IN Query to execute

## Optional parameters ### General

Parameter	Type	Direction	Description
Parameter	Type	Direction	Description
`TYPE`	TEXT	IN	Type of query to execute The supported types are: `SELECT` (default) `INSERT` `UPDATE` `DELETE` `SCALAR` `PROCEDURE`
`TRANSACTION`	TEXT	IN	When set to `Y`, the application triggers a SQL transaction before the request execution and will perform a commit or rollback based on the result. Default: `N`
`ON_ERROR`	TEXT	IN	When set to `CATCH`, the application will not return errors to WorkflowGen. This allows an error message to be stored in the `ERROR_MESSAGE` parameter and to continue the execution. Default: `THROW`
`TIMEOUT`	NUMERIC	IN	Indicates the number of seconds to define in the command execution time Default: `30`
`FORM_DATA`	FILE	INOUT	`FORM_DATA` file containing the XML process definition
`FORM_DATA_GRIDVIEW`	TEXT	IN	Identifier of the GridView to feed to the `FORM_DATA`
`RESULT_CSV_SEPARATOR`	TEXT	IN	Separator used in the return value or CSV file Default: `,` (comma)

{% hint style="info" %} * If you want to populate a GridView using the `FORM_DATA_GRIDVIEW` parameter, the contents of `FORM_DATA` must contain at least the XML schema. * If the first action of the process is an EXECSQL action, you must put a default value in `FORM_DATA` , with the definition of the schema. {% endhint %} ### Query parameters For each query, you can define parameters to use during execution. These parameters can be defined in two ways: either by using the `QUERY_PARAM` prefix or by using an at sign (`@`). #### 📌 Example You can use `QUERY_PARAM_MyParam` or `@MyParam`, where `MyParam` corresponds to the name of the parameter defined in the query.

Parameter	Type	Direction	Description
Parameter	Type	Direction	Description
`QUERY`	TEXT	IN	`SELECT * FROM USERS WHERE LASTNAME = @UserLastname`
`QUERY_PARAM_UserLastName` / `@UserLastName`	TEXT	IN	`Doe`

### Return parameters #### General

Parameter	Type	Direction	Description
Parameter	Type	Direction	Description
`ERROR_MESSAGE`	TEXT	OUT	Contains the error message in the event that the parameter value contains `CATCH` and an exception is thrown during execution
`RESULT_COMMIT`	TEXT	OUT	Indicates whether a `commit` was performed on the transaction Possible values:`Y` or `N`

#### `SELECT` query

Parameter	Type	Direction	Description
Parameter	Type	Direction	Description
`RESULT_ROWx_fieldName`	TEXT NUMERIC DATETIME	OUT	Contains the value of the `fieldName` column for row `x` You must replace `fieldname` with your column name (e.g. `LASTNAME`) and `x` with the row number (e.g. `2`).
`RESULT_ROW_fieldName`	TEXT NUMERIC DATETIME	OUT	Contains the value of the `fieldName` column for the first row returned
`RESULT_JSON`	TEXT	OUT	Contains the query result in `JSON` format
`RESULT_JSON_FILE`	FILE	OUT	Contains the query result in `JSON` format stored in a `.json` file
`RESULT_XML`	TEXT	OUT	Contains the query result in `XML` format
`RESULT_XML_FILE`	FILE	OUT	Contains the query result in `XML` format stored in an `.xml` file
`RESULT_CSV`	TEXT	OUT	Contains the query result in `CSV` format Data are separated according to the separator defined in the `RESULT_CSV_SEPARATOR` parameter.
`RESULT_CSV_FILE`	FILE	OUT	Contains the query result in `CSV` format stored in a `.csv` file Data are separated according to the separator defined in the `RESULT_CSV_SEPARATOR` parameter.

#### `SCALAR` query

Parameter	Type	Direction	Description
Parameter	Type	Direction	Description
`RESULT`	NUMERIC	OUT	Contains the numeric result of a `SCALAR` type query

## Examples ### `SELECT` query #### Store the results in data

Parameter	Type	Direction	Value
Parameter	Type	Direction	Value
`CONNECTION_NAME`	TEXT	IN	`MainDbSource`
`QUERY`	TEXT	IN	`SELECT LASTNAME, FIRSTNAME, USERNAME FROM USERS`
`RESULT_JSON`	TEXT	OUT	`{ "TABLE":[ { "LASTNAME":"Administrator", "FIRSTNAME":"WorkflowGen", "USERNAME":"wfgen_admin" }, { "LASTNAME":"Doe", "FIRSTNAME":"John", "USERNAME":"john.doe" } ] }`
`RESULT_JSON_FILE`	FILE	OUT	`result.json`
`RESULT_CSV`	TEXT	OUT	`"LASTNAME","FIRSTNAME","USERNAME" "Administrator","WorkflowGen","wfgen_admin"` `"Doe","John","john.doe"`
`RESULT_CSV_FILE`	FILE	OUT	`result.csv`
`RESULT_ROW_LASTNAME`	TEXT	OUT	`Administrator`
`RESULT_ROW1_FIRSTNAME`	TEXT	OUT	`WorkflowGen`
`RESULT_ROW2_USERNAME`	TEXT	OUT	`john.doe`

#### Populate a GridView

Parameter	Type	Direction	Value
Parameter	Type	Direction	Value
`CONNECTION_NAME`	TEXT	IN	`MainDbSource`
`QUERY`	TEXT	IN	`SELECT LASTNAME as REQUEST_GRID_LASTNAME, FIRSTNAME as REQUEST_GRID_FIRSTNAME, USERNAME as REQUEST_GRID_USERNAME FROM USERS`
`FORM_DATA`	FILE	INOUT	`FORM_DATA`
`FORM_DATA_GRIDVIEW`	TEXT	IN	`REQUEST_GRID`

{% hint style="info" %} If EXECSQL is the first action of the process, you must define a default value for the `FORM_DATA` data containing the schema of the array, as shown in the example below: ```markup ``` {% endhint %} ### `INSERT` query

Parameter	Type	Direction	Value
Parameter	Type	Direction	Value
`CONNECTION_NAME`	TEXT	IN	`MainDbSource`
`TYPE`	TEXT	IN	`INSERT`
`QUERY`	TEXT	IN	`INSERT INTO WFCATEGORY (ID_CATEGORY,NAME,DESCRIPTION) VALUES (@IdCategory, @Name, @Description)`
`QUERY_PARAM_IdCategory`	NUMERIC	IN	`1`
`QUERY_PARAM_Name`	TEXT	IN	`CategoryName`
`QUERY_PARAM_Description`	TEXT	IN	`Description of the category`

Here's another possibility for query parameters:

Parameter	Type	Direction	Value
Parameter	Type	Direction	Value
`CONNECTION_NAME`	TEXT	IN	`MainDbSource`
`TYPE`	TEXT	IN	`INSERT`
`QUERY`	TEXT	IN	`INSERT INTO WFCATEGORY (ID_CATEGORY,NAME,DESCRIPTION) VALUES (@IdCategory, @Name, @Description)`
`@IdCategory`	NUMERIC	IN	`1`
`@Name`	TEXT	IN	`CategoryName`
`@Description`	TEXT	IN	`Description of the category`

### `UPDATE` query

Parameter	Type	Direction	Value
Parameter	Type	Direction	Value
`CONNECTION_NAME`	TEXT	IN	`MainDbSource`
`TYPE`	TEXT	IN	`UPDATE`
`QUERY`	TEXT	IN	`UPDATE WFCATEGORY SET NAME = @Name WHERE ID_CATEGORY = @IdCategory`
`QUERY_PARAM_IdCategory`	NUMERIC	IN	`1`
`QUERY_PARAM_Name`	TEXT	IN	`NewCategoryName`

### `DELETE` query

Parameter	Type	Direction	Value
Parameter	Type	Direction	Value
`CONNECTION_NAME`	TEXT	IN	`MainDbSource`
`TYPE`	TEXT	IN	`DELETE`
`QUERY`	TEXT	IN	`DELETE FROM WFCATEGORY WHERE ID_CATEGORY = @IdCategory`
`QUERY_PARAM_IdCategory`	NUMERIC	IN	`1`

### Stored procedure

Parameter	Type	Direction	Value
Parameter	Type	Direction	Value
`CONNECTION_NAME`	TEXT	IN	`MainDbSource`
`TYPE`	TEXT	IN	`PROCEDURE`
`QUERY`	TEXT	IN	`INSERT_CATEGORY`
`QUERY_PARAM_ID_CATEGORY`	NUMERIC	IN	`1`
`QUERY_PARAM_NAME`	TEXT	IN	`CategoryName`
`QUERY_PARAM_DESCRIPTION`	TEXT	IN	`Description of the category`

{% hint style="info" %} The `PROCEDURE` query type does not return a value. If you want to return a value, you must use the `SELECT` type. {% endhint %}

Parameter	Type	Direction	Value
Parameter	Type	Direction	Value
`CONNECTION_NAME`	TEXT	IN	`MainDbSource`
`TYPE`	TEXT	IN	`SELECT`
`QUERY`	TEXT	IN	`EXEC GET_USER @USERNAME = @User`
`QUERY_PARAM_User`	NUMERIC	IN	`wfgen_admin`

### `SCALAR` query

Parameter	Type	Direction	Value
Parameter	Type	Direction	Value
`CONNECTION_NAME`	TEXT	IN	`MainDbSource`
`TYPE`	TEXT	IN	`SCALAR`
`QUERY`	TEXT	IN	`SELECT COUNT(*) FROM USERS`
`RESULT`	NUMERIC	OUT	`2`

### Launch multiple requests

Parameter	Type	Direction	Value
Parameter	Type	Direction	Value
`CONNECTION_NAME`	TEXT	IN	`MainDbSource`
`CMD1_TYPE`	TEXT	IN	`INSERT`
`CMD1_QUERY`	TEXT	IN	`INSERT INTO WFCATEGORY (ID_CATEGORY,NAME,DESCRIPTION) VALUES (@IdCategory, @Name, @Description)`
`CMD1_QUERY_PARAM_IdCategory`	NUMERIC	IN	`1`
`CMD1_QUERY_PARAM_Name`	TEXT	IN	`CategoryName`
`CMD1_QUERY_PARAM_Description`	TEXT	IN	`Description of the category`
`CMD2_QUERY`	TEXT	IN	`SELECT NAME FROM WFCATEGORY WHERE ID_CATEGORY = @IdCategory`
`CMD2_@IdCategory`	NUMERIC	IN	`1`
`CMD2_RESULT_ROW_NAME`	TEXT	OUT	`CategoryName`

### Requests with transactions and error management

Parameter	Type	Direction	Value
Parameter	Type	Direction	Value
`CONNECTION_NAME`	TEXT	IN	`MainDbSource`
`CMD1_TYPE`	TEXT	IN	`INSERT`
`CMD1_QUERY`	TEXT	IN	`INSERT INTO WFCATEGORY (ID_CATEGORY,NAME,DESCRIPTION) VALUES (1, "Name", "Description")`
`CMD1_TRANSACTION`	TEXT	IN	`Y`
`CMD1_ON_ERROR`	TEXT	IN	`CATCH`
`CMD1_COMMIT`	TEXT	OUT	`Y`
`CMD1_ERROR_MESSAGE`	TEXT	OUT	`NULL`
`CMD2_TYPE`	TEXT	IN	`INSERT`
`CMD2_QUERY`	TEXT	IN	`INSERT INTO WFCATEGORY (ID_CATEGORY,NAME,DESCRIPTION) VALUES (1, "OtherName", "Other description")`
`CMD2_TRANSACTION`	TEXT	IN	`Y`
`CMD2_ON_ERROR`	TEXT	IN	`CATCH`
`CMD2_COMMIT`	TEXT	OUT	`N`
`CMD2_ERROR_MESSAGE`	TEXT	OUT	`Cannot insert duplicate key in object 'dbo.WFCATEGORY'. The duplicate key value is (1).`

--- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.workflowgen.com/admin/workflow-applications/execsql-workflow-application.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.