-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 
Author Message
 Post subject: BigDecimal and MySQL problems
PostPosted: Sat Nov 15, 2003 6:02 pm 
Newbie

Joined: Mon Oct 06, 2003 4:41 pm
Posts: 18
I'm using BigDecimal datatypes in my POJOs against a MySQL 4.0.14 using InnoDB table handlers.

My first problem is that the default DDL generated for them is as follows:

Code:
quantity NUMERIC(19, 255)


That isn't particular big as decimals go is it? I've heard they got a lot bigger.

I can get round that by explicitly setting the column type as follows so it's not a major problem:

Code:
quantity DECIMAL(60, 0)


The second problem actually seems to be an issue with MySQL but at this tage I don't know if it's a feature or bug. It may well have an impact on Hibernate's sql generation. It can be demonstrated by running the following against MySQL 4.0.14, I've not yet tried any later versions but it doesn't occur on MySQL v3.

Code:
DROP TABLE IF EXISTS TEST_TABLE;

CREATE TABLE TEST_TABLE (
  quantity decimal(60,0)
) TYPE=InnoDB;

insert into TEST_TABLE values (10000000000000000000);
insert into TEST_TABLE values ('10000000000000000000');


On my version of MySQL the resulting table contains the following values:

Code:
-8446744073709551616
10000000000000000000


Obviously if Hibernate was wrapping BigDecimals in quotes then I wouldn't be experiencing the problem although I accept that that is probably a work-around for a MySQL problem rather than desired behaviour.

Should I raise this in JIRA or keep hammering away at MySQL?

Matt.
[/code]


Top
 Profile  
 
 Post subject:
PostPosted: Sun Nov 16, 2003 9:33 am 
Newbie

Joined: Mon Oct 06, 2003 4:41 pm
Posts: 18
Having thought about this a bit more I suppose it's probably the JDBC driver at fault (Connector/J 3.0.9). If the setBigDecimal() method wrapped the value in quotes then problem would be solved (in Java at least). I still can't find any MySQL documentation that will explain what the expected behaviour should be and it seems very odd that the behaviour is different across different versions of MySQL.

Any suggestions on a work-around that will force wrapping of my BigDecimal types in quotes in the hibernate generated SQL would be appreciated.

Matt.


Top
 Profile  
 
 Post subject:
PostPosted: Sun Nov 16, 2003 1:14 pm 
Newbie

Joined: Mon Oct 06, 2003 4:41 pm
Posts: 18
For anyone that's interestd:
http://bugs.mysql.com/bug.php?id=1845

Seems that my workaround is to do this prior to setting my BigDecimal property:

Code:
quantity = quantity.setScale(0, BigDecimal.ROUND_DOWN);
quantity = quantity.setScale(1, BigDecimal.ROUND_DOWN);


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.