topic Re: Using a datetime context variable in tMSSQLInput to filter the records in Talend Studio

Using a datetime context variable in tMSSQLInput to filter the records

Anonymous — Thu, 19 May 2016 19:20:04 GMT

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)+"'"

Re: Using a datetime context variable in tMSSQLInput to filter the records

Anonymous — Fri, 20 May 2016 09:58:31 GMT

flowtoiterate does not save the value in context.columnname but in (String)globalMap.get("rowname.columnname")
( if you have switch on standartbehaivor)
HTH
dj

Re: Using a datetime context variable in tMSSQLInput to filter the records

Anonymous — Fri, 20 May 2016 17:59:33 GMT

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.

Re: Using a datetime context variable in tMSSQLInput to filter the records

Anonymous — Mon, 23 May 2016 17:24:05 GMT

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.

Re: Using a datetime context variable in tMSSQLInput to filter the records

Anonymous — Tue, 05 Feb 2019 10:51:25 GMT

Thanks a lot, it works for me.

For another client, I did this and working fine at that time --

--tMSSQLInput - where mod_dt>'"+context.accounts_etl_date+"'

But this time, I have format context value --

--tMSSQLInput - where createdDate > '" + TalendDate.formatDate("yyyy-MM-dd HH:mm:ss.SSS",context.max_created)+"'"

Thanks a lot