[resolved] twaitforsqldata; how to check for new rows in two tables

One Star

[resolved] twaitforsqldata; how to check for new rows in two tables

hello,
i need to check for new rows in two different tables.
connecting with one table works, but if i connect the second component twaitforsqldata with the second table, then the check is performed only here. the first check don´t work anymore.
one table gets new data periodictly and the second table holds systemdata, which should be updated with timestamps after aggregation.
so the first component checks for new rows and the second one checks for updated coulums in usually one row.
another idea is a timebased start like with cron (how?), but best way will be, to start if new data arrived.
any suggestions?

i'm using TOS 4.2.2 on windows7, mysql 5 and java.
best wishes
jens

Accepted Solutions
One Star

Re: [resolved] twaitforsqldata; how to check for new rows in two tables

hello,
i created a solution: compare the max row in table with a stored value of the last max row of this table.
this sql-code works in twaitforsqldata.
"select max(DatumZeit) from wettertable where DatumZeit > '"+(String)context.get("dirty_datetime_last")+"'"
thanks
Jens

All Replies
Community Manager

Re: [resolved] twaitforsqldata; how to check for new rows in two tables

Hi Jens
Can you upload a screenshot of job? It will be very helpful for us to understand your problem.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] twaitforsqldata; how to check for new rows in two tables

Hello,
here is my upload.
It shows the relevant two connectors. As described, only one of them is working, if both are connected.
btw: i tried it also with "on component ok", but this do not work, because i cannot connect to the second connector.
In the meantime i try another idea:
I load the relevant rows out of the second table into global vars (using tsetglobalvar). That worked.
BUT now i have another problem. I see and could view the correct values in a connected tlogview.
Unfortunately a usage in a job (especially use in tmap) brings a "null" value...
I tried it with
(Date)globalmap.get(""+row5.dirty_datetime_last+"")
OR
(Date)globalmap.get("+row5.dirty_datetime_last+")
OR
(String)globalmap.get(""+row5.dirty_datetime_last+"")
(String)globalmap.get("+row5.dirty_datetime_last+")
I think there is a deeper problem i do not see, perhaps i cannot use this globals overall??
But i thought globals mean overall.
Perhaps i should store them in context. But how could i manage it, to store from global to context?
Any idea?
Jens
Community Manager

Re: [resolved] twaitforsqldata; how to check for new rows in two tables

Hi
I got the same problem when using two tWaitfordata components in a job, I will investigate it further and check if it is a bug or not. To get around it, move each tWaitfordata component to a child job, for example:
father job:
tRunJob_1(call the child job1)
tRunJob_2(call the child job2)
open the job setting/Extra and check the box 'Mutil thread execution' to make the job execute with multiple threads.
child job1:
tMysqlConnection--onsubjobok--tWaitfordata--iterate---next processing
child job2:
tMysqlConnection--onsubjobok--tWaitfordata--iterate---next processing
I think there is a deeper problem i do not see, perhaps i cannot use this globals overall??
But i thought globals mean overall

The global variables are only access in the current job.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] twaitforsqldata; how to check for new rows in two tables

Hello there,
thank you for answer.
<<<The global variables are only access in the current job.
Ok understand. For clearness: I mean in my current job, not overall in TOS, but in this job to all components e.g. tmap component. I could view data with the tlogrow component, but not with tmap.
Here is the tmap value-editor for one globalvar.
Seven Stars

Re: [resolved] twaitforsqldata; how to check for new rows in two tables

