Connecting ITPilot to VDP using a JDBC Extractor

Content

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:

1. Double click in the JDBC Extractor component and go to the “Connection Configuration” tab to set the connection details:

• Database Adapter: Denodo Virtual Data Port
• Driver Class: com.denodo.vdp.jdbc.Driver
• Database URI: jdbc:vdb://localhost:9999/<database_name>
• Login: User name.
• Password: The user’s password.

2. Go to the “Query” tab to specify the query to be executed in VDP.

3. Click on the “Query database” button. In this step the JDBC Extractor component needs to execute a query to be able to obtain the structure that will be returned to the query and create the Output record structure.

4. Click “Ok” to finish the component configuration.

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 by adding an additional input parameter that we will use as an input for the JDBC Extractor component. Note that in this example we will use this input parameter as the input of the component, however any value created or extracted inside the wrapper can also be used as an input for the JDBC Extractor.

Once the parameter is defined,  the JDBC Extractor can be modified by adding the input parameter as an input of the component and as a 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.

By 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”. 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:

References

ITPilot Generation Environment Guide: section “JDBC EXTRACTOR”