topic Re: Execute Command denied to User using variable in Talend Studio

Execute Command denied to User using variable

Anonymous — Mon, 11 Jun 2018 15:42:56 GMT

Hi everyone,

This is the first time I poste here, i'm a beginner at Talend.

I need to write 2 queries, one depending on the other result.

-My first query displays 3 columns, I need this to create conditions of my sum for each row, for my second query.

I create 3 variables with a tjavarow :

globalMap.put("codeOutlet",input_row.code);
globalMap.put("startTime",input_row.debut);
globalMap.put("endTime",input_row.fin);

I make a new connection to my database to write my second query in my input, and I use my 3 variables to make conditions in my where :

"(
select sum(b.value)
from myemoneypursedb.mye_voucher_sale b
where CONCAT(b.company, \"_\", b.pdv) = globalMap.get(codeOutlet) AND b.selling_time between globalMap.get(startTime) AND globalMap.get(endTime))"

Here is my process :

When I execute, I get this error message : execute command denied to user 'jeremy_m'@'%' for routine 'globalMap.get'

I don't know what to do.

I tried granting my access in mysql but it didn't work.

Do youhave any ideo ?

Thanks a lot

Re: Execute Command denied to User using variable

Anonymous — Tue, 12 Jun 2018 09:22:37 GMT

Your SQL query is a Java String. You need to build that String using the values held by your globalMap. What you are doing is sending a query which holds the String "globalMap" in it. I'm not sure if this is totally correct (as I do not know your data), but this will be closer to what you need.....

"(select sum(b.value)
from myemoneypursedb.mye_voucher_sale b
where CONCAT(b.company, \"_\", b.pdv) = '"+((String)globalMap.get(codeOutlet)) + "' AND b.selling_time between " + ((String)globalMap.get(startTime)) +" AND " + ((String)globalMap.get(endTime)) + ")"

You are dealing with dates in your globalMap as well. I am assuming you have formatted them as they will be required as a String in your query. If not, you will have to take that into account as well.

The best way to solve this is to build your query in a tJava and then print it to the console using a System.out. Then test it in your query analyser (TOAD, MSSQL Management Studio, etc).

Re: Execute Command denied to User using variable

Anonymous — Fri, 15 Jun 2018 11:21:19 GMT

Ok so I changed my routine a little :

I created my global Var with a tflowToIterate and it seems to work because i'm able to display the values into a java system.out.

Now I'd like to use these variables into my sql query.

Here is my code :

"(select sum(b.value)
from myemoneypursedb.mye_voucher_sale b
where CONCAT(b.company, \"_\", b.pdv) = '"+((String)globalMap.get(codeOutlet)) + "' AND b.selling_time between " + ((java.util.Date)globalMap.get(startTime)) +" AND " + ((java.util.Date)globalMap.get(endTime)) + ");"

I have an error : endTime is not recognized as a variable when I put the ";" at the end of the query.

When I remove It, and add It after the final double quote I have this message : The method tDBInput_1Process(Map<String,Object>) is undefined for the type Union.

My objective is still the same, I want to use my global Var as conditions for my where, in my sqlinput.

My 3 variables give me a store and a Time range.

I want to iterate through my sale table and for each line, check If my transaction happens on the timerange defined in my variable to sum It.

Do you know how I can settle It ?

Thx a lot

Re: Execute Command denied to User using variable

Anonymous — Fri, 15 Jun 2018 12:32:23 GMT

First of all, you do not need to put a semi colon in queries, in fact they will break your query. So leave the semi-colon out always.

Second, your globalMap keys MUST have quotes around them unless you are passing a Java String variable. In your case you are supplying a literal, so your query needs to look like this....

"(select sum(b.value)
from myemoneypursedb.mye_voucher_sale b
where CONCAT(b.company, \"_\", b.pdv) = '"+((String)globalMap.get("codeOutlet")) + "' AND b.selling_time between " + ((java.util.Date)globalMap.get("startTime")) +" AND " + ((java.util.Date)globalMap.get("endTime")) + ")"

However, the above will give you an error because you have to format your Dates in SQL according to your database. You are trying to implicitly convert a Java Date object to a String in the correct format for your database. That will not work. Do you know how to format dates in a query for your database? Do you have a hardcoded version of this query that will work in a query analyser? That way, it will be easier for people to help you without having to do too much work.

Re: Execute Command denied to User using variable

Anonymous — Fri, 15 Jun 2018 13:08:00 GMT

So here is my first query, source of all my csv. :

"SELECT
`mye_voucher_sale`.`selling_time`,
`mye_voucher_sale`.`company`,
`mye_voucher_sale`.`pdv`,
`mye_voucher_sale`.`street`,
`mye_voucher_sale`.`cp`,
`mye_voucher_sale`.`city`,
`mye_voucher_sale`.`owner`,
`mye_voucher_sale`.`serial_number`,
`mye_voucher_sale`.`product`,
`mye_voucher_sale`.`outlet`,
`mye_voucher_sale`.`value`
FROM `mye_voucher_sale`
WHERE DATE(`mye_voucher_sale`.`selling_time`)=CURRENT_DATE()
ORDER BY `mye_voucher_sale`.`selling_time` DESC"

I think the format of my selling_time is correct.

If not, I know I can use DATE_FORMAT() in my query to redefine It.

After this query I use a tMap :

My two dates have a Date type, so for me, I don't see any date format problem.

After these steps, I output and input .csv all these things.

Maybe using these fonctions alterates my date format.

Thx

Re: Execute Command denied to User using variable

Anonymous — Fri, 15 Jun 2018 13:31:26 GMT

I think I get It, I need to format my string to a date type in my last query using "date(" before my "+ ((java....,

Is that correct ?

Re: Execute Command denied to User using variable

Anonymous — Fri, 15 Jun 2018 13:50:45 GMT

I tried this but no success :

(select sum(b.value)
from myemoneypursedb.mye_voucher_sale b
where CONCAT(b.company, \"_\", b.pdv) = '"+((String)globalMap.get("codeOutlet")) + "' AND b.selling_time between TO_DATE(" + ((String)globalMap.get("startTime")) +", '%Y-%m-%d %H:%i:%s')
TO_DATE(" + ((String)globalMap.get("endTime")) +", '%Y-%m-%d %H:%i:%s')+ ")

Re: Execute Command denied to User using variable

Anonymous — Fri, 15 Jun 2018 14:31:07 GMT

Have you tried my suggestion from here: https://community.talend.com/t5/Design-and-Development/Execute-Command-denied-to-User-using-variable/m-p/123542/highlight/true#M77700

Look at the bottom of the post. My suggestion was to use a tJava to print your computed SQL to the output. Then copy it and use it in a query analyser. You will see why it is not working. FYI it is because you are now trying to convert an object of class Date to a String implicitly. You will probably see a reference to memory location, if that.

Basically, if your globalMap holds a Date object, you need to convert it to a String in Java. Then you need to wrap the result of that in whatever code is required by your DB to interpret your newly formatted String as a Date in your database.