Using Context-Variables in DB-Connections?

[kleinmat]

Hi everyone,
I am a Talend newbie who is just trying to build a little job connecting to an Oracle DB to pull out some data. Basic stuff 🙂
So here's what I have done since this morning:
1. Created a project
2. Created a context group in the metadata
3. Created a context for each system environment that job will later run (Test, Production etc.)
4. Created a set of suitable context variables (especially for the DB connection)
What I have been stuck with for the past few hours and could not resolve by reading manuals is this:
I created a DB-Connection in the Metadata-section of Talend, chose "Oracle with SID" as type and tried to use the already existing context variables.
But Talend wouldn't let me.
When I entered the connection data of my test environment directly into the text fields, I was successfully able to test the connection.
But when I then replaced the text field content with variables like
context.username
context.password
nothing worked anymore. It was not even possible to enter
context.port
into the "port" field
All I found was the option to "export as context". But when I did that, it would create a whole new context group, create new variable names and everything - and would disregard all the settings in "my" context group that was already created.
I find that very confusing.
My 2 questions to you are:
1. Have I generally set my project up properly? (Using global context groups, db-connections in the metadata etc.)
2. Is it even possible to use pre-existing context variables in a db connection? (If so: how?)
Thanks
Matt

[Anonymous]

hi kleinmat,
what do you mean by "Talend don't let you".
You"re not able to write in filed or it give you any errors when trying to run the job ?
regards
laurent

[Anonymous]

Hello, what you're going to want to do is configure the metadata to connect to your test environment, from there you press the "Export as context" button to convert the connection parameters into context variables for the metadata values. See the attached screen shots for what this will look like.

[kleinmat]

Thank you, kzone, for your quick reply.
What I meant is
a) that it is technically not even possible to enter something like context.port into the port text field (try it, it doesn't work) 🙂
b) even if I enter the port as number but replace any other field with the name of the context variable, it does not work (as in "does not create a connection").
Apparently, Talend does not use the context variables.

[kleinmat]

Hello, what you're going to want to do is configure the metadata to connect to your test environment, from there you press the "Export as context" button to convert the connection parameters into context variables for the metadata values. See the attached screen shots for what this will look like.

Thank you for your quick reply.
That is what I did - but that is not what I want.
Because the mechanism you showed does not make use of pre-existing context variables but creates a whole new context group which does not even know the contexts I have already defined.
If this "export to context" button is really the only way to deal with this, I am either not understanding the underlying principle of Talend contexts, or Talend does not use the context mechanism in an appropriate and intuitive fashion here.
Thank you
Matt

[willm1]

Hi kleinmat - after converting your DB connections to contexts or a context group, your new contexts are stored in the Talend repository. Next, you use the contexts on your DB input and output drums... To do this, you'd type those values in your DB properties (see screenshot - property set to built-in).
For your port in particular, make sure the data type of the context.port variable is integer...

[kleinmat]

Hi kleinmat - after converting your DB connections to contexts or a context group, your new contexts are stored in the Talend repository. Next, you use the contexts on your DB input and output drums... To do this, you'd type those values in your DB properties (see screenshot - property set to built-in).
For your port in particular, make sure the data type of the context.port variable is integer...

Hi willm, thank you very much for your reply. I have not actually taken a physical component to create my connection. Instead, I have tried to create a db connection in the metadata section of Talend. (see screenshot)
Is that the wrong way to approach the thing I am trying to do?
Thanks
Matt

[Anonymous]

Hi kleinmat. No it will work. But if you drag and drop your connection to a job I guess you will not get automatically the context var group also added to you job, this must be done manually - but that is not a big deal.

[kleinmat]

Hi kleinmat. No it will work. But if you drag and drop your connection to a job I guess you will not get automatically the context var group also added to you job, this must be done manually - but that is not a big deal.

Thanks for your reply. What do you mean by "it will work"? For me, it does not work.
When I create a DB connection (I used "Oracle with SID") and entered the information of my test environment, clicking "Check connection" results in a positive response.
But when I replace even only 1 single entry with the context variable I previously created (e.g. context.source_username), clicking "check" results in an error message.
And when I try to enter context.port into the port field, it does not even let me enter anything past context (pressing the . does not result a . showing up because apparently there is an input filter for that field)
Do I misunderstand the concept behind Talend in this scenario?
Or is this really not working in Talend at all?
Or am I doing something wrong?
Thank you
Matt

[Anonymous]

i have same situation using context variable in my database connection but it doesn't work.

error:  Connection failure. You must change the Database Settings.
java.lang.RuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'context.JV_UAT_Server.userName'. ClientConnectionId:67d9089e-7a71-49cd-81e3-1390844e4a89
at org.talend.core.model.metadata.builder.database.JDBCDriverLoader.getConnection(JDBCDriverLoader.java:204)

First figure show a successful test connection without using context variables.

2nd show the context created.

Lastly the failed connection test using context variables.

Would you know what I may did wrong? 

dbTestSuccess.jpg
dbTestConnectionFailed.jpg
contextServerConnection.jpg