WorkflowGen Administration Guide
8.0
DocumentationForumSupport
Search…
⌃K
Links

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
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).
​
Previous
RESTAPICLIENT Workflow Application
Next
JSONTODATA Workflow Application
Last modified 1yr ago