One Star

[resolved] How to set Context Variable with Value from Database

Hi there,
 
My last post "Simple job tMysqlRow to tLogRow not working", was a test that I had to do because I couldn't get setting a context variable with a value from the database working.  So now thanks to Matej, I know to use the tMysqlInput instead of the tMysqlRow component for selecting the record.  But my POC for setting a context variable with a value from the database is still not working.
 
Here is what I have:
tMysqlInput---Main/Row--->tJavaRow---OnComponentOK--->tContextDump---Row/Main--->tLogRow
 
The tMysqlInput component has a SELECT query that gets a single record by ID.  The same query from the working POC for going from tMysqlInput to tLogRow.
 
The tJavaRow component has the following code in it:
context.CurrentOperation_Task = input_row.Operations_Task;
context.CurrentOperation_Params = input_row.Operations_Params;
context.CurrentOperation_Initiated = input_row.Operations_Initiated;
context.CurrentOperation_FileName = input_row.Operations_FileName;
context.CurrentOperation_State = input_row.Operations_State;
context.CurrentOperation_User = input_row.Users_Login;
 
When I run the job it runs without error, and the tMysqlInput component says "1 rows in 0.03s", and the context variables are dumped into the log, but none of the values have been assigned.
 
What am I doing wrong here?
 
Regards,
 
Scott
1 ACCEPTED SOLUTION

Accepted Solutions
One Star

Re: [resolved] How to set Context Variable with Value from Database

MateJ,
Thanks again for your response.  Ok so here is what I have:
tMysqlInput---Row/Main--->tSplitRow---Row/Main--->tContextLoad
The tMysqlInput has the following schema:
DB Column                   Type              DB Type
Operations_Task            Integer         INT UNSIGNED
Operations_Params        String           VARCHAR
Operations_Initiated       Date             DATETIME
Operations_FileName      String           VARCHAR
Operations_State            String          VARCHAR
The tSplitRow has all the same columns on the left and the following columns on the right which match the tContextLoad schema:
Column   Type      Length
key         String    255
value      String    255
I have the following context variables:
Name                                     Type
CurrentOperation_Task            Integer
CurrentOperation_Params        String
CurrentOperation_Initiated       Date
CurrentOperation_FileName      String
CurrentOperation_State            String
In the tSplitRow Columns mapping I have the following:
"CurrentOperation_Task"         --->   row5.Operations_Task
"CurrentOperation_Params"     --->   row5.Operations_Params
"CurrentOperation_Initiated"    --->   row5.Operations_Initiated
"CurrentOperation_FileName"   --->   row5.Operations_FileName
"CurrentOperation_State"         --->   row5.Operations_State
 
When I run the job, I get the following errors:
 - Type mismatch: cannot convert from Integer to String
 - Type mismatch: cannot convert from Date to String
Not sure what I have done wrong here?
PS:
Is there a trick to getting images to show.  I did the "Click and past your screenshot" which actually worked this time, and I could see the image in the editor (though it made the image small and unreadable and there was no way to re-size it?), but when I preview the post no images show....?  It would save me a heap of typing if I could just take some screen shots....  Also it says I can't post live URLs till I have done 10 posts on the forum.  I don't think it is counting replies...   What is the way to get around this... should I just post 10 posts and then delete them?
Also you mention that assigning variables via tJava/tJavaRow is not recommended.  Is there a best practices guide somewhere I can check out that has these type of guidelines?
Thanks again for your time,
Scott
8 REPLIES
One Star

Re: [resolved] How to set Context Variable with Value from Database

Ok so it appears that tContextDump is not the right component to do this with, or I am not using it correctly, as I found that if I added the following code to the bottom of tJavaRow:
 
System.out.println("context.CurrentOperation_Task = " + context.CurrentOperation_Task);
System.out.println("context.CurrentOperation_Params = " + context.CurrentOperation_Params);
System.out.println("context.CurrentOperation_Initiated = " + context.CurrentOperation_Initiated);
System.out.println("context.CurrentOperation_FileName = " + context.CurrentOperation_FileName);
System.out.println("context.CurrentOperation_State = " + context.CurrentOperation_State);
System.out.println("context.CurrentOperation_User = " + context.CurrentOperation_User);
I could see that the values were assigned.  But the output of tContextDump in the log is not showing this.
Regards,
Scott
One Star ark
One Star

Re: [resolved] How to set Context Variable with Value from Database

Hey Scott,
Talend offers 2 components for loading/dumping context variables: tContextLoad, tContextDump. The loading components expects flow in a key & value pair, defined by a fixed schema. It would be better (and it is recommended) to use these components.
To transform data into the key/value pairs, you can use tSplitRow component (It would be better to do it on the DB level, but I am not sure how and if it can be achieved). When you have a flow in form of key/value pair, you can direct the flow into the tContextLoad component, which will map the values (using the key as an exact name of a context variable) into your context variables.
Why it wasn't working the way you tried it? The location context.<var_name> is not permanent and it just holds the value for you to access it easily. The context is in fact in form of Map with keys and values. In the background, the context is accessed using methods getProperty/setProperty. So in your case the tJavaRow might look like this:
context.setProperty("CurrentOperation_Task", input_row.Operations_Task);
etc.
I'll leave it up to you, what way of implementation you choose. When we are talking about 1 line with several values, it does not make a huge difference in performance.
Regards,
Matej
One Star

