[ 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)