You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by jr...@aep.com on 2003/07/03 14:26:27 UTC
Loss of precision on DB2 with BigDecimal and PersistenceBroker.store()
Thomas,
We've been happily using OJB for several months now with very few issues,
but I recently found one that I was a little surprised hasn't yet been
found. Perhaps the DB2 drivers are the only one that exhibit this
behavior, however. After having fully researched the problem, though, I
think IBM has actually implemented their drivers to spec.
The short summary of this problem is that we are seeing a loss in precision
when storing ANY BigDecimal using either the Type 4 or Type 2 IBM DB2
database drivers. All digits after the decimal point are truncated. This
same problem does NOT occur with the HSQL database driver.
Detailed info:
-> We tested the running of a prepared insert statement against DB2 using
the PreparedStatement.setBigDecimal() method and found that no truncation
occurred. The PreparedStatement same code run against an HSQL database did
not exhibit the problem, either. Similar code run using a
PersistenceBroker under HSQL did NOT show the problem. Similar code run
using a PersistenceBroker under DB2 DID show the problem.
-> This led me to believe that the problem was not exclusively a driver
problem, but a problem specific to OJB OR a problem with the interaction
between the DB2 driver and OJB. That is, using a direct setBigDecimal()
call using JDBC works in all cases, but when we store objects via OJB with
the DB2 driver, we see the problem.
-> After much troubleshooting, I finally started exploring the OJB source
code and discovered how OJB was performing the parameter substitution on an
INSERT statement. In the source of the OJB 1.0 rc3 distribution, the
problem occurs on line 230 of
org.apache.ojb.broker.platforms.PlatformDefaultImpl. The line of code is
shown below:
ps.setObject(index, value, sqlType);
-> Turns out that per the JDBC specification, that the 3 argument version
of setObject() SHOULD ASSUME a SCALE of 0 for the passed Object. This is
not desirable in all cases IF the given Object is a BigDecimal. Here is
the link to the javadoc that describes this behavior:
http://java.sun.com/j2se/1.4.2/docs/api/java/sql/PreparedStatement.html#setObject(int,%20java.lang.Object,%20int)
-> It appears that HSQL is a little liberal with the spec on this method,
as this same method is being called when we target HSQL and we aren't
seeing the problem. Naughty, naughty! Don't get me wrong, though, WE LOVE
HSQL!
-> In order to isolate other platforms from my hack to correct this
problem, I have made a correction to the
org.apache.ojb.broker.platforms.PlatformDb2Impl class to fix this issue,
but your team should decide on how this should best be implemented.
Perhaps it is more suited for the PlatformDefaultImpl class? And perhaps
setBigDecimal() should be called if the passed Object is an instance of
BigDecimal instead of relying on setObject()? In any event, I have worked
around the problem with the correction starting at line 84 in the attached
version of PlatformDb2Impl.
Some background links that helped me figure out the problem:
http://www-1.ibm.com/support/docview.wss?uid=swg1PQ66125
http://java.sun.com/j2se/1.4.2/docs/api/java/sql/PreparedStatement.html#setObject(int,%20java.lang.Object,%20int)
Thanks for your time, Thomas, and please let me know if I can provide you
with any more information. I have an example that I can send if need be to
demonstrate the problem.
Best regards ... and thanks for being a part of such a wonderful piece of
software!
-- Jason Mihalick
(See attached file: PlatformDb2Impl.java)
Re: Loss of precision on DB2 with BigDecimal and PersistenceBroker.store()
Posted by Thomas Mahler <th...@web.de>.
Hi Jason,
your patch didn't make it through the filter...
please post it directly to me.
I'll have a look at it. I agree that it will be better to it in the
PlatformDefaultImpl.
I'm not sure why setObject is used at all. normally the type specific
methods (setBigDecimal() in his case) should be used ?
cheers,
Thomas
jrmihalick@aep.com wrote:
> Thomas,
>
> We've been happily using OJB for several months now with very few issues,
> but I recently found one that I was a little surprised hasn't yet been
> found. Perhaps the DB2 drivers are the only one that exhibit this
> behavior, however. After having fully researched the problem, though, I
> think IBM has actually implemented their drivers to spec.
>
> The short summary of this problem is that we are seeing a loss in precision
> when storing ANY BigDecimal using either the Type 4 or Type 2 IBM DB2
> database drivers. All digits after the decimal point are truncated. This
> same problem does NOT occur with the HSQL database driver.
>
> Detailed info:
> -> We tested the running of a prepared insert statement against DB2 using
> the PreparedStatement.setBigDecimal() method and found that no truncation
> occurred. The PreparedStatement same code run against an HSQL database did
> not exhibit the problem, either. Similar code run using a
> PersistenceBroker under HSQL did NOT show the problem. Similar code run
> using a PersistenceBroker under DB2 DID show the problem.
> -> This led me to believe that the problem was not exclusively a driver
> problem, but a problem specific to OJB OR a problem with the interaction
> between the DB2 driver and OJB. That is, using a direct setBigDecimal()
> call using JDBC works in all cases, but when we store objects via OJB with
> the DB2 driver, we see the problem.
> -> After much troubleshooting, I finally started exploring the OJB source
> code and discovered how OJB was performing the parameter substitution on an
> INSERT statement. In the source of the OJB 1.0 rc3 distribution, the
> problem occurs on line 230 of
> org.apache.ojb.broker.platforms.PlatformDefaultImpl. The line of code is
> shown below:
> ps.setObject(index, value, sqlType);
>
> -> Turns out that per the JDBC specification, that the 3 argument version
> of setObject() SHOULD ASSUME a SCALE of 0 for the passed Object. This is
> not desirable in all cases IF the given Object is a BigDecimal. Here is
> the link to the javadoc that describes this behavior:
> http://java.sun.com/j2se/1.4.2/docs/api/java/sql/PreparedStatement.html#setObject(int,%20java.lang.Object,%20int)
>
> -> It appears that HSQL is a little liberal with the spec on this method,
> as this same method is being called when we target HSQL and we aren't
> seeing the problem. Naughty, naughty! Don't get me wrong, though, WE LOVE
> HSQL!
>
> -> In order to isolate other platforms from my hack to correct this
> problem, I have made a correction to the
> org.apache.ojb.broker.platforms.PlatformDb2Impl class to fix this issue,
> but your team should decide on how this should best be implemented.
> Perhaps it is more suited for the PlatformDefaultImpl class? And perhaps
> setBigDecimal() should be called if the passed Object is an instance of
> BigDecimal instead of relying on setObject()? In any event, I have worked
> around the problem with the correction starting at line 84 in the attached
> version of PlatformDb2Impl.
>
>
> Some background links that helped me figure out the problem:
>
> http://www-1.ibm.com/support/docview.wss?uid=swg1PQ66125
> http://java.sun.com/j2se/1.4.2/docs/api/java/sql/PreparedStatement.html#setObject(int,%20java.lang.Object,%20int)
>
>
> Thanks for your time, Thomas, and please let me know if I can provide you
> with any more information. I have an example that I can send if need be to
> demonstrate the problem.
>
> Best regards ... and thanks for being a part of such a wonderful piece of
> software!
>
> -- Jason Mihalick
>
> (See attached file: PlatformDb2Impl.java)
>
>
>
>
> ------------------------------------------------------------------------
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org