# Workflow Application: EXECSQL usage

**URL:** <https://discuss.workflowgen.com/t/workflow-application-execsql-usage/1738>\
**Category:** Workflow Applications\
**Created:** [December 18, 2020, 5:27pm UTC](https://discuss.workflowgen.com/t/workflow-application-execsql-usage/1738 "2020-12-18T17:27:26Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![wfg-admin](https://yyz2.discourse-cdn.com/flex030/user_avatar/discuss.workflowgen.com/wfg-admin/32/714_2.png) [@wfg-admin](https://discuss.workflowgen.com/u/wfg-admin)\
**Post date:** [December 18, 2020, 5:27pm UTC](https://discuss.workflowgen.com/t/workflow-application-execsql-usage/1738/1 "2020-12-18T17:27:26Z")

</div>

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.

See the [EXECSQL Workflow Application](https://docs.workflowgen.com/admin/workflow-applications/execsql-workflow-application) section in the [WorkflowGen Administration Guide](https://docs.workflowgen.com/admin/) for complete instructions on how to use EXECSQL.

### Supported query types

- `SELECT`
- `INSERT`
- `UPDATE`
- `DELETE`
- `SCALAR`
- `PROCEDURE`

### Required parameters

| Parameter | Type | Direction | Description |
| --- | --- | --- | --- |
| `CONNECTION_NAME` | TEXT | IN | 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 |
| --- | --- | --- | --- |
| `TYPE` | TEXT | IN | Type of query to execute: `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) |

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

#### 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 ( `@` ).

For 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 |
| --- | --- | --- | --- |
| `QUERY` | TEXT | IN | `SELECT * FROM USERS WHERE LASTNAME = @UserLastname` |
| `QUERY_PARAM_UserLastName` / `@UserLastName` | TEXT | IN | `Doe` |

### Return parameters

#### General

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

### Examples

#### `SELECT` query

Store the results in data:

| 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 |
| --- | --- | --- | --- |
| `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` |

#### `INSERT` query

| Parameter | Type | Direction | Value |
| --- | --- | --- | --- |
| `CONNECTION_NAME` | TEXT | IN | `MainDbSource` |
| `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 |
| --- | --- | --- | --- |
| `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 |
| --- | --- | --- | --- |
| `CONNECTION_NAME` | TEXT | IN | `MainDbSource` |
| `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 |
| --- | --- | --- | --- |
| `CONNECTION_NAME` | TEXT | IN | `MainDbSource` |
| `QUERY` | TEXT | IN | `DELETE FROM WFCATEGORY WHERE ID_CATEGORY = @IdCategory` |
| `QUERY_PARAM_IdCategory` | NUMERIC | IN | `1` |

#### Stored procedure

| 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` |

**Note:** The `PROCEDURE` query type does not return a value. If you want to return a value, you must use the `SELECT` type:

| 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` |

#### Launch multiple requests

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

---

<div class="post-metadata">

**Author:** ![jianan.hsu](https://yyz2.discourse-cdn.com/flex030/user_avatar/discuss.workflowgen.com/jianan.hsu/32/411_2.png) [@jianan.hsu](https://discuss.workflowgen.com/u/jianan.hsu)\
**Post date:** [November 29, 2024, 9:44pm UTC](https://discuss.workflowgen.com/t/workflow-application-execsql-usage/1738/3 "2024-11-29T21:44:55Z")

</div>

Unpinned on November 29, 2024, 9:44pm UTC