Workflow Application: EXECSQL usage

Development Workflow Applications
1

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.

See the EXECSQL Workflow Application section in the WorkflowGen Administration Guide for complete instructions on how to use EXECSQL.

Supported query types

  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • SCALAR
  • PROCEDURE

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: 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 ( @ ).

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

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

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

Note: 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

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