[jira] [Comment Edited] (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=17417882#comment-17417882
]
Barry Sham edited comment on DBCP-581 at 9/21/21, 1:19 AM:
---
Thanks for your suggestion. We will look into H2 and see if we can reproduce
our test case on H2 as well.
Besides, our teammate found flag *poolPreparedStatements* seems trigger the
behavor like *maxStatements* which provided by DB2 driver for statement pooling
purpose.
We enabled trace log and found that the DBCP library calles
*PreparedStatement.clearParameters()* after the update query executed.
Is there have any reference material for the +implemenation of statement
pooling in DBCP+?
Many thanks.
was (Author: bcfsham):
Thanks for your suggestion. We will look into H2 and see if we can reproduce
our test case on H2 as well.
Besides, our teammate found flag *poolPreparedStatements* seems trigger the
behavor like *maxStatements* which provided by DB2 driver for statement pooling
purpose.
We enabled trace log and found that the DBCP library calles
*PreparedStatement.clearParameters()* after the update query execuated.
Is there have any reference material for the +implemenation of statement
pooling in DBCP+?
Many thanks.
> 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+
> We are using IBM DB2 driver 4.25.13, but the problem exists in other versions
> as well (see below)
> * 3.69.76
> * 4.19.77
> * 4.25.13
> * 4.26.14
> * 4.27.25
> * 4.28.11
> * 4.29.24
>
> +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:#ff}_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:5/mydb
> 2021-09-07 10:22:13:232 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
> inserted (value = 0.1099940395355224609375)
> 2021-09-07 10:22:13:235 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
> inserted (value = 100.1099940395355224609375)
> 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:#ff}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:5/mydb
> 2021-09-07 10:22:13:562 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
> inserted (value = 0.1099940395355224609375)
> 2021-09-07 10:22:13:564 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
> inserted (value = 100.1099940395355224609375)
> 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:3
[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=17417882#comment-17417882
]
Barry Sham commented on DBCP-581:
-
Thanks for your suggestion. We will look into H2 and see if we can reproduce
our test case on H2 as well.
Besides, our teammate found flag *poolPreparedStatements* seems trigger the
behavor like *maxStatements* which provided by DB2 driver for statement pooling
purpose.
We enabled trace log and found that the DBCP library calles
*PreparedStatement.clearParameters()* after the update query execuated.
Is there have any reference material for the +implemenation of statement
pooling in DBCP+?
Many thanks.
> 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+
> We are using IBM DB2 driver 4.25.13, but the problem exists in other versions
> as well (see below)
> * 3.69.76
> * 4.19.77
> * 4.25.13
> * 4.26.14
> * 4.27.25
> * 4.28.11
> * 4.29.24
>
> +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:#ff}_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:5/mydb
> 2021-09-07 10:22:13:232 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
> inserted (value = 0.1099940395355224609375)
> 2021-09-07 10:22:13:235 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
> inserted (value = 100.1099940395355224609375)
> 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:#ff}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:5/mydb
> 2021-09-07 10:22:13:562 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
> inserted (value = 0.1099940395355224609375)
> 2021-09-07 10:22:13:564 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
> inserted (value = 100.1099940395355224609375)
> 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:5/mydb
> 2021-09-07 10:22:13:364 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
> inserted (value = 0.1099940395355224609375)
> 2021-09-07 10:22:13:371 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
> inserted (value = 100.1099940395355224609375)
> 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
[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:
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 {color:#ff}_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:5/mydb
2021-09-07 10:22:13:232 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
inserted (value = 0.1099940395355224609375)
2021-09-07 10:22:13:235 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
inserted (value = 100.1099940395355224609375)
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:#ff}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:5/mydb
2021-09-07 10:22:13:562 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
inserted (value = 0.1099940395355224609375)
2021-09-07 10:22:13:564 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
inserted (value = 100.1099940395355224609375)
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:5/mydb
2021-09-07 10:22:13:364 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
inserted (value = 0.1099940395355224609375)
2021-09-07 10:22:13:371 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
inserted (value = 100.1099940395355224609375)
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}
Sample Project: [maplecfiv/dbcp-test
(github.com)|https://github.com/maplecfiv/dbcp-test]
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 {color:#ff}_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:5/mydb
2021-09-07 10:22:13:232 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
inserted (value = 0.1099940395355224609375)
2021-09-07 10:22:13:235 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
inserted (value = 100.1099940395355224609375)
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:#ff}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$PoolGuardCon
[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:
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+
We are using IBM DB2 driver 4.25.13, but the problem exists in other versions
as well (see below)
* 3.69.76
* 4.19.77
* 4.25.13
* 4.26.14
* 4.27.25
* 4.28.11
* 4.29.24
+MySql Ver 8.0.17 for Linux on x86_64 (MySQL Community Server - GPL)+
was:
*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)+
> 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+
> We are using IBM DB2 driver 4.25.13, but the problem exists in other versions
> as well (see below)
> * 3.69.76
> * 4.19.77
> * 4.25.13
> * 4.26.14
> * 4.27.25
> * 4.28.11
> * 4.29.24
>
> +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:#ff}_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:5/mydb
> 2021-09-07 10:22:13:232 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
> inserted (value = 0.1099940395355224609375)
> 2021-09-07 10:22:13:235 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
> inserted (value = 100.1099940395355224609375)
> 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:#ff}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:5/mydb
> 2021-09-07 10:22:13:562 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
> inserted (value = 0.1099940395355224609375)
> 2021-09-07 10:22:13:564 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
> inserted (value = 100.1099940395355224609375)
> 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:5/mydb
> 2021-09-07 10:22:13:364 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
> inserted (value = 0.1099940395355224609375)
> 2021-09-07 10:22:13:371 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
> inserted (
[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=17413039#comment-17413039
]
Barry Sham commented on DBCP-581:
-
Thank you for your comment!
The failing test case can be found on Github: [maplecfiv/dbcp-test
(github.com)|https://github.com/maplecfiv/dbcp-test]
We are using IBM DB2 driver 4.25.13, but the problem exists in other versions
as well (see below)
* 3.69.76
* 4.19.77
* 4.25.13
* 4.26.14
* 4.27.25
* 4.28.11
* 4.29.24
Also there is no problem when using JDBC (the classic
DriverManager.getConnection()) but failed when using
org.apache.commons.dbcp.BasicDataSource /
org.apache.commons.dbcp2.BasicDataSource.
> 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:#ff}_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:5/mydb
> 2021-09-07 10:22:13:232 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
> inserted (value = 0.1099940395355224609375)
> 2021-09-07 10:22:13:235 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
> inserted (value = 100.1099940395355224609375)
> 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:#ff}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:5/mydb
> 2021-09-07 10:22:13:562 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
> inserted (value = 0.1099940395355224609375)
> 2021-09-07 10:22:13:564 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
> inserted (value = 100.1099940395355224609375)
> 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:5/mydb
> 2021-09-07 10:22:13:364 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
> inserted (value = 0.1099940395355224609375)
> 2021-09-07 10:22:13:371 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
> inserted (value = 100.1099940395355224609375)
> 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 Projec
[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: (was: image-2021-09-10-15-50-56-604.png)
> 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:#ff}_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:5/mydb
> 2021-09-07 10:22:13:232 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
> inserted (value = 0.1099940395355224609375)
> 2021-09-07 10:22:13:235 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
> inserted (value = 100.1099940395355224609375)
> 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:#ff}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:5/mydb
> 2021-09-07 10:22:13:562 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
> inserted (value = 0.1099940395355224609375)
> 2021-09-07 10:22:13:564 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
> inserted (value = 100.1099940395355224609375)
> 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:5/mydb
> 2021-09-07 10:22:13:364 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
> inserted (value = 0.1099940395355224609375)
> 2021-09-07 10:22:13:371 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
> inserted (value = 100.1099940395355224609375)
> 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)
[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: image-2021-09-10-15-50-56-604.png
> 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, image-2021-09-10-15-50-56-604.png
>
>
> 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:#ff}_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:5/mydb
> 2021-09-07 10:22:13:232 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
> inserted (value = 0.1099940395355224609375)
> 2021-09-07 10:22:13:235 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
> inserted (value = 100.1099940395355224609375)
> 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:#ff}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:5/mydb
> 2021-09-07 10:22:13:562 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
> inserted (value = 0.1099940395355224609375)
> 2021-09-07 10:22:13:564 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
> inserted (value = 100.1099940395355224609375)
> 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:5/mydb
> 2021-09-07 10:22:13:364 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
> inserted (value = 0.1099940395355224609375)
> 2021-09-07 10:22:13:371 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
> inserted (value = 100.1099940395355224609375)
> 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)
[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: (was: MyBatisTest.zip)
> 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:#ff}_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:5/mydb
> 2021-09-07 10:22:13:232 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
> inserted (value = 0.1099940395355224609375)
> 2021-09-07 10:22:13:235 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
> inserted (value = 100.1099940395355224609375)
> 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:#ff}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:5/mydb
> 2021-09-07 10:22:13:562 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
> inserted (value = 0.1099940395355224609375)
> 2021-09-07 10:22:13:564 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
> inserted (value = 100.1099940395355224609375)
> 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:5/mydb
> 2021-09-07 10:22:13:364 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
> inserted (value = 0.1099940395355224609375)
> 2021-09-07 10:22:13:371 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
> inserted (value = 100.1099940395355224609375)
> 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)
[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: DBCPTest.zip
> 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:#ff}_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:5/mydb
> 2021-09-07 10:22:13:232 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
> inserted (value = 0.1099940395355224609375)
> 2021-09-07 10:22:13:235 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
> inserted (value = 100.1099940395355224609375)
> 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:#ff}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:5/mydb
> 2021-09-07 10:22:13:562 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
> inserted (value = 0.1099940395355224609375)
> 2021-09-07 10:22:13:564 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
> inserted (value = 100.1099940395355224609375)
> 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:5/mydb
> 2021-09-07 10:22:13:364 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
> inserted (value = 0.1099940395355224609375)
> 2021-09-07 10:22:13:371 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
> inserted (value = 100.1099940395355224609375)
> 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)
[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:
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 {color:#ff}_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:5/mydb
2021-09-07 10:22:13:232 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
inserted (value = 0.1099940395355224609375)
2021-09-07 10:22:13:235 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
inserted (value = 100.1099940395355224609375)
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:#ff}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:5/mydb
2021-09-07 10:22:13:562 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
inserted (value = 0.1099940395355224609375)
2021-09-07 10:22:13:564 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
inserted (value = 100.1099940395355224609375)
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:5/mydb
2021-09-07 10:22:13:364 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
inserted (value = 0.1099940395355224609375)
2021-09-07 10:22:13:371 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
inserted (value = 100.1099940395355224609375)
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)
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 {color:#FF}_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:5/mydb
2021-09-07 10:22:13:232 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
inserted (value = 0.1099940395355224609375)
2021-09-07 10:22:13:235 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
inserted (value = 100.1099940395355224609375)
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:#FF}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:/
[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:
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 {color:#FF}_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:5/mydb
2021-09-07 10:22:13:232 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
inserted (value = 0.1099940395355224609375)
2021-09-07 10:22:13:235 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
inserted (value = 100.1099940395355224609375)
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:#FF}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:5/mydb
2021-09-07 10:22:13:562 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
inserted (value = 0.1099940395355224609375)
2021-09-07 10:22:13:564 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
inserted (value = 100.1099940395355224609375)
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:5/mydb
2021-09-07 10:22:13:232 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
inserted (value = 0.1099940395355224609375)
2021-09-07 10:22:13:235 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
inserted (value = 100.1099940395355224609375)
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:5/mydb
2021-09-07 10:22:13:562 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
inserted (value = 0.1099940395355224609375)
2021-09-07 10:22:13:564 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
inserted (value = 100.1099940395355224609375)
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)
> BigDecimal precision lossing when reusing prepared statement for INSERT /
> UPDATE queries in DB2
>
[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:5/mydb
2021-09-07 10:22:13:232 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
inserted (value = 0.1099940395355224609375)
2021-09-07 10:22:13:235 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
inserted (value = 100.1099940395355224609375)
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:5/mydb
2021-09-07 10:22:13:562 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
inserted (value = 0.1099940395355224609375)
2021-09-07 10:22:13:564 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
inserted (value = 100.1099940395355224609375)
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:5/mydb
2021-09-07 10:22:13:232 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
inserted (value = 0.1099940395355224609375)
2021-09-07 10:22:13:235 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
inserted (value = 100.1099940395355224609375)
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:5/mydb
2021-09-07 10:22:13:562 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
inserted (value = 0.1099940395355224609375)
2021-09-07 10:22:13:564 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
inserted (value = 100.1099940395355224609375)
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
> -
[jira] [Created] (DBCP-581) BigDecimal precision lossing when reusing prepared statement for INSERT / UPDATE queries in DB2
Barry Sham created DBCP-581:
---
Summary: 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: 2.9.0, 1.4
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
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:5/mydb
2021-09-07 10:22:13:232 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
inserted (value = 0.1099940395355224609375)
2021-09-07 10:22:13:235 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
inserted (value = 100.1099940395355224609375)
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:5/mydb
2021-09-07 10:22:13:562 DEBUG JdbcDbcpTest:103 - [Perform 1 insert] 1 record
inserted (value = 0.1099940395355224609375)
2021-09-07 10:22:13:564 DEBUG JdbcDbcpTest:103 - [Perform 2 insert] 1 record
inserted (value = 100.1099940395355224609375)
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.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
