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