Re: Problem with SUM and DECIMAL field
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 LAFONTAINE Julien - LYO wrote: > 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) Julien, I went ahead and reported this as a bug, you can track it via http://bugs.mysql.com/bug.php?id=8794 (You might want to comment on it, that way you will get e-mails as its status changes). -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 iD8DBQFCHk0dtvXNTca6JD8RAoYBAJ4qTvbLE/1+HJbccqr7W8LEs2m4ygCfZW24 /mWAYntne1Gvv+K78DLgECY= =guxR -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with SUM and DECIMAL field
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:[EMAIL PROTECTED] Envoyé : lundi 21 février 2005 16:24 À : LAFONTAINE Julien - LYO Cc : mysql@lists.mysql.com 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- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with SUM and DECIMAL field
Hell. I think you should read comments at: http://bugs.mysql.com/bug.php?id=1388 They are related to the ODBC connector, but may be it will help you to solve problems with Connector/J. LAFONTAINE Julien - LYO <[EMAIL PROTECTED]> 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 > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
