One Star

Iterate ?

Hi everybody,
My issue of the day :
I have a list of IDs, and I'd like, for each ID, to extract corresponding data in an Oracle database.
I guess I should use an "Iterate" link, but I don't know how to use it, and documentation is really light about this.
Maybe someone is able to help me ?
Thanks ! ;-)
Tags (1)
7 REPLIES
Employee

Re: Iterate ?

I've used MySQL instead of Oracle because it's easier for me :-)
mysql> select * from topic3529;
+------+-----------+
| id | name |
+------+-----------+
| 1 | name1-01 |
| 14 | name14-01 |
| 14 | name14-02 |
| 18 | name18-01 |
| 23 | name23-01 |
| 23 | name23-02 |
| 23 | name23-03 |
+------+-----------+
7 rows in set (0.00 sec)

The output of my job is:
Starting job topic3529 at 15:42 16/07/2008.
.----------------.
| tLogRow_1 |
+----+-----------+
| id | name |
+----+-----------+
| 14 | name14-01 |
| 14 | name14-02 |
'----+-----------'
.----------------.
| tLogRow_1 |
+----+-----------+
| id | name |
+----+-----------+
| 23 | name23-01 |
| 23 | name23-02 |
| 23 | name23-03 |
'----+-----------'
.----------------.
| tLogRow_6 |
+----+-----------+
| id | name |
+----+-----------+
| 14 | name14-01 |
| 14 | name14-02 |
| 23 | name23-01 |
| 23 | name23-02 |
| 23 | name23-03 |
'----+-----------'
===
execution time: 24 milliseconds
===
Job topic3529 ended at 15:42 16/07/2008.

The difference between subjob A and subjob B is the tUnite, that's all. You can see that in subjob B, all the queries are considered as a single data flow.
One Star

Re: Iterate ?

OK I think I've got it ! Thanks ! :-)
I wouldn't have found it on my own !
One Star

Re: Iterate ?

Hello,
Anybody can translate this perl call function : $_globals{tFlowToIterate_1}{'current_id'} into java ?
Many thanks
Employee

Re: Iterate ?

((String)globalMap.get("current_id"))
One Star

Re: Iterate ?

Hi plegall,
This is great example, but raises a question in my mind about iteration.
To achieve the same result I thought an Iterate connection from tFileInputDelimited_1 to tMysqlinput_1 would achieve the same result by referencing data via $_globals{tFileInputDelimited_1}{'current_id'}. Through my testing I notice that it doesn't work.
Therefore, what is the real purpose of the Iterate connection or does/can/will it supersede tFlowToIterate?
Cheers,
c0utta
Employee

Re: Iterate ?

To achieve the same result I thought an Iterate connection from tFileInputDelimited_1 to tMysqlinput_1 would achieve the same result by referencing data via $_globals{tFileInputDelimited_1}{'current_id'}. Through my testing I notice that it doesn't work.

In a Perl project tFlowToIterate is not mandatory but highly recommended for maintainability and readability. Without intermediate tFlowToIterate, you could have written $tFileInputDelimited_1-> (syntax introduced in TOS 2.2, $tFileInputDelimited_1 in earlier releases). tFlowToIterate maps flow columns into global variables with names (not only numeric indexes in an array). What you may suggest is that we have an implicit tFlowToIterate? It would be a good suggestion but how would we make the difference between tFileList (which only output an iterate link) and tFileInputDelimited (which outputs rows in a flow and optionnaly an iterate link).
In a Java project tFlowToIterate is mandatory because data flow is not created if you don't create a row output link to tFileInputDelimited_1.
Therefore, what is the real purpose of the Iterate connection or does/can/will it supersede tFlowToIterate?

iterate link was created a very long time before tFlowToIterate component. It's not planned we remove the link of the component.
In the component palette, there are components dedicated to iterate : tFor, tForeach, tLoop, tFileList, tMomMessageIdList, tInfiniteLoop. Any component creating a data flow can be transformed into an "iterate dedicated component" thanks to tFlowToIterate component.
Four Stars

Re: Iterate ?

you can achieve this by  putting below query in your tmysqlinput6 component

SELECT id,name FROM topicc3529   
           WHERE id='"+((Integer)globalMap.get("row3.current_id"))+"'"