Connecting ITPilot to VDP using a JDBC Extractor

Applies to: Denodo 7.0 , Denodo 6.0 , Denodo 5.5 , Denodo 5.0
Last modified on: 12 Apr 2018
Tags: Connectivity JDBC data sources

Download original document

You can translate the document:

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.

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

  1. 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.

  1. 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 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:

References

ITPilot Generation Environment Guide: section “JDBC EXTRACTOR

Questions

Ask a question
You must sign in to ask a question. If you do not have an account, you can register here

Featured content

DENODO TRAINING

Ready for more? Great! We offer a comprehensive set of training courses, taught by our technical instructors in small, private groups for getting a full, in-depth guided training in the usage of the Denodo Platform. Check out our training courses.

Training