EXECSQL Workflow Application
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 theQUERY
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_
, wherex
corresponds to the command number (e.g.CMD1_
). - The supported query types are
SELECT
,INSERT
,UPDATE
,DELETE
,SCALAR
, andPROCEDURE
. - 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 withCMDx_
. It is not possible to use a global connection and a local connection (e.g.CONNECTION_NAME
andCMD2_CONNECTION_NAME
). - You can use a global transaction for multiple commands. In this case, you must not prefix the
TRANSACTION
parameter withCMDx_
. It is not possible to use a global transaction and a local transaction (e.g.TRANSACTION
andCMD2_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 theweb.config
file to0
to deactivate logs,1
for error logs,2
for information logs, or3
for debug logs; the default value is0
.
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:
|
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 ofFORM_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.
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.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 | 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. |
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 | 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 |
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>
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 |
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 |
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 |
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 |
Parameter | Type | Direction | Value |
CONNECTION_NAME | TEXT | IN | MainDbSource |
TYPE | TEXT | IN | SCALAR |
QUERY | TEXT | IN | SELECT COUNT(*) FROM USERS |
RESULT | NUMERIC | OUT | 2 |
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 |
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). |
Last modified 3mo ago