topic Re: Dynamic query in global variable to be used in tMySQLRow in Talend Studio

Dynamic query in global variable to be used in tMySQLRow

Anonymous — Sat, 16 Nov 2024 03:27:05 GMT

Hi everyone!

I am generating a dynamic query in tjavaFlex and storing it into global variable.

It is similar to this :globVar = insert into test(id,name) values('"+row1.line.split(",")[0]+"','"+row1.line.split(",")[1]+"')

When I use this global variable in tmysqlrow's query tab( for which the input is row1.line), it inserts extra single quote at the start and end which is creating an issue for me.

If i manually pass the global variable with double quotes at start and end, it works fine.

Any help guys? I am even storing the globVar in a file. and the query is generated fine.

Thanks in advance.

Ravinder

Re: Dynamic query in global variable to be used in tMySQLRow

Anonymous — Mon, 27 Jan 2020 13:48:52 GMT

Can you show us exactly what you use to set this in the tJavaFlex and exactly what you use in your DB component? Giving us a pseudocode version isn't enough to identify the issue I'm afraid.

Re: Dynamic query in global variable to be used in tMySQLRow

Anonymous — Mon, 27 Jan 2020 13:56:44 GMT

Part1: So there is delimited file with header is "id,name"

tjavaFlex reads the header and creates a query as : insert into test(id,name) values('"+row1.line.split(",")[0]+"','"+row1.line.split(",")[1]+"')

this query is saved in a globalMap.

Part2. tmysqlrow reads input file rows (row1) which has just one column for e.g : 1,john, and i have used globalMap variable in the query tab. Please see the screenshot.

It inserts "'"+row1.line.split(",")[0]+"' and '"+row1.line.split(",")[1]+"' in the table.

But if i copy the generated query within double quotes, it populates actual values in the table i.e. 1 , John.

Capture.JPG

Re: Dynamic query in global variable to be used in tMySQLRow

Anonymous — Mon, 27 Jan 2020 14:02:12 GMT

I see. You cannot store Java code inside a Java variable. It will be treated as a Java String. This is what is happening in your job. What is wrong with the method of adding the code directly to the DB component? Does this break a requirement somewhere?

Re: Dynamic query in global variable to be used in tMySQLRow

Anonymous — Mon, 27 Jan 2020 14:06:01 GMT

I have to use a for loop in tjavaFlex. if there are 10 columns, query will be created for that, if 100, then same will happen for that.

tjavaFlex is creating this query.

Can you suggest any alternate way? Can i pass java code in tmysqlRow?

Re: Dynamic query in global variable to be used in tMySQLRow

Anonymous — Mon, 27 Jan 2020 14:27:34 GMT

You are inserting data, so why can't you generate the insert statements with the values populated in your tJavaFlex?