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]

Thank you rhall_2_0.  Its working !!!

I just have one more question though. I am able to query the database to load the mysql connections implicitly.

How do I load the sqlserver connection implicitly too? The job is tmssqlimput -- tmysqloutput.

In the implicit context tab, there is no place where I can enter sql server connection details

Thanks

[Anonymous]

Thank you rhall_2_0.  Its working !!!

I just have one more question though. I am able to query the database to load the mysql connections implicitly.

How do I load the sqlserver connection implicitly too? The job is tmssqlimput -- tmysqloutput.

In the implicit context tab, there is no place where I can enter sql server connection details

Thanks

[Anonymous]

You need to look at the error message you posted. There is something wrong with your implicit context load config OR the values in your database. Can you show your database table holding the context variables and your implicit context load settings

[Anonymous]

The implicit context load screen is for configuring ONLY the connection to the database holding your context variables. ALL of your context variables need to be in your database holding context variable values

[Anonymous]

Apologies rhall_2_0.

The reply to you was pasted by chance.All the errors are fixed. 

However, I have just one question like I have told earlier

I am able to query the database to load the mysql connections implicitly.

How do I load the sqlserver connection implicitly too? The job is tmssqlimput --> tmysqloutput.

In the implicit context tab, there is no place where I can enter sql server connection details

[Anonymous]

Ok. My configuration table has the following rows. In the where if I specify dbtype = 'mysql', I get the mysql configuration details. Where do I specify dbtype = 'sqlserver' to get the sql server connection details?

[Anonymous]

Oh I see. You can't do that. You need to have context variables specified for each database used in your job. If you are using 3 databases in your job, you will need 3 sets of context variables describing the connection. You are using your Implicit Context Load Where Clause incorrectly as well. You need to have your Where Clause filter on an environment variable, not on your database type.

[Anonymous]

When you say environment variable, is it the system environment variable? Even if I define separate contexts for 3 databases, how do I load it implicitly?

The where clause is not the where clause for an sql query?

Request you to give me a example of this

Thanks in advance

Rathi

[Anonymous · Accepted solution]

You are making this more complicated than it needs to be.

If you have 3 databases, you need 3 lots of context variables.

DBHost1
DBUsername1

DBPasssord1

DBHost2
DBUsername2

DBPasssord2

DBHost3
DBUsername3

DBPasssord3

Your environments relate to your combinations of the 3 databases. You might have DEV, TEST and PROD. You will not use the same 3 databases for each environment. However, you do not need to have a n environment (or a Where Clause).

Your implicit context load query is essentially the below without a Where Clause....

"Select key, value from youContextTable"

This will return ALL context variables. So it should return values for all of the above contexts for the 3 databases. 

In your job you use DB1 context variables for your Source table, DB2 context variables for Target table and DB3 context variables for your lookup database (for example).

The implicit context load settings ONLY point to the database which hold your context variable values. You can store thousands of context variables in that.

[Anonymous]

Thank you rhall_2_0. That solved all my issues with regard to this question

Your patience much appreciated !!