topic Re: [resolved] context values for sql query in Talend Studio

[resolved] context values for sql query

Anonymous — Wed, 04 May 2016 21:25:45 GMT

Hi.
I want use some context values for sql query.
Query will be something like: select * from table where value in (context.values);
Those context values are stored in a txt file and they are several, like: monday, friday, saturday.
the query returns no values, because it's looking for context like a string.
How can I make i read context like separated values?
Or is there other better solution?

Re: [resolved] context values for sql query

Anonymous — Fri, 06 May 2016 16:28:13 GMT

Hello!
Anyone?
Thank you!

Re: [resolved] context values for sql query

Anonymous — Fri, 06 May 2016 16:31:19 GMT

In your t{DB}Input component, you will notice that the SQL is within quotes. It is basically a Java String. All you need to do is build your query (its good practice to name your columns explicitly by the way) and then concatenate your context variable to the query like below....

"SELECT Col1, Col2, Col3 FROM mytable WHERE talend_context =" +context.yourVal

If you are concatenating a String value, then you will need to add single quotes, for example....

"SELECT Col1, Col2, Col3 FROM mytable WHERE talend_context ='" +context.yourVal +"'"

As I said, all you are doing is building a query String in Java. So as long as your String results in a legitimate SQL query (and your columns match your schema), it will work.

Re: [resolved] context values for sql query

Anonymous — Mon, 09 May 2016 16:01:03 GMT

Hi.
Thanks for your answer. I saw an old post from you referring this, but I still have the problem.
My query is: Select city, country from companies where city in ('lisbon','london') - This works in a DBA Manager.
I have the same query like this "Select city, country from companies where city in" +context.city+
on text file that hold the context info, I have: lisbon','london this does not work beacause it's a string, so it's looking for the exact value: lisbon','london
If I put in the text file: 'lisbon','london', it throws an error "remove the quotes"...
So Any help will be appreciated!

Re: [resolved] context values for sql query

Anonymous — Mon, 09 May 2016 18:30:24 GMT

the example you've been provided with shows you how you construct the statement.
you need to make sure that you're constructing valid SQL.
In this instance, your context value needs to be set to: - "('lisbon', 'london')".
In your question above, you have no space after "in". You need it.
You just need to make sure that you're creating valid SQL.

Re: [resolved] context values for sql query

Anonymous — Wed, 11 May 2016 17:31:55 GMT

Hi.
Now it's ok and I "got the picture".
It works like a string replacement.
Thank you!!