topic Re: Using Context-Variables in DB-Connections? in Talend Studio

Using Context-Variables in DB-Connections?

kleinmat — Thu, 27 Mar 2014 13:52:46 GMT

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

Re: Using Context-Variables in DB-Connections?

Anonymous — Thu, 27 Mar 2014 14:04:22 GMT

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

Re: Using Context-Variables in DB-Connections?

Anonymous — Thu, 27 Mar 2014 14:24:54 GMT

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.

Re: Using Context-Variables in DB-Connections?

kleinmat — Thu, 27 Mar 2014 15:21:07 GMT

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.

Re: Using Context-Variables in DB-Connections?

kleinmat — Thu, 27 Mar 2014 15:24:01 GMT

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

Re: Using Context-Variables in DB-Connections?

willm1 — Thu, 27 Mar 2014 15:31:03 GMT

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...

Re: Using Context-Variables in DB-Connections?

kleinmat — Thu, 27 Mar 2014 15:42:32 GMT

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

Re: Using Context-Variables in DB-Connections?

Anonymous — Thu, 27 Mar 2014 20:03:44 GMT

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.

Re: Using Context-Variables in DB-Connections?

kleinmat — Fri, 28 Mar 2014 15:16:25 GMT

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

Re: Using Context-Variables in DB-Connections?

Anonymous — Wed, 27 Feb 2019 01:42:38 GMT

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

Re: Using Context-Variables in DB-Connections?

Anonymous — Wed, 27 Feb 2019 19:54:32 GMT

Contexts are initialized at run time. I do not think you can add that to the Metadata section & test it out because they don't get initialized during that test. For testing the connection you will need the absolute values.

However, you can add that to the metadata schema, use that schema as an input/ output and then test it out. It should work definitely.

Re: Using Context-Variables in DB-Connections?

Anonymous — Tue, 27 Aug 2019 02:32:44 GMT

Hi all,

While on the subject on DB connections and contexts...

Lets say I created 2 distinct Oracle connections which I exported as context.

Is it possible to have a input parameter which would allow me to switch the connection on a given job?

For instance, I have several databases that contain the same table name which I have to extract data. I would like to avoid recreating the same job for each databases because if the table changes, it will impact both jobs.
My goal would be to fetch data both databases by running the same job on using a parameter that would toggle source DB connection. Is this possible? Could someone provide the steps and/or screen prints as example?

Thanks,