Database Activity

Activity Description

The Database activity enables execution of queries, stored procedures, and functions from the workflow. The database activity establishes connection with the database server and retrieves or updates data in the database. The activity gets the schema of the database table to an XML variable according to the query, stored procedures, or functions passed.

Application Scenario

Consider a scenario where a manager wants to send appraisal forms to all the employees. The manager can add the database activity with a query to select the email address column from the Employee Database. The Output XML will have the schema based on the query and this XML variable can used to assign employees to whom the appraisal forms are to be sent. When the workflow is initiated, the query in the database activity will be executed and all the employees will receive the appraisal form in their Inbox.

Activity Properties

You can configure the Database activity by specifying appropriate values in the Activity Properties area. To access the Activity Properties, click the Activity Properties menu in the Tool Bar. Or, right-click the Database activity and select the Activity Properties option. The Activity properties are organized under groups of related properties. The properties under each group are described in this topic.

Note: Every time you input a value for a property, ensure to save the data. Click the Save button on the Activity Properties window. You need to save the property so that the subsequent properties in the activity can inherit the required information.

Name & Description  

You can use these properties to specify the name and description for the activity.

Name - This property can be used to specify a name for the activity.

Property Type: Optional (This property needs to be set only if necessary.)

Description - This property specifies a brief description to be displayed for the activity.

Property Type: Optional (This property needs to be set only if necessary.)

Configuration

You can use these properties to configure the functionality of the activity.

Database Connection

Database connection property is used to get the connection to the Database Server. You can use this property to select the Database connection (which has been configured in Enterprise Console > Settings > Database Connections).

Property Type: Mandatory (This property must be set if the activity has to establish connection with the database server.)

Query Builder

To enter the query, click the Query Builder property to open the property window.

In this window, set the following properties:

1. Command Type

2. Query

3. Output XML variable

   

Command Type: The command types that can be selected

• Query

• Stored Procedure

• Functions

Enter Query: As the Command Type is selected, you can enter a query, Stored Procedure, or Function.

Note: The Expression Editor <> icon is displayed only for the Command Type "Query ", as the expressions set cannot be evaluated in design time for stored procedures and functions.

Query: Query can be entered in the text box or from the Expression Builder.

For example, the query can be Select * from EmployeeTable and if the query is to be passed with parameters then the query will be Select * from EmployeeTable where EmployeeID=@Id. Here @Id is the parameter. When you click Load Parameters, it will load the parameters within the query. In the Value field you can pass the value for the parameter.

Query with 'Order By', 'Group By' keywords are supported.

The following Parameter values can be saved with empty values:

• Name: Displays the name of the parameter.

• Type: Select the Parameter type from the drop-down list. This type helps you to execute the query with internally defined default values only while generating the database schema. The appropriate Type needs to be selected for the Query and Functions. For stored procedures the Types will be automatically selected.

• Value: Type the value of the parameter.

Parameter helps you retrieving values from the database where the value is null or empty.

If the query is to retrieve values from the database we have to specify the Output XML variable name. For more information, see the table Database Activity.

Multiple queries are not supported. If multiple queries are used,  XML variable will carry the result of the last query written.

Click View Schema to view the schema of the output XML variable "outXML"

XML variable used in one activity cannot be reused within other database activities.

If the output schema containing return values have been modified after the property is saved, open the Query Builder property and click Save. The Schema will get updated with the latest values. Click View Schema to see the updated changes.

This XML Variable "outXML" will be added in the Start > XML Variables as shown below.

Parameter values cannot update data types like 'image', 'blob' and 'unique identifier' from XML variables or variables.

Stored Procedure

Stored procedures can be executed by simply specifying the name of the Stored Procedure (provided there is a stored procedure created in your database server). On the click of "Load Parameters" it will load the parameters present in the Stored Procedure. If you want to retrieve values on executing the Stored Procedure you can specify the name of the XML variable. If return values from the stored procedure are stored in output parameters, the activity will load the XML schema with the output parameters.

To discover the output XML schema, system internally executes stored procedure with parameter values as empty string or null. If a stored procedure has validations on parameter or data integrity constraints, you cannot configure a stored procedure.

If a stored procedure returns the values for return Result Set and output parameters, then both the values will be available in the XML variable.

Note: If there are multiple Return Result Sets, then only the first return Result Set will be considered.

• Skip Execution of Stored Procedure: To avoid the execution of stored procedure to get the output xml schema, select this check box so that you can configure stored procedure with parameter validation or data integrity constraints.

Note:
- It is recommended to use proper parameter values/variables that has appropriate values at runtime to execute the stored procedure.
- To execute a stored procedure, which expects default values for execution, you can create a wrapper stored procedure with the same parameter. You can then call the actual stored procedure with default values passed to the stored procedure if the parameter values are empty or null.

Functions

Functions are executed in the same way as executed in the database server, for e.g. "select dbo.FunctionName". If the function contains parameters you should specify the exact parameters. For e.g. "select dbo.FunctionName(@param1, @param2)" (provided there is the function created in your database server). If you want to retrieve values on executing the function you can specify the name of the XML variable. If return values from the function are stored in output parameters, the activity will load the XML schema with the output parameters.

Note:
- Table valued functions are not supported.

Output XML Variable for return value:

Output XML variable will have the schema of the table as per the query entered. If the query is "Select * from EmployeeData" then the XML variable will have all the columns as nodes in the schema which can be looped through and used in the subsequent activities. Output XML Variable name should be unique, it cannot be repeated.

Note: To create an XML Iterator of the output XML Variable the node selected should always be the "Table" node.

Capture runtime exceptions in a variable

You can create a variable with the name ^<ActivityName>Error in the Start activity. The exception message will be available in the variable if an exception occurs.

For example: For an activity named as "DBAct1", you have to create a variable with name as ^DBAct1Error in the Start activity.

For more information on creating variables, see Start - Variables.

Property Type: Mandatory (This property must be set if the activity is to retrieving any value from the database (i.e. if query is of "select" type, else this field will be optional because it will not load any schema for 'insert', 'update' or 'delete' type of query)

Retry Connections: This property allows setting the maximum retries if a Database goes Offline at run time. By default the value set will be 1.

Property Type: Optional (This property needs to be set only if necessary.)

The following table shows the SQL database types and their corresponding parameter types for a database activity:

Note: If the Database connection is Windows authenticated, the Identity of the Quickflow Application Pool should be the user who has access to the same database.