I had no problem with two tWaitForSqlData components in one job. Note that with your job design the OnSubjobOK links are executed sequentially i.e. only after the tWaitForSqlData_1 branch is complete will the tWaitForSqlData_2 branch be started.
Re your globalMap problem: you say that you've used tSetGlobalVar so to reference the stored values you should be using the Key name from that component e.g. (Date)globalMap.get("LastDateTime"). globalMap.get tries to find a variable with the name that's in the brackets. Note that you can also use auto-complete (Ctrl+Space) to obtain the correct reference to the variables created with tSetGlobalVar (although you might need to change the data type reference in the preceding brackets.
One Star

Re: [resolved] twaitforsqldata; how to check for new rows in two tables

Hello there,
i tried it and it works!
But how could i manage it to use the results of both subjobs?
I noticed, that both subjobs are executed quasi parallel, but if i want to compare both results (one or more rows), these results have to be there to same time, aren`t they?
Usually i use a tmap component with two inputs and one output.
Any idea?
Best wishes
Jens
here are the pictures
main job and the two subjobs
One Star

Re: [resolved] twaitforsqldata; how to check for new rows in two tables

Re your globalMap problem: you say that you've used tSetGlobalVar so to reference the stored values you should be using the Key name from that component e.g. (Date)globalMap.get("LastDateTime"). globalMap.get tries to find a variable with the name that's in the brackets. Note that you can also use auto-complete (Ctrl+Space) to obtain the correct reference to the variables created with tSetGlobalVar (although you might need to change the data type reference in the preceding brackets.

here is my setting for the globalvars.
Do you think it is ok?
I don´t get any errors, but i cannot access the variables.
Any idea?
Best wishes
Jens
Seven Stars

Re: [resolved] twaitforsqldata; how to check for new rows in two tables

What you are doing in tSetGlobalVar is creating a globalMap variable with e.g. the name set to the value of row5.dirty_datetime_last and the value set to the String "+row5.dirty_datetime_last+". No wonder you can't access them! Smiley Happy
It should look like the following screenprint and be accessed using (Date)globalMap.get("row5.dirty_datetime_last").
Note that if you want to store a lot of values from a row into the globalMap, it's easier to just use tFlowToIterate with "Use the default (key,value) in global variables" checked as this will do exactly what I've shown in the screenprint for all the columns in the flow.
One Star

Re: [resolved] twaitforsqldata; how to check for new rows in two tables

What you are doing in tSetGlobalVar is creating a globalMap variable with e.g. the name set to the value of row5.dirty_datetime_last and the value set to the String "+row5.dirty_datetime_last+". No wonder you can't access them! Smiley Happy
It should look like the following screenprint and be accessed using (Date)globalMap.get("row5.dirty_datetime_last").
Note that if you want to store a lot of values from a row into the globalMap, it's easier to just use tFlowToIterate with "Use the default (key,value) in global variables" checked as this will do exactly what I've shown in the screenprint for all the columns in the flow.

Hello!
thank you for your input, but it doesn´t work.
It leads to a " ...cannot resolved to a variable"
here is the screenshot.
any ideas?
bestwishes
Jens
Seven Stars

Re: [resolved] twaitforsqldata; how to check for new rows in two tables

You left out the row5. on the right-hand (value) side. Check my screenprint again Smiley Happy
Seven Stars

Re: [resolved] twaitforsqldata; how to check for new rows in two tables

Also, since you have row3 leading in to tSetGlobalVar, all the references should be row3 not row5.
You should also note that what tLogRow is showing is the values of row3 and not what is being set by tSetGlobalVar.
One Star

Re: [resolved] twaitforsqldata; how to check for new rows in two tables

You left out the row5. on the right-hand (value) side. Check my screenprint again Smiley Happy

hello there!
sorry my mistake.
But i doesn´t work :-(
the excel file contains null values...

here are three scrennshots.
1. the whole job
2. setting the global vars
3. tmap component
anything else must be wrong, perhaps the tmap-component is doing his job BEFORE the setglobals is filled up with the variables.
is this possible? Or how could i syncronize the flow?
best wishes
jens
One Star

Re: [resolved] twaitforsqldata; how to check for new rows in two tables

Hello there,
how could i manage it to use the results of both subjobs?
I noticed, that both subjobs are executed quasi parallel, but if i want to compare both results (one or more rows), these results have to be there to same time, aren`t they?
Usually i use a tmap component with two inputs and one output.

hello there,
does anybody have an idea for that?
is it possible with tos? or should i rearrange my job?
best wishes
jens
Seven Stars

Re: [resolved] twaitforsqldata; how to check for new rows in two tables

You've put two copies of the tSetGlobalVar screenshot instead of tMap.
But, yes, OnComponentOK is executed before OnSubjobOK so the subjob from tWaitForSqlData to tFileOutputExcel is executed before the tSetGlobalVar subjob in your design. If you want tSetGlobalVar executed first then instead of the OnComponentOK from tMysqlConnection to tWaitForSqlData you should have an OnSubjobOK from tMysqlInput_3 to tWaitForSqlData.
But I'm not exactly clear on what you're trying to do/what order you want things to happen...
One Star

Re: [resolved] twaitforsqldata; how to check for new rows in two tables

You've put two copies of the tSetGlobalVar screenshot instead of tMap.
But, yes, OnComponentOK is executed before OnSubjobOK so the subjob from tWaitForSqlData to tFileOutputExcel is executed before the tSetGlobalVar subjob in your design. If you want tSetGlobalVar executed first then instead of the OnComponentOK from tMysqlConnection to tWaitForSqlData you should have an OnSubjobOK from tMysqlInput_3 to tWaitForSqlData.
But I'm not exactly clear on what you're trying to do/what order you want things to happen...

Thanks, now it works!
But now i find out, that another thing isn´t working, the way i need.
ToDo:
I want to check every 60sec, if new data rows has arrived in the table wettertable. that means, an insert of rows has occured.
but i don`t know how many rows and the exact timestamp of them.
because of that, there should be a check on new rows.
i thought, that the component tWaitForSqlData could do that, but now i mean the component can´t.
if i understand the documentation correct, the criteria in the following screenshot checks if the absolut rowcount is greater or equal 1, but not that one or more new rows arrived in table wettertable.
correct?
if so, how could i manage a dynamic grow of the table?
any ideas?
jens
Seven Stars

Re: [resolved] twaitforsqldata; how to check for new rows in two tables

You should really start a new topic for this question but you need to count the rows already in the table and use that value for the "Trigger action when row count is greater than" or if the table has an identity field use the current max ID in a where clause in the advanced settings of the component or set up a CDC on the table.
One Star

Re: [resolved] twaitforsqldata; how to check for new rows in two tables

hello,
i created a solution: compare the max row in table with a stored value of the last max row of this table.
this sql-code works in twaitforsqldata.
"select max(DatumZeit) from wettertable where DatumZeit > '"+(String)context.get("dirty_datetime_last")+"'"
thanks
Jens