You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@commons.apache.org by "Gary D. Gregory (Jira)" <ji...@apache.org> on 2021/09/08 15:54:00 UTC
[jira] [Commented] (DBCP-581) BigDecimal precision lossing when
reusing prepared statement for INSERT / UPDATE queries in DB2
[ https://issues.apache.org/jira/browse/DBCP-581?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17412029#comment-17412029 ]
Gary D. Gregory commented on DBCP-581:
--------------------------------------
It would be most helpful if you could provide a failing test case on GitHub, where you can likely clone of one the existing tests. This should also reveal if this is an issue in DBCP in general or is specific to one driver.
> BigDecimal precision lossing when reusing prepared statement for INSERT / UPDATE queries in DB2
> -----------------------------------------------------------------------------------------------
>
> Key: DBCP-581
> URL: https://issues.apache.org/jira/browse/DBCP-581
> Project: Commons DBCP
> Issue Type: Bug
> Affects Versions: 1.4, 2.9.0
> Environment: *Java Version*
> +DBCP 1.4+
> * IBM 64-bit SDK for Windows AMD64/EM64T architecture, Java Technology Edition, Version 7 Release 1
> * Open JDK Zulu 8.31.0.1
> +DBCP 2.9.0+
> * Open JDK Zulu 8.31.0.1
>
> *Database*
> +IBM DB2 10.5+
> +IBM DB2 11.5+
> +MySql Ver 8.0.17 for Linux on x86_64 (MySQL Community Server - GPL)+
> Reporter: Barry Sham
> Priority: Blocker
> Attachments: DBCPTest.zip
>
>
> We use DBCP1.4 (with *poolPreparedStatements enabled*) to insert records that contains {{*BigDecimal* and it will store into a *Decimal(11, 2)*}} column.
> Although BigDecimal in the first insertion can be rounded up propertly, the remains {color:#ff0000}_fails_{color}: (see the bottom output)
> {code:java}
> 2021-09-07 10:22:13:186 INFO JdbcDbcpTest:81 - JDK version 1.8.0_181
> 2021-09-07 10:22:13:190 INFO JdbcDbcpTest:83 - [Connection class: org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper] connected to jdbc:db2://localhost:50000/mydb
> 2021-09-07 10:22:13:232 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record inserted (value = 0.10999999940395355224609375)
> 2021-09-07 10:22:13:235 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record inserted (value = 100.10999999940395355224609375)
> 2021-09-07 10:22:13:241 INFO JdbcDbcpTest:113 - Execute query SELECT DECTEST FROM TBL_DECTEST
> 2021-09-07 10:22:13:262 INFO CompareUtils:27 - *******************************
> 2021-09-07 10:22:13:263 INFO CompareUtils:28 - * Value set: [0.11, 100.10]
> 2021-09-07 10:22:13:263 INFO CompareUtils:29 - *******************************{code}
> Then we upgraded the DBCP from 1.4 to 2.9.0 but the problem {color:#ff0000}still exists{color}: (see the connection class in line 2)
> {code:java}
> 2021-09-07 10:22:13:533 INFO JdbcDbcpTest:81 - JDK version 1.8.0_181
> 2021-09-07 10:22:13:534 INFO JdbcDbcpTest:83 - [Connection class: org.apache.commons.dbcp2.PoolingDataSource$PoolGuardConnectionWrapper] connected to jdbc:db2://localhost:50000/mydb
> 2021-09-07 10:22:13:562 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record inserted (value = 0.10999999940395355224609375)
> 2021-09-07 10:22:13:564 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record inserted (value = 100.10999999940395355224609375)
> 2021-09-07 10:22:13:571 INFO JdbcDbcpTest:113 - Execute query SELECT DECTEST FROM TBL_DECTEST
> 2021-09-07 10:22:13:574 INFO CompareUtils:27 - *******************************
> 2021-09-07 10:22:13:574 INFO CompareUtils:28 - * Value set: [0.11, 100.10]
> 2021-09-07 10:22:13:574 INFO CompareUtils:29 - ******************************* {code}
> We found all BigDecimal can rounded up properly if we *disable poolPreparedStatements*. However this is not an option due to performance condition.
> Also we tried to insert BigDecimal with only JDBC and all BigDecimal rounded up properly as well:
> {code:java}
> 2021-09-07 10:22:13:345 INFO JdbcDbcpTest:81 - JDK version 1.8.0_181
> 2021-09-07 10:22:13:345 INFO JdbcDbcpTest:83 - [Connection class: com.ibm.db2.jcc.t4.b] connected to jdbc:db2://localhost:50000/mydb
> 2021-09-07 10:22:13:364 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record inserted (value = 0.10999999940395355224609375)
> 2021-09-07 10:22:13:371 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record inserted (value = 100.10999999940395355224609375)
> 2021-09-07 10:22:13:372 INFO JdbcDbcpTest:113 - Execute query SELECT DECTEST FROM TBL_DECTEST
> 2021-09-07 10:22:13:374 INFO CompareUtils:27 - *******************************
> 2021-09-07 10:22:13:374 INFO CompareUtils:28 - * Value set: [100.11, 0.11]
> 2021-09-07 10:22:13:374 INFO CompareUtils:29 - *******************************{code}
>
> (Please find the attachment for Example Project)
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)