topic Re: Storing Date-time in a variable from tMSSQLinput query and using it in another tMSSQLinput in Talend Studio

Storing Date-time in a variable from tMSSQLinput query and using it in another tMSSQLinput

Anonymous — Thu, 07 Mar 2019 06:46:58 GMT

Hi Everybody,

I'm very new to TOS and I have almost a similar situation in MS SQL where i'm suppose to insert latest records to a table AC. So I'm trying to get max Date from AC to know which records to fetch from another SQL DB. For this as per above diagram i have started with a tMSSQLinput_1 component with above mention query then i want to store the result set of tMSSQLinput_1 to tSetGlobalVar_2 (not sure if this is the best approach). As you can see i have added a Key and just to test if the results are stored i have used tLogRow_1 Component. but i am getting this error

"Execution failed : object is not an instance of declaring class
Job AgentCubeNew ended at 20:59 06/03/2019. [exit code=0]"

I would like to know is this the right approach?, also where should i write globalMap.put("myValue",*value*) to set the value in my variable from the query ?

Once i'm able to set the variable i would like to call it in another MSSQLinput component to get records from another db table which > max(date) of table AC

Please guide

my talend is Version: 5.6.1

Re: Storing Date-time in a variable from tMSSQLinput query and using it in another tMSSQLinput

manodwhb — Thu, 07 Mar 2019 06:56:31 GMT

@abdulbasit,you can do multiple one is creating context and loading that max date into context and reading that context variable in the next component where you were writing query based on date.

and another way is set using tsetGlobalvar. on which component are you getting an error?

Re: Storing Date-time in a variable from tMSSQLinput query and using it in another tMSSQLinput

Anonymous — Thu, 07 Mar 2019 07:21:03 GMT

@manodwhb
can you see the screen shot and guide me accordingly.

Re: Storing Date-time in a variable from tMSSQLinput query and using it in another tMSSQLinput

manodwhb — Thu, 07 Mar 2019 07:23:59 GMT

@abdulbasit ,i see the problem in tSetGlobalVar,value should be like row1.colum with out " ". can you provide that way.

Re: Storing Date-time in a variable from tMSSQLinput query and using it in another tMSSQLinput

Anonymous — Thu, 07 Mar 2019 07:31:24 GMT

@manodwhb
i have replaced tsetGlobalvar_2 value to row1.maxdate
still the same error
"Execution failed : object is not an instance of declaring class
Job AgentCubeNew ended at 10:29 07/03/2019. [exit code=0]
"

Re: Storing Date-time in a variable from tMSSQLinput query and using it in another tMSSQLinput

manodwhb — Thu, 07 Mar 2019 07:37:10 GMT

@abdulbasit ,on which component are you getting that error?

Re: Storing Date-time in a variable from tMSSQLinput query and using it in another tMSSQLinput

manodwhb — Thu, 07 Mar 2019 07:42:26 GMT

@abdulbasit ,check the below example,

Re: Storing Date-time in a variable from tMSSQLinput query and using it in another tMSSQLinput

Anonymous — Thu, 07 Mar 2019 07:46:20 GMT

@manodwhb Please see the attached
error.jpg

Re: Storing Date-time in a variable from tMSSQLinput query and using it in another tMSSQLinput

manodwhb — Thu, 07 Mar 2019 07:55:27 GMT

@abdulbasit ,I relay not able to test your issue. we have dig into one step down,with out tSetGlobalvar was that job running successfully just to keep toracleinput and tlogrow

Re: Storing Date-time in a variable from tMSSQLinput query and using it in another tMSSQLinput

Anonymous — Thu, 07 Mar 2019 08:07:04 GMT

i think its because of the format of datetime can you try with "yyyy-MM-dd HH:mm:ss"

Re: Storing Date-time in a variable from tMSSQLinput query and using it in another tMSSQLinput

manodwhb — Thu, 07 Mar 2019 08:12:37 GMT

@abdulbasit ,i am not getting in error in talend Open studio 7.1.1,check below

Re: Storing Date-time in a variable from tMSSQLinput query and using it in another tMSSQLinput

Anonymous — Thu, 07 Mar 2019 08:28:59 GMT

@manodwhb

i tried it without tGlobalVariable still the same Class Cast exception. i even tried changing the date format by
"Select format(max(Date), 'dd-MM-yyyy') as mdt from AgentCube" same error. when i guess schema after this updated query the system reads it as string with 400 length

Re: Storing Date-time in a variable from tMSSQLinput query and using it in another tMSSQLinput

manodwhb — Thu, 07 Mar 2019 09:27:45 GMT

@abdulbasit ,can you check the schema and query of the tOracleInput.

Re: Storing Date-time in a variable from tMSSQLinput query and using it in another tMSSQLinput

Anonymous — Thu, 07 Mar 2019 09:52:51 GMT

@manodwhb
its tMSSQL input
the query is mention in the 1st screenshot