Sometimes an ITPilot wrapper needs to connect to external sources to obtain data useful for its execution. ITPilot provides a component that allows to send queries to any data source available through the JDBC protocol and returns a list of records containing the resultset from the query. This component is the JDBC Extractor.
This component accepts zero or more records and zero or more values as input arguments. These elements are used to assign variables to the component configuration parameters.
The output of the JDBC Extractor will be a record list, where the record structure is defined by the query performed on the data source.
If we need to get data from a VDP server, we can configure the JDBC Extractor component as follows:
As the output of the component is a record list, the results of the query will have to be iterated in a flow similar to the one below:
This is a very simple example of a process that executes a query and includes the results in the output of the wrapper.
This would be the result of the wrapper execution:
In this example, the same query will always be executed. It is also possible to parameterize the query using input values coming from the wrapper.
We are going to modify the example adding an additional input parameter that we will use as input of the JDBC Extractor component. Note that in this example we will use this input parameter as input of the component but a value created or extracted inside the wrapper could also be used as input for the JDBC Extractor.
Once the parameter is defined, the JDBC Extractor can be modified adding the input parameter as input of the component and as parameter of the query. In this example, the output of the Start component (“Start_1_Output” by default) will be the input value for the JDBC Extractor component.
Editing the query in the “Query” tab of the JDBC Extractor component, it is possible to specify the parameter using the syntax ‘@<parameter_name>’ with the ‘@’ character preceding the name of the parameter, for example:
SELECT * FROM incident WHERE state = '@state'
When clicking the “Query Database” button, we will need to specify a state to allow the component to execute the query and get its output schema.
Click “Ok”, and the wrapper will be configured to send the input parameter as a query parameter to the data source.
Now, to run the wrapper in the example, it will be necessary to provide a value for the input parameter:
The output of the wrapper will contain the resultset of the query as before but with this version the query executed will be different depending on the value entered as input:
ITPilot Generation Environment Guide: section “JDBC EXTRACTOR”