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.
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_).
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 (@).
You can use QUERY_PARAM_MyParam or @MyParam, where MyParam corresponds to the name of the parameter defined in the query.
SELECT querySCALAR querySELECT queryINSERT queryHere's another possibility for query parameters:
UPDATE queryDELETE querySCALAR queryThe 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.
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)
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.
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
INOUT
FORM_DATA
FORM_DATA_GRIDVIEW
TEXT
IN
REQUEST_GRID
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
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
IN
UPDATE WFCATEGORY SET NAME = @Name WHERE ID_CATEGORY = @IdCategory
QUERY_PARAM_IdCategory
NUMERIC
IN
1
QUERY_PARAM_Name
TEXT
IN
NewCategoryName
IN
DELETE FROM WFCATEGORY WHERE ID_CATEGORY = @IdCategory
QUERY_PARAM_IdCategory
NUMERIC
IN
1
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
IN
EXEC GET_USER @USERNAME = @User
QUERY_PARAM_User
NUMERIC
IN
wfgen_admin
IN
SELECT COUNT(*) FROM USERS
RESULT
NUMERIC
OUT
2
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
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).
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
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
Parameter
Type
Direction
Description
RESULT
NUMERIC
OUT
Contains the numeric result of a SCALAR type query
Parameter
Type
Direction
Value
CONNECTION_NAME
TEXT
IN
MainDbSource
QUERY
TEXT
IN
SELECT LASTNAME, FIRSTNAME, USERNAME FROM USERS
RESULT_JSON
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
Parameter
Type
Direction
Value
CONNECTION_NAME
TEXT
IN
MainDbSource
TYPE
TEXT
IN
INSERT
QUERY
Parameter
Type
Direction
Value
CONNECTION_NAME
TEXT
IN
MainDbSource
TYPE
TEXT
IN
INSERT
QUERY
Parameter
Type
Direction
Value
CONNECTION_NAME
TEXT
IN
MainDbSource
TYPE
TEXT
IN
UPDATE
QUERY
Parameter
Type
Direction
Value
CONNECTION_NAME
TEXT
IN
MainDbSource
TYPE
TEXT
IN
DELETE
QUERY
Parameter
Type
Direction
Value
CONNECTION_NAME
TEXT
IN
MainDbSource
TYPE
TEXT
IN
PROCEDURE
QUERY
Parameter
Type
Direction
Value
CONNECTION_NAME
TEXT
IN
MainDbSource
TYPE
TEXT
IN
SELECT
QUERY
Parameter
Type
Direction
Value
CONNECTION_NAME
TEXT
IN
MainDbSource
TYPE
TEXT
IN
SCALAR
QUERY
Parameter
Type
Direction
Value
CONNECTION_NAME
TEXT
IN
MainDbSource
CMD1_TYPE
TEXT
IN
INSERT
CMD1_QUERY
Parameter
Type
Direction
Value
CONNECTION_NAME
TEXT
IN
MainDbSource
CMD1_TYPE
TEXT
IN
INSERT
CMD1_QUERY
TEXT
TEXT
TEXT
FILE
TEXT
TEXT
TEXT
TEXT
TEXT
TEXT
TEXT
TEXT
TEXT
<?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>