Using a datetime context variable in tMSSQLInput to filter the records
Hello,
I am trying to read a max datetime from SQL Server DB (using tMSSQLInput1) to set a context variable that is then passed to another tMSSQLInput2 and used to filter the query in that component to write to a DW. I have not been able to successfully get tMSSQLInput2 to recognize the context variable being passed in. I had a tLogRow that captured/displayed the variable correctly but I'm not convinced the variable is being passed as displayed in tLogRow, or recognized as a datetime. I am getting a NullPointerException error, and others depending on how I'm trying to pass the variable.
Current job:
tMSSQLInput1 --- max_created (datetime format) -- tFlowToItereate
|
-Iterate
|
--tMSSQLInput - where createdDate > '" + TalendDate.formatDate("yyyy-MM-dd HH:mm:ss.SSS",context.max_created)+"'"
flowtoiterate does not save the value in context.columnname but in (String)globalMap.get("rowname.columnname") ( if you have switch on standartbehaivor) HTH dj
thanks dj,
I was able to solve by adding a tFixedDlowInput and tContextLoad. In the tFixedFlow I'm using (String)globalMap.get("row2.max_created") to assign the context.
I may have painted myself into a corner though because what I'm now working on is the ability to assign 2 variables, max_created and max_modified with the end goal of filtering the SQL as such: where createdDate > max_created OR modifiedDate > max_modified.
I think I can do this with the current components. Currently in the FlowtoItereate I am using the default (key,value) in global variables which seems to only allow one variable at a time. But I might be able to deselect the 'use default key/value' and define my own.
Current job:
tSQLInput --- max_created(string format) ---> tFlowToIterate (using default global variables - iterate) ---- tFixedFlowInput ((String)globalMap.get("row2.max_created") ----> tContextLoad
|
OnSubjobOK
|
tSQLInput -
where createdDate > '" + TalendDate.formatDate("yyyy-MM-dd HH:mm:ss.SSS",context.max_created)+"'"
I'll post back on how I solve it but if anyone has sugestions they are greatly appreciated.
Default (key value) Option gives you ALL columns of the Input row in form of globalMap.get("row_nr.columnName"), not only one so it should work . Manipulation of the SQL Statement as you showed is exactly what i do in such cases.
