Integration Task: Database Pull
The Database Pull task enables customers to create custom, parameterized queries that execute within a process and retrieve query results into a form within the process.
Once the Database Pull query results are mapped to a form, all tasks within that process have access to the data that was pulled in. This allows to you define tasks and routing rules based on any piece of data from Integrify or external systems.
- Pull data from external systems into a form within Integrify.
- Define tasks and routing rules based on any piece of data from external systems or Integrify itself.
- Save time and effort normally spent reviewing information residing outside a process and performing manual data entry.
Configuring the Database Pull Task
Step by Step Guide
Below is a step-by-step guide on how to configure the Database Pull task.
For example, you have a need to edit an applicant's contact information. After the initial form is submitted, a Database Pull (Lookup Applicant Data) task is configured to retrieve the applicant's contact information:
Once the data is retrieved and mapped into the executing process, the data then becomes available to the rest of the process to base business rules, prefill other tasks, etc.
Configuring the Database Pull Task
Once a Database Pull task has been added to your Process Flow. Right click on the task and select the Configure Task option from the context menu:
You will be presented with the Database Pull task configuration screen:
The initial Settings tab provides the necessary settings you will need to complete to retrieve the data, what database server you will be connecting to, and the type of database provider you will need to use.
In this particular scenario, a SELECT statement has been added to the Query area to retrieve applicant information from a custom table within the database. A Query Parameter (@applicant_id) is also being used that, at run-time, will be replaced with a value based on user input on a previous form.
The following is a description of each attribute for the Database Pull Settings:
Query Parameters
The Query Parameters section allows you to capture data from the request that is executing and use it within the SQL Statement that will retrieve your list of users who will be assigned as recipients to a task.
In the SQL statement below, you will notice a @applicant_id syntax that has been included in the SQL Statement for this Database Pull configuration:
At run-time, Integrify will use a specified value from within the request and insert it into the SQL statement prior to execution. In this case, it will be the value of the unique Applicant ID from the initial form in the process.
To add a parameter to your Database Pull configuration select the Query Parameters tab:
The Query Parameters tab will list all parameters you have defined for the Database Pull. With a Query Parameter, it is possible to retrieve data from a form, information about the Requester of the process, the manager of the Requester, etc.
Output Mappings
In order for the Integrify workflow engine to utilize the query result(s) within a process, it requires that the query results be mapped to a form built within Integrify.
The form can be any existing form you have already created or a brand new form specifically built for this Database Pull task.
To map the query results to a form, select the Mappings tab:
Based on the form selection you defined on the Settings tab, the Mappings tab will have a list of available questions on that form to map.
In the example above, only 4 questions were created on the form:
- Last Name
- State
- Consearch
The assumption for this Database Pull task is that it will return only one row of data with two columns when it executes. In order to map the results of the query to the form, start by clicking the Edit icon for the row of the given question on the form.
In the example above, for the question First Name, the database column first_name is explicitly defined.
Note: The ordinal values are 1 based.
You will also need to define a row number in the result set returned for each question. Click on the Edit icon for the row for each question and enter the row the expected query result will be returned.
Note: You can return 1 to many rows of data in your query, so if you would like to return 100 rows of data with one column and map all of that into the process, you will need to create a form with 100 questions.
Once your mappings have been set, click Close.
Calling a Stored Procedure
It is possible to use a stored procedure to perform your database actions as needed using the Database Pull task. The primary difference is the initial SQL construct and the Command Type option:
In this case, we are calling a SQL Server stored procedure called spRetrieveBudget and passing in a single parameter. Also, notice that the Command Type has been changed to Stored Procedure.
If the command type is set to Stored Procedure in a DB push or pull, then only the name of the proc should be in the text field. Any parameters in the list will be automatically passed by name to the proc when executed. If you are executing a proc on SQL Server, you can also set the command type to text and enter an inline execute the query with parameters specified (i.e. "exec sp_myproc @p1, @p2).
Important! If you are using an Oracle stored procedure, take care of the order of the parameters. If you were calling a procedure like:
spInsertSalesForecast @request_id, @sales_forecast, @close_date
Then you must create the parameters in that order in the Query Parameters screen (i.e. @request_id is at the top of the list, then @sales_forecast, etc). This is due to a constraint with the Oracle Provider. SQL Server does not have the same requirements.