MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:LAFONTAINE Julien - LYO Date:February 24 2005 9:49am
Subject:RE: Problem with SUM and DECIMAL field
View as plain text  
Hi Mark,

Thank you for your help.

I have the same problem when I execute the query with the MySQL client on my
AIX server :

mysql> select sum(AMFTPF) from DWH_AMF;

+-------------+
| sum(AMFTPF) |
+-------------+
|    12000,00 |
+-------------+

1 row in set (0,03 sec)

The problem occurs only when I use an operator like SUM, AVG, MAX, MIN...

mysql> select AMFTPF from DWH_AMF LIMIT 1;

+--------+
| AMFTPF |
+--------+
|  15.00 |
+--------+

1 row in set (0,01 sec)                     


Moreover the local on the client (swing GUI) is already set depending on the
preferences of the user that's connected.
Hence I cannot force it.

I don't think the problem is with Connector/J. I thing the getBigDecimal
method fail because of the comma returned by MySQL. But to me the MySQL
server shouldn't be returning a comma in that case.

It seems like a MySQL bug to me, but maybe I'm doing something wrong.

Regards,

Julien

-----Message d'origine-----
De : Mark Matthews [mailto:mark@stripped]
Envoyé : lundi 21 février 2005 16:24
À : LAFONTAINE Julien - LYO
Cc : mysql@stripped
Objet : Re: Problem with SUM and DECIMAL field


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

LAFONTAINE Julien - LYO wrote:
> Hi everyone,
> 
> I've recentely upgarded my servers from MySQL 3.23.49 to MySQL 4.1.9.
> 
> Now I have a problem when using the SUM operator on DECIMAL field.
> 
> The value returned by the SUM operator when used with DECIMAL field has a
> coma (,) as decimal separator while it used to be have a dot (.) . If I
> query my table to display the DECIMAL fields (SELECT * FROM ...)  I get a
> dot as decimal separator as expected.
> 
> This doesn't look like a big issue but it prevents Connector/J from
> retieving the data properly. Connector/J can't parse the value of the
field
> as it's expecting a dot as decimal separator.
> 
> Here is the stack trace :
> 
> java.sql.SQLException: Bad format for BigDecimal '12660,95' in column 1( .
> () . SUM(AMFTPF)()).
>             at com.mysql.jdbc.ResultSet.getBigDecimal(ResultSet.java:493)
> 
> 
> 
> I'm using Connector/J 3.0.16.
> 
> One last thing : this seems to happen only on AIX. I have tried on Linux
and
> Windows XP and everything works as expected.
> 
> Is there something wrong with some of my database parameters or is this a
> bug ?
> 
> Please let me know if someone is interested by a testcase.
> 
> 
> Reagrds,
> 
> Julien LAFONTAINE
> 

Julien,

Connector/J uses the locale of the _client_ computer to parse numbers.
It seems your AIX box and your MySQL compile are 'sensitive' to the
locale, and thus returning numbers formatted different than your client
expects them.

If you use the 'mysql' client, does it show decimal numbers with comma
separators as well?

Since MySQL doesn't actually have configurable locale, and doesn't
expose this information in any status variable, you will have to set
your client to the same locale as your server to get these numbers to
parse, see:

http://java.sun.com/j2se/1.5.0/docs/guide/intl/locale.doc.html

and

http://java.sun.com/j2se/corejava/intl/reference/faqs/index.html#set-default
-locale

	-Mark

- --
Mark Matthews
MySQL AB, Software Development Manager - Client Connectivity
www.mysql.com

MySQL User Conference (Santa Clara CA, 18-21 April 2005)
Early registration until February 28: http://www.mysqluc.com/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.6 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFCGf0ctvXNTca6JD8RAvBCAKCedPifB6OwMK0KWmqtDSo71dLmKwCgi99W
1NQrbWDzt3BrP4YcySewcFI=
=xry/
-----END PGP SIGNATURE-----
Thread
Problem with SUM and DECIMAL fieldLAFONTAINE Julien - LYO17 Feb
  • Re: Problem with SUM and DECIMAL fieldGleb Paharenko21 Feb
  • Re: Problem with SUM and DECIMAL fieldMark Matthews21 Feb
RE: Problem with SUM and DECIMAL fieldLAFONTAINE Julien - LYO24 Feb
  • Re: Problem with SUM and DECIMAL fieldMark Matthews24 Feb