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.

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
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
​
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)
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 (@).
📌 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.
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
"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
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:
<?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>
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
 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
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
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).
​

RESTAPICLIENT Workflow Application

JSONTODATA Workflow Application

Last modified 3mo ago

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

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)

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

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`

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.

EXECSQL Workflow Application

Overview

How it works

Required parameters

Optional parameters

General

Query parameters

📌 Example

Return parameters

General

`SELECT` query

`SCALAR` query

Examples

`SELECT` query

Store the results in data

Populate a GridView

`INSERT` query

`UPDATE` query

`DELETE` query

Stored procedure

`SCALAR` query

Launch multiple requests

Requests with transactions and error management