# EXECSQL Workflow Application

## 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.<br>
* 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_`).<br>
* The supported query types are `SELECT`, `INSERT`, `UPDATE`, `DELETE`, `SCALAR`, and `PROCEDURE`.<br>
* The application supports SQL transaction management.<br>
* 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`).<br>
* 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.<br>
* 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**                                                                                                                        |
| ----------------- | -------- | ------------- | -------------------------------------------------------------------------------------------------------------------------------------- |
| `CONNECTION_NAME` | TEXT     | IN            | <p>Name of the connection to use</p><p></p><p>The connection name must be defined in the WorkflowGen <code>web.config</code> file.</p> |
| `QUERY`           | TEXT     | IN            | Query to execute                                                                                                                       |

## Optional parameters

### General

| **Parameter**          | **Type** | **Direction** | **Description**                                                                                                                                                                                                                                                     |
| ---------------------- | -------- | ------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `TYPE`                 | TEXT     | IN            | <p>Type of query to execute</p><p></p><p>The supported types are:</p><ul><li><code>SELECT</code> (default)</li><li><code>INSERT</code></li><li><code>UPDATE</code></li><li><code>DELETE</code></li><li><code>SCALAR</code></li><li><code>PROCEDURE</code></li></ul> |
| `TRANSACTION`          | TEXT     | IN            | <p>When set to <code>Y</code>, the application triggers a SQL transaction before the request execution and will perform a commit or rollback based on the result.<br><strong>Default:</strong> <code>N</code></p>                                                   |
| `ON_ERROR`             | TEXT     | IN            | <p>When set to <code>CATCH</code>, the application will not return errors to WorkflowGen. This allows an error message to be stored in the <code>ERROR\_MESSAGE</code> parameter and to continue the execution.<br><strong>Default:</strong> <code>THROW</code></p> |
| `TIMEOUT`              | NUMERIC  | IN            | <p>Indicates the number of seconds to define in the command execution time<br><strong>Default:</strong> <code>30</code></p>                                                                                                                                         |
| `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            | <p>Separator used in the return value or CSV file</p><p><strong>Default:</strong> <code>,</code> (comma)</p>                                                                                                                                                        |

{% 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.<br>
* 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**                                      |
| --------------------------------------------- | -------- | ------------- | ---------------------------------------------------- |
| `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           | <p>Indicates whether a <code>commit</code> was performed on the transaction<br><strong>Possible values:</strong><code>Y</code> or <code>N</code></p> |

#### `SELECT` query

| **Parameter**           | **Type**                                 | **Direction** | **Description**                                                                                                                                                                                                                                          |
| ----------------------- | ---------------------------------------- | ------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `RESULT_ROWx_fieldName` | <p>TEXT</p><p>NUMERIC</p><p>DATETIME</p> | OUT           | <p>Contains the value of the <code>fieldName</code> column for row <code>x</code><br></p><p>You must replace <code>fieldname</code> with your column name (e.g. <code>LASTNAME</code>) and <code>x</code> with the row number (e.g. <code>2</code>).</p> |
| `RESULT_ROW_fieldName`  | <p>TEXT</p><p>NUMERIC</p><p>DATETIME</p> | 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           | <p>Contains the query result in <code>CSV</code> format</p><p></p><p>Data are separated according to the separator defined in the <code>RESULT\_CSV\_SEPARATOR</code> parameter.</p>                                                                     |
| `RESULT_CSV_FILE`       | FILE                                     | OUT           | <p>Contains the query result in <code>CSV</code> format stored in a <code>.csv</code> file</p><p></p><p>Data are separated according to the separator defined in the <code>RESULT\_CSV\_SEPARATOR</code> parameter.</p>                                  |

#### `SCALAR` query

| **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**                                                                                                                                                              |
| ----------------------- | -------- | ------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `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           | <p><code>"LASTNAME","FIRSTNAME","USERNAME" "Administrator","WorkflowGen","wfgen\_admin"</code></p><p><code>"Doe","John","john.doe"</code></p>                          |
| `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`                                                                                                                |

{% 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
<?xml version="1.0" standalone="yes"?>
<NewDataSet>
    <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
        <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
            <xs:complexType>
                <xs:choice minOccurs="0" maxOccurs="unbounded">
                    <xs:element name="Table1">
                        <xs:complexType>
                            <xs:sequence>
                            </xs:sequence>
                        </xs:complexType>
                    </xs:element>
                    <xs:element name="REQUEST_GRID">
                        <xs:complexType>
                            <xs:sequence>
                                <xs:element name="REQUEST_GRID_LASTNAME" type="xs:string" minOccurs="0"/>
                                <xs:element name="REQUEST_GRID_FIRSTNAME" type="xs:string" minOccurs="0"/>
                                <xs:element name="REQUEST_GRID_USERNAME" type="xs:string" minOccurs="0"/>
                            </xs:sequence>
                        </xs:complexType>
                    </xs:element>
                </xs:choice>
            </xs:complexType>
        </xs:element>
    </xs:schema>
    <Table1></Table1>
</NewDataSet>
```

{% endhint %}

### `INSERT` query

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

### &#x20;`UPDATE` query

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


---

# 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/9.0/execsql-workflow-application.md?ask=<question>
```

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.