Four Stars

Configuring a DB Connection from OS Environment variables (searching for the best practice)

Hello,
we are searching for the best practice in using enviroment variables to set a DB Connection at runtime in jobs Talend.
We have experimented all these solutions:
1) setting context parameter for a connection to a DB using --context_param GDB01_IDWA_Login="dbusername" --context_param GDB01_IDWA_Password="dbpwd" and so on for host, port, dbname ....
But this solution is not so usable and require specific configuration for each job of the project.
2) Using a TJava component to set the context variables of the connection from the environment variable by ystem.getenv()
But this solution imply the presence of the TJAVA in each job of the project, and if you have to make some change, you must modify every tjava.
3) Set a dymanic configuration of a connection directly by the enviroment
This is the very useful because every change you made is propagated to every job of the project
We have succesfully configured a DB connection to retrieve at runtime the credential Login typing in the field this: ""+System.getenv("DBUSR")+""
and this for the field password: ""+System.getenv("PSWUSR")+""
I want to do the same with the others parameters: Host, port, database.
The problem appear within the database field, because it is not possible to obtain a correct url.
In detail setting the field like this HOST = ""+System.getenv("HOST_G01")+"" PORT : ""+System.getenv("PORT_G01")+"" Database = +""+System.getenv("DB_G01")+""
and appliyng the change to all job, when I go to see the connection parameter in an table input object,
the database field content appear like this: "\"\"+System.getenv(\"DB_G01\")+\"\"" and the final connection URL is INVALID and generate a runtime error like this:

jdbc:db2://GDB01SRV:3700/+""+System.getenv("DBNAME")+"" . ERRORCODE=-4461, SQLSTATE=42815

The code in the field database is NOT managed like it is in the others.  

If I type directly in the field database of the object the code System.getenv("DB_G01"), possible only in integrated mode, it work fine,
as shown in the attached file.

I have tried every type of escape trick and format of string in the connection configuration, but the result not change.

Is it Possible that only the field database of the connection setted like the System.getenv() is not correct interpretaded/translated in the objects in the repository mode?

Thanks to every one that can help me to solve this issue or if you can suggest other methods to reach the target

bye

Riccardo.

3 REPLIES
Fifteen Stars

Re: Configuring a DB Connection from OS Environment variables (searching for the best practice)

This is easily solved and you're most of the way there. Use context variables for all your connections, etc, and populate those context variables using the Implicit Context Load functionality. You can actually use system environment variables to point to your file /database where your context variables will be stored. I talk about this briefly here: https://community.talend.com/t5/Design-and-Development/Context-Variables/m-p/51733

Another page to look at is this one: https://help.talend.com/reader/mhqCkTBnin7IXmJBUJoocQ/0lPtF5eayDI~33QdLfRY~A
Rilhia Solutions
Four Stars

Re: Configuring a DB Connection from OS Environment variables (searching for the best practice)

Thank's for your indication.

Your solution (that certantly represent a best practice) is ok for using a readable file to contain the values of the variables and load a context.

 

Cause security policy, in our environment we can not create and then access in reading a file that contain credential versus the Databases.

We can only execute, before the talend job, a specific script (made by the administrators with only execution grant) that export the enviroment variables for the connections, and then retrive and use those variables in the talend jobs.

 

What we need, to complete the puzzle, is only the DBNAME from the system.getenv() to configure the connection.

Thank you for any further indication.

bye

Riccardo

 

 

 

 

 

 

 

 

 

 

Four Stars

Re: Configuring a DB Connection from OS Environment variables (searching for the best practice)

Can anybody help me?

thank's