Reading connection details from database for an existing context group(hardcoded)

[Anonymous]

Hi,

I have 75 talend jobs and these are connected. Each talend job migrates data from sql server to mysql. Each job reads the connection details from the contexts that I have defined. I have one context for sql server and another context for mysql. These have hardcoded connection values

Now, the requirement is to read the connection details for sql server and mysql from database. I want to change the contexts that I have already defined to read the values from database instead of the hard coded values.

I want to this as a wrapper so, that I don't have to make individual changes in every job

Any ideas on this?

Thanks in advance
Rathi

[Anonymous]

You want to make use of the Implicit Context Load functionality. This can be set up for the whole project in one go. Take a look here: https://help.talend.com/reader/mhqCkTBnin7IXmJBUJoocQ/0lPtF5eayDI~33QdLfRY~A

[Anonymous]

In the implicit context loading I need to still go to every job and make the changes using tmysqlconnection which I currently do not have

Is there a way wherein I can write a job which will write into existing harcoded contexts, and this will inturn help me not changing many jobs that I have?

Thanks in advance

Rathi

[Anonymous]

You don't need to open each job. 

Go to File --> Project Settings -->Job Settings-->Implicit context load

Then set that to be on (tick the Implicit tContextLoad box). This will set all of your jobs to retrieve their contexts implicitly

[Anonymous]

Thank you rhall_2_0. I did that and it worked

However, I still not add to containers for host,port,database, username and password in every job to hold the implicit connection details, right?

[Anonymous]

The context variables supplied using the implicit context load will overwrite any hard coded context variables. If you want to remove those values you will have to remove them manually, but there is not need to do it immediately, since they will not be used.

[Anonymous]

Hi, I did not remove the already existing connection details. I have set up the implicit connection at the project level

The scenario is, I have hardcoded contexts for both mysql and sqlserver. Let's say the hardcoded mysql connection points to database db1(which has the table loadcontext which is used for implicit connection), but, I want the data from sql server to move to db2 database whose connection details comes from implicit context

The details are as below

Now, the existing contexts are as below

Now when I run the job, I get the error as follows

Starting job job1 at 15:26 08/11/2017.

[statistics] connecting to socket on port 3396
[statistics] connected
Table 'devwarehouseqa.loadfromqa' doesn't existTable 'devwarehouseqa.loadfromqa' doesn't existTable 'devwarehouseqa.loadfromqa' doesn't existTable 'devwarehouseqa.loadfromqa' doesn't existTable 'devwarehouseqa.loadfromqa' doesn't existTable 'devwarehouseqa.loadfromqa' doesn't existTable 'devwarehouseqa.loadfromqa' doesn't existTable 'devwarehouseqa.loadfromqa' doesn't exist
[statistics] disconnected
Job job1 ended at 15:26 08/11/2017. [exit code=0]

I am unable to figure this out

[Anonymous]

You cannot have hardcoded values in ANY of your database connection details....apart from your Implicit Context settings. All of your other connection details must be supplied by context variables which are supplied values using the Implicit Context Load process.

[Anonymous]

Ok. In the implicit connection details I have the accomodation for only one connection to one database. How do I get the connection details for sql server and mysql from the implicit connection? What must I add in the contexts tab of the job?

The table from which the connection details are fetched have the following columns key,value, dbtype. dbtype has value sqlserver for sqlserver connection and mysql for mysql connection. So, I have used in the where clause of the implicit connections tab, dbtype = 'mysql', where do I specify dbtype = 'sqlserver'

The where clause in the implicit connections tab has an accomodation for only one connection, how do I get it for more than one database?

Thanks

[Anonymous]

You have misunderstood how it works. The Implicit Context Load can get context variable values from a file or a database. You are using a database for this. That database just holds the context variable values. So, if your context variables are.....

context.myContext1

context.myContext2

context.myContext3

context.myContext4

context.myContext5

......then you need to have their values stored in a database table (with columns "key" and "value" as well as any others you may want to use for your WHERE Clause).

In your table, you will have the values stored for the variables above, like below....

key                    value

myContext1      This

myContext2      Is

myContext3      The

myContext4      Example

myContext5      Done

You would then set up your database connections to be configured by the values in your conetxt variables database table. This means that the ONLY database that needs to be permanent is your context variable database. You can even make that dynamic with a bit of code......but that is much more complicated and not worth going into now