Execute SQL that Results in Additional SQL Select Statements to be Executed

CloverDX Platform
1

I am new to CloverETL and I want to execute a SQL statement that results in additional select statements to then be executed. The first statement dynamically retrieves the schemas in a Vertica database. The result statements are select statements for each schema that insert audit information into a Vertica system table.

The first statement is:
select distinct ‘select audit(’‘’||table_schema||‘’‘);’ from tables;

The results from the first query are, for example:
select audit(‘schema1’);
select audit(‘schema2’);
select audit(‘schema3’);
select audit(‘schema4’);
select audit(‘schema5’);

I am using a DBInputTable component to execute the first query then sending the output as a discrete query to a DBExecute component. I am getting an error “A RowCount was expected but not generated from query”. I was reading on the forums that DBExecute does not take select statements even though in this particular case it inserts records into a system table. What can I use to execute the resulting select statements?

2

Hi,

I would recommend that instead of using DBExecute you use another DBInputTable to process your queries. The DBinputTable should have the following settings:

  • D_B connection_ should have your Vertica connection.

  • In the Query URL click on Port tab and select your input port field (make sure your Processing Type is set to discrete).

  • Run your graph

3

I did find a solution to my issue. I created a second DBInputTable component in place of the DBExecute component I originally had to execute the second set of select statements then have an output from there to a Trash component. This solution executes as intended.

4

Hi,

I would recommend that instead of using DBExecute you use another DBInputTable to process your queries. The DBinputTable should have the following settings:

  • D_B connection_ should have your Vertica connection.

  • In the Query URL click on Port tab and select your input port field (make sure your Processing Type is set to discrete).

  • Run your graph

“vazquezrosariop”

Hi Pedro, I’m trying to do this but it wont let me set the processing type to discrete for some reason.

5

Hi Maximo,

Could you please provide me with a screenshot where the error is visible. Also, what version of CloverETL are you currently running?