tELTPostgresqlMap and context variables

[Anonymous]

Hi,

im using Talend Open Studio for Big Data V7.1.1

I got a tELTPostgresqlMap which is getting data from some tELTPostgresqlInputs.

I want to join these inputs with the help of context variables which get set before.

Also i want to use a context variable for where clauses.

The context variable always gets escaped and queries break.

Is there any way to actually use context variables as column values or for joins/where ?

[Anonymous]

Hi,

   Did you print the output of context variables and verified whether they are having values loaded before running this step?

   I would also suggest to give the column values directly without unwanted escape characters and double quotes. 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[Anonymous]

Hi,

the values are set and correct.
I'm not Sure what you mean with

I would also suggest to give the column values directly without unwanted escape characters and double quotes.

I did not quote anything

[Anonymous]

Hi,

    You have currently given the details as shown below.

Could you please try as shown below?

"SELECT column1,"+context.day+" FROM
 test t1
WHERE t1.day_column > "+context.day

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[Anonymous]

Hi,

not sure how to do this as the context in a tPostgresqlmap always gets escaped by talend. 😕

Also if in the final select the context value is in double quotes it is handled as column name and wont work. 

I got it working for string fields by using single quotes but its not working on string fields

invalid input syntax for integer: ""123432423456""

In the select the field looks like this:

'"5324324678"'

[Anonymous]

Hi,

   Apologies for coming back late as I was busy with my real job and could not do data setup for this scenario.

   I have created a database in AWS MySQL database where I was able to extract the data with minor changes in the query. Please refer the below screen shots for details.

Job execution output

Context setup

string to date conversion of context variable

Unfortunately, in my last post, I had overlooked the conversion of data from String to date. Once you convert the context variable to date format, you will be able to execute the query.

"SELECT id, name, day_column,STR_TO_DATE('"+context.day+ "', '%d/%m/%Y') as day 
FROM test
WHERE day_column >STR_TO_DATE('"+context.day+ "', '%d/%m/%Y')"

Please note that I am using MySQL for verification. So I have used MySQL functions for String to date conversion. Please refer the below link to get the corresponding PostgreSQL functions and you will have to use to_date(text, text) function for the same.

https://www.postgresql.org/docs/8.1/functions-formatting.html

Hope I have answered your query. Please remember to mark the topic as resolved before signing out for the benefit of our Talend community and Kudos are also welcome 🙂

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