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

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

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

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

[email protected]

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