Strange return in MySQL queries

Question

Got a little issue here. I'm periodically making an automated update of currencies in a MySQL table. Part of the generated query is :

UPDATE ara_curr SET 
curr_xchange=REPLACE(CONVERT(CONVERT('1.60739',DECIMAL(9,5))/CONVERT('10000',DECIMAL(9,5)),DECIMAL(9,5)),'.',','),
curr_rev_xchange=REPLACE(CONVERT('6221.27',DECIMAL(9,5)),'.',',') 
WHERE curr_name='IDR';

I get the values from a online service. However this query returns

Out of range value for column (null) at row 1

However if I break it down the conversions return these

REPLACE(CONVERT('6221.27',DECIMAL(9,5)),'.',',') = 6221,27000
CONVERT('1.60739',DECIMAL(9,5)) = 1,60739
CONVERT('10000',DECIMAL(9,5)) = 9999,99999
CONVERT(CONVERT('1.60739',DECIMAL(9,5))/CONVERT('10000',DECIMAL(9,5)),DECIMAL(9,5)) = 0,00016

And if I try to run the query directly with those values e.g.

UPDATE ara_curr SET curr_xchange='0,00016', curr_rev_xchange='6221,27000' WHERE curr_name='IDR';

It runs perfectly OK!

Any ideas about this?

Answer 1

What datatypes are curr_xchange and curr_rev_xchange?

If i run your query it returns the result as binary data. Maybe you can try this modification with a CAST around the results.

UPDATE ara_curr SET 
curr_xchange=CAST(REPLACE(CONVERT(CONVERT('1.60739',DECIMAL(9,5))/CONVERT('10000',DECIMAL(9,5)),DECIMAL(9,5)),'.',',') AS CHAR),
curr_rev_xchange=CAST(REPLACE(CONVERT('6221.27',DECIMAL(9,5)),'.',',') AS CHAR) 
WHERE curr_name='IDR';

Answer 2

Try using CAST instead of CONVERT:

UPDATE  ara_curr
SET     REPLACE(CAST(CAST('1.60739' AS DECIMAL(9, 5)) / CAST('10000' AS DECIMAL(9, 5)) AS DECIMAL(9, 5)), '.', ',') AS curr_xchange
        , REPLACE(CAST('6221.27' AS DECIMAL(9, 5)), '.', ',') AS curr_rev_xchange
WHERE   curr_name = 'IDR';

Also, you may run into some trouble casting 10000 as a DECIMAL(9, 5) as it exceeds the allowed range. You may want to cast that to DECIMAL(10, 5) instead.