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