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