Execute Command denied to User using variable

Four Stars

Execute Command denied to User using variable

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 :  

 

image.pngWhen 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 Smiley Wink

Community Manager

Re: Execute Command denied to User using variable

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).

Four Stars

Re: Execute Command denied to User using variable

Ok so I changed my routine a little :

 

image.pngI 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

 

Community Manager

Re: Execute Command denied to User using variable

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.

Four Stars

Re: Execute Command denied to User using variable

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 : 

 

image.pngMy 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

Four Stars

Re: Execute Command denied to User using variable

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 ? 

Highlighted
Four Stars

Re: Execute Command denied to User using variable

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')+ ")

Community Manager

Re: Execute Command denied to User using variable

Have you tried my suggestion from here: https://community.talend.com/t5/Design-and-Development/Execute-Command-denied-to-User-using-variable...

 

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.

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch