How to pass one complete row from the main flow to a subjob?

[Anonymous]

Hello,
I have a parent job iterating over all entries of a source table.
For each single row of the source table the parent job calls a subjob which processes this single row by generating inserts/updates to multiple other tables.
The work of the subjob that is all inserts/updates done when processing a single row of the source table is a single transaction and has to be committed or rollbacked.

I have tried this by just connecting the iterate flow of tMSSqlInput to the tRunJob which starts the subjob and by using the tBufferInput in the subjob.
But this does not work. The subjob get no data.
Has anybody an idea how to reach this goal?
Many thanks in advance for every help.
Regards Hartmut

[Anonymous]

Hello,
here is another try with a new parent job.
But still the subjob gets no data.
Why?

[Anonymous]

Hello
You could define context vars in the child job to receive the vlaue of each column of main flow from father job.
Please see my screenshots.
Best regards

shong

[Anonymous]

Hello Shong,
thank you very much for your reply.
I have just one problem. My sourcetable has 65 columns.
So when going the way you have designed I have to create 65 context variables in the subjob. This is not very nice;-)
Is there no other way to give a complete row as recordset from the fatherjob to the childjob?
Many thanks in advance for any help!
Regards Hartmut

[Anonymous]

Hello

Is there no other way to give a complete row as recordset from the fatherjob to the childjob?

Talend Integration Suite, which is a commerical version and you need purchase it, provide jobjet feature. With jobjet, you can pass the complete row more easy to child job like this:
tFileInputDelimited---tJobLet_1
Best regards
shong

[Anonymous]

Hello Shong,
i have done it like you suggested using context variables in the child job and a tFlowToIterate component in the father job and a tFixedFlowInput component in the chid job.
But now i have the following problem with context variables of type String:
When there is no value passed from the father job to the child job then i get a string "null" in the child job as value of the schema column.
The context variables have no default values defined in the context-tab.
Can you please help me to avoid this "null" string?
Many thanks in advance.
Regards Hartmut

[Anonymous]

Hello again,
i have found out that when the tFixedFlowInput component reads out a context variable of type string, it gets a string "null" instead of a NULL value.
When defining a context variable of type string it gets automatically the default value null assigned.
You can see this on the "values as tree" and on the "values as table" subtab on the "Contexts" tab of the job.
But i have removed this assignment. The columns "value" and "Default" are empty.
And even so the context variable gets the string "null" assigned instead of a NULL value when calling the child job with the tRunJob component (Basic Settings\Context Param).
Or the assignment of NULL works fine but when reading the context variable out NULL is replaced by "null".
So i have to care for this when reading the value out of a context variable of type string (and replace "null" with NULL).
Any idea how to prevent the assignment of "null" to a context variable of type string when assigning a NULL value to it?
Many thanks in advance for any help.
Regards Hartmut

[Anonymous]

Hello

Any idea how to prevent the assignment of "null" to a context variable of type string when assigning a NULL value to it?

You can convert null to NUll Using UPCASE() method before referencing context var,

if(context.name.equals("null")){
System.out.println(context.name);
context.name=StringHandling.UPCASE(context.name);
System.out.println(context.name);
}

Result:
null
NULL
Best regards
shong

[Anonymous]

Hello shong,
thank you for your reply.
I dont want a string "null" and i dont want a string "NULL".
I just want an empty context variable in case of no value is present.

To reach this i have used this function in the "Basic Settings" of the tFixedFlowInput component in the section "Values" in the column "Value":

public static String nvl(String p_string) {
    	if ((p_string == null) || (p_string.equals("")) || (p_string.equals("null")) || (p_string.trim().equals(""))) {
    		return null;    		
    	} else {
        	return p_string;
    	}
    }

Regards Hartmut