Re: [resolved] How to set Context Variable with Value from Database

Matej,
 
Thanks again mate.  So does this mean that using context.setProperty I have to convert dates and int's to strings to use that method.. to assign these values to fields of type date and int.
 
The values I am loading in are from an existing system, so I can't have a key value schema.  I will have a look at tSplitRow.
 
If the names don't match I guess I have to use a tMap to get them to...
 
Seems like a lot of hoops to jump through for such a simple task of assigning a value to a variable.
 
What if you want to assign a value to a context variable in one tJava component that will be used by other components.  Do you have to use the context.setProperty for this and and convert dates and other types to strings?
 
Thanks for your time,
 
Scott
One Star ark
One Star

Re: [resolved] How to set Context Variable with Value from Database

All datatypes except Dates are ok, but converting Date to string and putting it into the context variable might cause a problem. Talend stores dates in a special format and uses internal function to parse date to form, readable for Studio. So I don't think you can do it using the tJavaRow/tJava component (after all, it's not even recommended).
But using the tSplitRow is not a problem. You will define 2-columns schema as an output schema from the tSplitRow (You need to copy the schema from the connected tContextLoad component), define key values as fixed strings, depending on the names of your context variables and assign values from the flow (f.e. <input_row_name>.<column_name_from_input_flow>, where <input_row_name> is a name of a row connected as an input into the component). This will parse the row in a key/value pairs and using the tContextLoad will do all the magic. This way there will be no conflict in context variable names. 3 components all together.
I hope this helped you.
Regards
Matej
One Star

Re: [resolved] How to set Context Variable with Value from Database

MateJ,
Thanks again for your response.  Ok so here is what I have:
tMysqlInput---Row/Main--->tSplitRow---Row/Main--->tContextLoad
The tMysqlInput has the following schema:
DB Column                   Type              DB Type
Operations_Task            Integer         INT UNSIGNED
Operations_Params        String           VARCHAR
Operations_Initiated       Date             DATETIME
Operations_FileName      String           VARCHAR
Operations_State            String          VARCHAR
The tSplitRow has all the same columns on the left and the following columns on the right which match the tContextLoad schema:
Column   Type      Length
key         String    255
value      String    255
I have the following context variables:
Name                                     Type
CurrentOperation_Task            Integer
CurrentOperation_Params        String
CurrentOperation_Initiated       Date
CurrentOperation_FileName      String
CurrentOperation_State            String
In the tSplitRow Columns mapping I have the following:
"CurrentOperation_Task"         --->   row5.Operations_Task
"CurrentOperation_Params"     --->   row5.Operations_Params
"CurrentOperation_Initiated"    --->   row5.Operations_Initiated
"CurrentOperation_FileName"   --->   row5.Operations_FileName
"CurrentOperation_State"         --->   row5.Operations_State
 
When I run the job, I get the following errors:
 - Type mismatch: cannot convert from Integer to String
 - Type mismatch: cannot convert from Date to String
Not sure what I have done wrong here?
PS:
Is there a trick to getting images to show.  I did the "Click and past your screenshot" which actually worked this time, and I could see the image in the editor (though it made the image small and unreadable and there was no way to re-size it?), but when I preview the post no images show....?  It would save me a heap of typing if I could just take some screen shots....  Also it says I can't post live URLs till I have done 10 posts on the forum.  I don't think it is counting replies...   What is the way to get around this... should I just post 10 posts and then delete them?
Also you mention that assigning variables via tJava/tJavaRow is not recommended.  Is there a best practices guide somewhere I can check out that has these type of guidelines?
Thanks again for your time,
Scott
One Star ark
One Star

Re: [resolved] How to set Context Variable with Value from Database

Your job is fine, but you need to assign Strings in tSplitRow component. Use the .toString() on your Integer. As for Date: The tContextLoad accepts only dates in the "dd-MM-yyyy HH:mm:ss" format.
So what you need to do, is basically use the Talend function and format the date into the mentioned format:
TalendDate.formatDate("yyyy-MM-dd HH:mm:ss", row5.Operations_Initiated)
If you put this into the tSplitRow, you should be fine.
Regards
Matej
One Star

Re: [resolved] How to set Context Variable with Value from Database

Matej,
That worked a treat mate, thanks for your assistance on this, much appreciated!!
Regards,
Scott
One Star

Re: [resolved] How to set Context Variable with Value from Database

For anyone reading this, I would suggest looking at this post:
The correct way to assign the value of context variables in Java
https://www.talendforge.org/forum/viewtopic.php?id=46191
As the official word from the Talend team is that they use simple direct assignment in java code, not the setProperty method.
(No offence meant to Matej, very thankful for him taking the time to answer my posts, but after reading more on this issue in the forum I felt there was some confusion in the developer community on this issue and we needed an official response from the Talend team)
Regards,
Scott