Five Stars

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Twelve Stars

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

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.

 

Rilhia Solutions
19 REPLIES
Twelve Stars

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

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

Rilhia Solutions
Five Stars

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

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

Twelve Stars

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

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

Rilhia Solutions
Five Stars

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

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?

 
 
 

 

Twelve Stars

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

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.

Rilhia Solutions
Five Stars

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

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

pic1.png

 

Now, the existing contexts are as below

pic2.png

 

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

 

Twelve Stars

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

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.

Rilhia Solutions
Five Stars

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

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

 

Twelve Stars

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

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

Rilhia Solutions
Five Stars

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

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

Five Stars

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

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

Twelve Stars

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

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

Rilhia Solutions
Twelve Stars

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

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

Rilhia Solutions
Five Stars

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

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

Five Stars

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

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?

 

pic5.png

 

 

Twelve Stars

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

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.

Rilhia Solutions
Five Stars

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

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

Twelve Stars

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

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.

 

Rilhia Solutions
Five Stars

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

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

 

Your patience much appreciated !!