tMysqlInput + context variables + IN condition

Anonymous — Tue, 30 Aug 2016 15:33:30 GMT

Hello,
Trying to use values from a query in a context variable, which is then used within an IN condition in another query. There are a lot of examples using equality conditions which seem to work fine, but when using an IN condition things aren't working as expected.
SETUP:
A string context variable called "excludes" is created and set to a benign value "1" (if value is included in final query it will have no impact). A tMysqlInput_1 component is run and returns:
1234
2345
3456
which is then piped to tDenormalize which converts it to "1234,2345,3456" and then is set to context.excludes in a tJavaRow. The context values are confirmed by placing the following in the tJavaRow component:

System.out.println(context.exclude);
context.exclude=input_row.exclude;
System.out.println(context.exclude);

When the job runs the values returned are:

1
1234,2345,3456

So far so good, we have the correct context values being set up to this point. The next step in the job is to query a different database using tMysqlInput with the following:

SELECT *
FROM tableA
WHERE fieldA NOT IN ( "+context.excludes+" )
LIMIT 10

ISSUE A: The results returned include rows which should be excluded.
ISSUE B: if the context variable is set to something like:

context.exclude="("+input_row.exclude+")";

and the query is adjusted to:

SELECT *
FROM tableA
WHERE fieldA NOT IN "+context.excludes+"
LIMIT 10

this throws an error in MySQL.
In the query, fieldA is an INT so not sure how to pass a comma separated list of integers into the query. The query has also been verified by running it in MySQL with the values of the context variable so the query is correct.
Any help is appreciated as always.

Re: tMysqlInput + context variables + IN condition

Anonymous — Tue, 30 Aug 2016 16:02:42 GMT

After some further testing, it would seem that

SELECT *
FROM tableA
WHERE fieldA NOT IN ( "+context.excludes+" )
LIMIT 10

is coming out as

SELECT *
FROM tableA
WHERE fieldA NOT IN ( '1234,2345,3456' )
LIMIT 10

and not

SELECT *
FROM tableA
WHERE fieldA NOT IN ( 1234,2345,3456 )
LIMIT 10

which so far is the only way I can explain how the rows which should be excluded are actually being included.

Re: tMysqlInput + context variables + IN condition

cterenzi — Tue, 30 Aug 2016 21:38:50 GMT

There is a global variable containing the query string generated by a database input component. Try printing it with a tJava so you can see exactly what's being sent to MySQL.
It would be something like ((String)globalMap.get("tMysqlInput_1_QUERY"))

topic tMysqlInput + context variables + IN condition in Talend Studio

tMysqlInput + context variables + IN condition

Re: tMysqlInput + context variables + IN condition

Re: tMysqlInput + context variables + IN condition