Iterate

One Star

Iterate

Hi i need to create an itaration that executes a tdbinput for everty record in a table.
The sql i have to use in the tdbinput is dependet of the values of the table.
So
i create a tdbsqlrow a tdbinput and i link from sqlrow to tdbinput whit an iterate link.
How can i use the field values in tdbslrow as a part of the sql of the tdbinput
thank u!
Employee

Re: Iterate

At the moment, there is no "auto completion" way to do this. All you really need to know is that the current row of the tDBInput_1 component is a Perl array @tDBInput_1. Thus, you can reach the first column with $tDBInput_1[0], the second column is $tDBInput_1[1] and so on.

Can you give more details about the job you want to create, because I'm not sure having an iterate link from a tDBSQLRow to a tDBInput is what you really want (I'm sure you don't want this in fact, it can't work this way).
One Star

Re: Iterate

In the source db i have a lot of tables with the same structrures that i want to load into one.
The name of the tables are stored in a table

So i tought to create a tdbsqlrow that executes a query from the table containig names and to create a dbinput and a dboutput.
The sql query in the dbinput can be something like "select * from " . $tdbsqlrow_1[0] .
The sqlrow is linked to the dbinput via a iterate link an the dbinput to dboutput via main link.

it is correct?
Employee

Re: Iterate

No tDBSQLRow required. As you can see on the screenshot, use 2 tDBInput and a tDBOutput. An iterate link between tDBInput_1 and tDBInput_2 and a row link between tDBInput_2 and tDBOuput_1.

In the tDBInput_1, the query must be something like 'select tablename from tablelist', in tDBInput_2, the query must be something like 'select id, name from '.$tDBInput_1[0]

Warnings: all what is after the iterate link is inside a loop, so the tDBOutput_1 connection will be made as many times as lines coming from tDBInput_1 query. Don't use the action "drop and create table" on the tDBOutput_1, unless you want only the last input table to persist in output database.
One Star

Re: Iterate

Ok. It works...
but how to truncate the table prior the execution of the iteration?
Employee

Re: Iterate

gb wrote:
how to truncate the table prior the execution of the iteration?

With a tDBOutput triggered before the sub-job, with action "clear table".
One Star

Re: Iterate

What is the java equivalent of Perl's "$tDBInput_1[0]" for select statement???
Thx
Employee

Re: Iterate

You can use inputRow.columnName (row1.myColumn for example)
One Star

Re: Iterate

I Try to reproduce this exemple "tDBInput_1" / "tDBInput_2" and a "tFileOutputCsv_1" with filename :'C:/out'.$tDBInput_1[0].'.csv'
This command does not produce iterative creation of file with different filename. Only one file is created : Out.csv with the result of the query inside.

Is-it possible to check / debug the .$tDBInput_1[0] values ?

Thanks in advance
Employee

Re: Iterate

gblanc wrote:
I Try to reproduce [...]

Which release are you working with? which generated language?
One Star

Re: Iterate

Sorry, i forgot to indicate my version of talend : 2.2.0.GA_r6191 with a perl project.
Employee

Re: Iterate

gblanc wrote:
Sorry, i forgot to indicate my version of talend : 2.2.0.GA_r6191 with a perl project.

With the new 2.2.0, you have to write $tDBInput_1->[0]. See informations on my blog : Talend 2.2.0M1 and Perl code performances
One Star

Re: Iterate

thanks, It's works fine now !

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Have you checked out Talend’s 2019 Summer release yet?

Find out about Talend's 2019 Summer release

Blog

Talend Summer 2019 – What’s New?

Talend continues to revolutionize how businesses leverage speed and manage scale

Watch Now

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog