topic Re: Load context from MS SQL table in Talend Studio

Load context from MS SQL table

Anonymous — Wed, 04 Dec 2013 02:49:06 GMT

I've used implicit context load using file but now I'm trying Load context from a table but I don't know why its not able to load the contexts from the table.
Table columns
key | value
host | abc.it.go
username | talend
pwd | mypassword
additonal_param | php=domain
port | 1234
I've created a context group called test_contexts
in that i've created
host
username
pwd
additional_param
port
So in the SQLconnection i've got the host as context.host , username as context.username and so on ... I've got system.out.println(context.host); and so on in a tjava. When I run the job it fails saying implicit_context_load exception for 'key' and everything is null null null null what ever prints for host, username etc
Is it mandatory to use another tsqlconnection and then tcontextload to read this 2 columns? Can you let me know what am doing wrong here?
Thanks

Re: Load context from MS SQL table

Anonymous — Wed, 04 Dec 2013 03:35:52 GMT

Hi,
I have made a testing on talend open studio for Data Integration 5.3.1, everything is fine. Could you connect to your DB with data without context directly? Is there any error when you click guess schema under the condition of using implicit context?
Best regards
Sabrina

Re: Load context from MS SQL table

Anonymous — Wed, 04 Dec 2013 04:30:10 GMT

Yes. It connects succesfully if I hardcode the values. So you used the same key and value as columns?
Does this problem occur if MS SQL server has both windows and SQL server authentication?
Thanks,
Anjan

Re: Load context from MS SQL table

Anonymous — Wed, 04 Dec 2013 04:46:54 GMT

no there is no error when i click the schema retrieval under implicit context load
Thanks,
Anjan

Re: Load context from MS SQL table

Anonymous — Wed, 04 Dec 2013 06:38:01 GMT

Hi,
I suspect you job still use explicit context which contains "null" value in Contexts. Make sure you have checked your Implicit context option in job view.
Is it working when you use explicit context?(give fixed value in context ).
Best regards
Sabrina

Re: Load context from MS SQL table

_AnonymousUser — Wed, 04 Dec 2013 16:47:22 GMT

Found the error.

MS SQL server does not allow key as a column. So even if you create a table with key and value as a column MS SQL will put as the column so when you take the SQL from the generated code
select context.key,context.value from context and execute it will fail. Is there a work around for this without changing anything in the job?
Thanks

Re: Load context from MS SQL table

_AnonymousUser — Wed, 04 Dec 2013 19:39:45 GMT

Found a solution. You need to edit the
\plugins\tContextLoad\tContextLoad_java.xml
and change the key to the column name you are using.. for ex i used tkey
<TABLE READONLY="true">
<COLUMN NAME="tkey" TYPE="id_String" READONLY="true" CUSTOM="true" LENGTH="255"/>
<COLUMN NAME="value" TYPE="id_String" READONLY="false" CUSTOM="true" LENGTH="255"/>
</TABLE>
</PARAMETER>

However i think talend should fix this bug on their side to add [] to key in the query like
select ,value from table;
Thanks,
Anjan

Re: Load context from MS SQL table

_AnonymousUser — Wed, 04 Dec 2013 19:41:49 GMT

BTW we are using enterprise edition. So I don't know if you guys still have a solution without changing the xml file and editing any existing jobs using the context groups which have already been defined please let us know.

Re: Load context from MS SQL table

Anonymous — Thu, 05 Dec 2013 03:01:08 GMT

Hi,

BTW we are using enterprise edition. So I don't know if you guys still have a solution without changing the xml file and editing any existing jobs using the context groups which have already been defined please let us know.

Thanks for sharing your solution with us.
Could you open a ticket on Talend Support Portal so that we can follow your steps to see if it is a bug for talend.
Best regards
Sabrina

Re: Load context from MS SQL table

Anonymous — Wed, 11 Dec 2013 23:07:15 GMT

Hello,
There is already a ticket with support #2013120510000201
After modifying tContextload.xml, It works on Talend Studio on local machine, Please suggest what needs to be done to apply same component to job/tac server ?
tried updating : /talend/studio/Talend-Studio-r99271-V5.2.2/plugins/org.talend.designer.components.localprovider_5.2.2.r99271/components/tContextLoad
and
/talend/cmdline/studio/plugins/org.talend.designer.components.localprovider_5.2.2.r99271/components/tContextLoad
also removed javacache at /talend/cmdline/studio/configuration but no luck.