Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » JPA BigDecimal scale difference between Oracle and DB2(BugDecimal scale difference)
JPA BigDecimal scale difference between Oracle and DB2 [message #708411] Tue, 02 August 2011 19:40 Go to next message
Rolf Paulsen is currently offline Rolf PaulsenFriend
Messages: 14
Registered: June 2010
Junior Member
Hi forum,
An entity with field of type BigDecimal, scale 2, is handled (fetched) differently between Oracle and DB2.
(...)
@Entity
@Table(name = "ENTITYNUMBER")
public class EntityNumber implements Serializable {
	private static final long serialVersionUID = 1L;	   
	@Id
	private int id;

	@Column(name="BIGDEC", nullable = false, unique = false, scale = 2, precision = 10)
	private BigDecimal bigdec;

	public EntityNumber() {
		super();
	}   
	public int getId() {
		return this.id;
	}

	public void setId(int id) {
		this.id = id;
	}   
	public BigDecimal getBigdec() {
		return this.bigdec;
	}

	public void setBigdec(BigDecimal number) {
		this.bigdec = number;
	}
}


An entity (row) with column BIGDEC=12 is fetched as "12.00" from DB2 and "12" from Oracle, where
"12.00" means BigDecimal with scale=2
"12" means BigDecimal with scale=0.

In DB2 schema, the database column is defined as DECIMAL(10,2),
in oracle schema, the database column defined as NUMBER(10,2),
both generated by eclipselink DDL generation.

So the scale is ignored in oracle.
I cannot decide which behavior is expected, but it should be identical. Is this a bug, am I missing a configuration, or a restriction in portability?

I am using eclipselink 2.1.1.

Thanks
Rolf

[Updated on: Tue, 02 August 2011 19:44]

Report message to a moderator

Re: JPA BigDecimal scale difference between Oracle and DB2 [message #709185 is a reply to message #708411] Wed, 03 August 2011 15:49 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1039
Registered: July 2009
Senior Member
Hello Rolf,

I'm not sure I understand. The scale and precision are optional and are only used for DDL generation, and appear to be working on both database platforms. The difference you see seems due to what the driver returns for the value of 12.00 from the database. If there is configuration that can be done, it is at the driver level but at the JPA level only the bigdecimal value is persisted, not its scale. Are you losing precision and it being rounded to 12?

Regards,
Chris
Re: JPA BigDecimal scale difference between Oracle and DB2 [message #710189 is a reply to message #709185] Thu, 04 August 2011 19:46 Go to previous messageGo to next message
Rolf Paulsen is currently offline Rolf PaulsenFriend
Messages: 14
Registered: June 2010
Junior Member
Hello Chris,
thanks for your reply. No scale is lost, I jost expected that the JPA provider encapsulates/hides differences of jdbc drivers. You are right, the behavior is exactly what is returned by the jdbc driver - DB2 returns always the scale of the database column, Oracle cuts the scale to the minimum if applicable. I was not aware of the fact that the scale = 2 attribute in the @Column annotation is for DDL only, as this is not explicitly described in the JPA Spec.

So if we want to have e.g. values of Money which always has scale()=2, we may have to change to a class like "Money" and use eclipselink converters - or replace equals on BigDecimal with compareTo == 0.
I did not yet find anything about configuration of one of the jdbc drivers.

Regards
Rolf

[Updated on: Thu, 04 August 2011 21:50]

Report message to a moderator

(no subject) [message #710213 is a reply to message #709185] Thu, 04 August 2011 19:46 Go to previous messageGo to next message
Rolf Paulsen is currently offline Rolf PaulsenFriend
Messages: 14
Registered: June 2010
Junior Member
P.S. If only JPA had a built-in solution like @CurrencyType... :-)

[Updated on: Thu, 04 August 2011 21:39]

Report message to a moderator

Re: (no subject) [message #716826 is a reply to message #710213] Thu, 18 August 2011 14:27 Go to previous message
Michael OBrien is currently offline Michael OBrienFriend
Messages: 34
Registered: July 2009
Member
Rolf,
I would recommend using EclipseLink converters. I have an example the uses BigInteger with no loss of precision or digits. The database sees the numeric field as a varchar - we rely on EclipseLink to convert back and forth to BigInteger or BigDecimal in your case. If you strictly use @Column you will run up to a 64 digit limitation otherwise.

Se my analysis below
http://wiki.eclipse.org/EclipseLink/Examples/Distributed#DI_5:_Limitations_of_BigInteger_translation_to_BIGINT_Database_DataType
https://bugs.eclipse.org/bugs/show_bug.cgi?id=337036


thank you
/michael
michael.f.obrien att eclipselink.org




Previous Topic:PersistenceUnitLoadingException.exceptionSearchingForPersistenceResources
Next Topic:JPA (EclipseLink) can't seem to handle an inheritance hierarchy 3 levels deep
Goto Forum:
  


Current Time: Sat Nov 29 09:01:17 GMT 2014

Powered by FUDForum. Page generated in 0.11613 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software