You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@commons.apache.org by "Barry Sham (Jira)" <ji...@apache.org> on 2021/09/07 02:38:00 UTC
[jira] [Updated] (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:all-tabpanel ]
Barry Sham updated DBCP-581:
----------------------------
Attachment: MyBatisTest.zip
Description:
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 _fails_: (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 still exists: (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.
(Please find the attachment for Example Project)
was:
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 _fails_: (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 still exists: (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.
> 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: MyBatisTest.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 _fails_: (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 still exists: (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.
>
> (Please find the attachment for Example Project)
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)