You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Lukasz P (JIRA)" <ji...@apache.org> on 2006/11/13 10:37:37 UTC
[jira] Created: (DERBY-2073) Inserting BigDecimal value in
PreparedStatement into double column loses fractional digits
Inserting BigDecimal value in PreparedStatement into double column loses fractional digits
------------------------------------------------------------------------------------------
Key: DERBY-2073
URL: http://issues.apache.org/jira/browse/DERBY-2073
Project: Derby
Issue Type: Bug
Components: JDBC
Affects Versions: 10.1.2.1
Environment: windows xp
Reporter: Lukasz P
Attachments: BigDecimalProblemTester.java
Create table with column of type double.
Create a prepared statement which inserts value to this column.
use preparedStatement#setBigDecimal method of the statement to set value of the column.
Execution of such statement succeeds, but actual value in db has no fractional digits.
You can use attached test class to reproduce this problem
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
Re: [jira] Commented: (DERBY-2073) Inserting BigDecimal value in
PreparedStatement into double column loses fractional digits
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
"Daniel John Debrunner (JIRA)" <ji...@apache.org> writes:
> Class.forName(JDBC_DERBY_DRIVER).newInstance();
The ".newInstance()" part can usually be dropped as well. The JDBC3
specification does not mention use of newInstance, I think. The Derby
manuals use both versions; although mostly newInstance is omitted. I
seem to remember I had to use newInstance to be able to re-load the
driver in a test, though...
In JDBC4, only a DriverManager.connect() call will be required
(driver autoloading feature).
[jira] Commented: (DERBY-2073) Inserting BigDecimal value in
PreparedStatement into double column loses fractional digits
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-2073?page=comments#action_12449403 ]
Daniel John Debrunner commented on DERBY-2073:
----------------------------------------------
Just an FYI on some code in the repro case (thanks for that).
DriverManager.registerDriver((Driver) Class.forName(JDBC_DERBY_DRIVER).newInstance());
There is no need for applications to call DriverManager.registerDriver, that method is intended for a driver to register itself.
Calling this method from an application can cause unexpected failures.
To load the Derby driver, the correct code is simply:
Class.forName(JDBC_DERBY_DRIVER).newInstance();
> Inserting BigDecimal value in PreparedStatement into double column loses fractional digits
> ------------------------------------------------------------------------------------------
>
> Key: DERBY-2073
> URL: http://issues.apache.org/jira/browse/DERBY-2073
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.1.2.1
> Environment: windows xp
> Reporter: Lukasz P
> Attachments: BigDecimalProblemTester.java
>
>
> Create table with column of type double.
> Create a prepared statement which inserts value to this column.
> use preparedStatement#setBigDecimal method of the statement to set value of the column.
> Execution of such statement succeeds, but actual value in db has no fractional digits.
> You can use attached test class to reproduce this problem
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Assigned: (DERBY-2073) Inserting BigDecimal value in
PreparedStatement into double column loses fractional digits
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2073?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden reassigned DERBY-2073:
-------------------------------------
Assignee: Kathey Marsden
> Inserting BigDecimal value in PreparedStatement into double column loses fractional digits
> ------------------------------------------------------------------------------------------
>
> Key: DERBY-2073
> URL: https://issues.apache.org/jira/browse/DERBY-2073
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.1.2.1
> Environment: windows xp
> Reporter: Lukasz P
> Assignee: Kathey Marsden
> Attachments: BigDecimalProblemTester.java
>
>
> Create table with column of type double.
> Create a prepared statement which inserts value to this column.
> use preparedStatement#setBigDecimal method of the statement to set value of the column.
> Execution of such statement succeeds, but actual value in db has no fractional digits.
> You can use attached test class to reproduce this problem
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Resolved: (DERBY-2073) Inserting BigDecimal value in
PreparedStatement into double column loses fractional digits
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2073?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden resolved DERBY-2073.
-----------------------------------
Resolution: Fixed
Fix Version/s: 10.4.0.0
10.3.1.5
> Inserting BigDecimal value in PreparedStatement into double column loses fractional digits
> ------------------------------------------------------------------------------------------
>
> Key: DERBY-2073
> URL: https://issues.apache.org/jira/browse/DERBY-2073
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.1.2.1
> Environment: windows xp
> Reporter: Lukasz P
> Assignee: Kathey Marsden
> Fix For: 10.3.1.5, 10.4.0.0
>
> Attachments: BigDecimalProblemTester.java, NegativeScaleTest.java
>
>
> Create table with column of type double.
> Create a prepared statement which inserts value to this column.
> use preparedStatement#setBigDecimal method of the statement to set value of the column.
> Execution of such statement succeeds, but actual value in db has no fractional digits.
> You can use attached test class to reproduce this problem
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2073) Inserting BigDecimal value in
PreparedStatement into double column loses fractional digits
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2073?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12534657 ]
Knut Anders Hatlen commented on DERBY-2073:
-------------------------------------------
Hmm, seems like the client driver isn't too fond of negative scales... With 10.3, I see that this piece of code fails with ClientDriver:
PreparedStatement ps = c.prepareStatement("values cast(? as double)");
BigDecimal bd = new BigDecimal(3000).setScale(-1);
ps.setObject(1, bd, Types.DECIMAL, -1);
Exception in thread "main" java.sql.SQLException: '-1' is an invalid scale.
at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:96)
at org.apache.derby.client.am.SqlException.getSQLException(SqlException.java:362)
at org.apache.derby.client.am.PreparedStatement.setObject(PreparedStatement.java:1483)
at bigdec.main(bigdec.java:10)
Caused by: org.apache.derby.client.am.SqlException: '-1' is an invalid scale.
at org.apache.derby.client.am.PreparedStatement.checkForValidScale(PreparedStatement.java:2433)
at org.apache.derby.client.am.PreparedStatement.setObjectX(PreparedStatement.java:1492)
at org.apache.derby.client.am.PreparedStatement.setObject(PreparedStatement.java:1478)
... 1 more
The same code runs just fine on EmbeddedDriver. Perhaps something for a new JIRA?
> Inserting BigDecimal value in PreparedStatement into double column loses fractional digits
> ------------------------------------------------------------------------------------------
>
> Key: DERBY-2073
> URL: https://issues.apache.org/jira/browse/DERBY-2073
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.1.2.1
> Environment: windows xp
> Reporter: Lukasz P
> Assignee: Kathey Marsden
> Attachments: BigDecimalProblemTester.java, NegativeScaleTest.java
>
>
> Create table with column of type double.
> Create a prepared statement which inserts value to this column.
> use preparedStatement#setBigDecimal method of the statement to set value of the column.
> Execution of such statement succeeds, but actual value in db has no fractional digits.
> You can use attached test class to reproduce this problem
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2073) Inserting BigDecimal value in
PreparedStatement into double column loses fractional digits
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2073?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12534105 ]
Kathey Marsden commented on DERBY-2073:
---------------------------------------
I noticed this commented out code in NetStatementRequest.java
I think for DERBY we don't use describe input so we are going to need to determine precision and scale from the data. The exception would be for a specified scale in:
oid setObject(int parameterIndex,
Object x,
int targetSqlType,
int scaleOrLength)
throws SQLException
Still the precision would have to be calculated.
case java.sql.Types.NUMERIC:
case java.sql.Types.DECIMAL:
// lid: PROTOCOL_TYPE_NDECIMAL
// dataFormat: java.math.BigDecimal
// input only:
// if null and describe input - use describe input precision and scale
// if not null and describe input - calculate precision and actual scale from data
// if null and no describe input - guess with precision 1 scale 0
// if not null and no describe input - calculate precision and actual scale from data
// output only:
// use largest precision/scale based on registered scale from registerOutParameter
// inout:
// if null - use largest precision/scale based on scale from registerOutParameter
// if not null - write bigDecimal () pass registered scale so it can pad, you don't even
// have to look at the actual scale at this level.
/*
if (parameterMetaData.isGuessed) {
java.math.BigDecimal bigDecimal = (java.math.BigDecimal) inputRow[i];
int precision = Utils.computeBigDecimalPrecision (bigDecimal);
lidAndLengths[i][1] = (precision << 8) + // use precision above
(bigDecimal.scale() << 0);
}
*/
// Split this entire method into two parts, the first method is called only once and the inputRow is not passed,!!
// the second method is called for every inputRow and overrides inputDA lengths/scales based upon the acutal data!
// for decimal and blob columns only
int precision = parameterMetaData.sqlPrecision_[i];
int scale = parameterMetaData.sqlScale_[i];
lidAndLengths[i][0] = DRDAConstants.DRDA_TYPE_NDECIMAL;
lidAndLengths[i][1] = (precision << 8) + (scale << 0);
break;
> Inserting BigDecimal value in PreparedStatement into double column loses fractional digits
> ------------------------------------------------------------------------------------------
>
> Key: DERBY-2073
> URL: https://issues.apache.org/jira/browse/DERBY-2073
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.1.2.1
> Environment: windows xp
> Reporter: Lukasz P
> Assignee: Kathey Marsden
> Attachments: BigDecimalProblemTester.java
>
>
> Create table with column of type double.
> Create a prepared statement which inserts value to this column.
> use preparedStatement#setBigDecimal method of the statement to set value of the column.
> Execution of such statement succeeds, but actual value in db has no fractional digits.
> You can use attached test class to reproduce this problem
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2073) Inserting BigDecimal value in
PreparedStatement into double column loses fractional digits
Posted by "James F. Adams (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2073?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12522827 ]
James F. Adams commented on DERBY-2073:
---------------------------------------
This problem appears to only occur with the client driver, the fractional digits are retained when using the embedded driver.
> Inserting BigDecimal value in PreparedStatement into double column loses fractional digits
> ------------------------------------------------------------------------------------------
>
> Key: DERBY-2073
> URL: https://issues.apache.org/jira/browse/DERBY-2073
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.1.2.1
> Environment: windows xp
> Reporter: Lukasz P
> Attachments: BigDecimalProblemTester.java
>
>
> Create table with column of type double.
> Create a prepared statement which inserts value to this column.
> use preparedStatement#setBigDecimal method of the statement to set value of the column.
> Execution of such statement succeeds, but actual value in db has no fractional digits.
> You can use attached test class to reproduce this problem
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2073) Inserting BigDecimal value in
PreparedStatement into double column loses fractional digits
Posted by "Lukasz P (JIRA)" <ji...@apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-2073?page=all ]
Lukasz P updated DERBY-2073:
----------------------------
Attachment: BigDecimalProblemTester.java
> Inserting BigDecimal value in PreparedStatement into double column loses fractional digits
> ------------------------------------------------------------------------------------------
>
> Key: DERBY-2073
> URL: http://issues.apache.org/jira/browse/DERBY-2073
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.1.2.1
> Environment: windows xp
> Reporter: Lukasz P
> Attachments: BigDecimalProblemTester.java
>
>
> Create table with column of type double.
> Create a prepared statement which inserts value to this column.
> use preparedStatement#setBigDecimal method of the statement to set value of the column.
> Execution of such statement succeeds, but actual value in db has no fractional digits.
> You can use attached test class to reproduce this problem
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] Updated: (DERBY-2073) Inserting BigDecimal value in
PreparedStatement into double column loses fractional digits
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2073?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden updated DERBY-2073:
----------------------------------
Attachment: NegativeScaleTest.java
Thanks Knut for pointing out the negative scale case. I get an overflow error with my patch. Without the patch it passes ok on client with negative scale. I'll take a closer look at how best to resolve the issue.
[C:/kmarsden/repro/DERBY-2073] java NegativeScaleTest
Embedded
PASS: got expected value:2000.00
PASS: got expected value:2000.0
PASS: got expected value:2000.0
PASS: got expected value:2E+3
client
Exception in thread "main" java.sql.SQLDataException: Overflow occurred during numeric data type conversion of '2E+3' to
packed decimal.
at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(SQLExceptionFactory40.java:72)
at org.apache.derby.client.am.SqlException.getSQLException(SqlException.java:362)
at org.apache.derby.client.am.PreparedStatement.executeUpdate(PreparedStatement.java:391)
at NegativeScaleTest.testNegativeScale(NegativeScaleTest.java:28)
at NegativeScaleTest.main(NegativeScaleTest.java:16)
Caused by: org.apache.derby.client.am.SqlException: Overflow occurred during numeric data type conversion of '2E+3' to p
acked decimal.
at org.apache.derby.client.am.Decimal.bigDecimalToPackedDecimalBytes(Decimal.java:398)
at org.apache.derby.client.net.Request.writeBigDecimal(Request.java:1497)
at org.apache.derby.client.net.NetStatementRequest.buildFDODTA(NetStatementRequest.java:733)
at org.apache.derby.client.net.NetStatementRequest.buildSQLDTAcommandData(NetStatementRequest.java:536)
at org.apache.derby.client.net.NetStatementRequest.writeExecute(NetStatementRequest.java:143)
at org.apache.derby.client.net.NetPreparedStatement.writeExecute_(NetPreparedStatement.java:172)
at org.apache.derby.client.am.PreparedStatement.writeExecute(PreparedStatement.java:1784)
at org.apache.derby.client.am.PreparedStatement.flowExecute(PreparedStatement.java:2030)
at org.apache.derby.client.am.PreparedStatement.executeUpdateX(PreparedStatement.java:396)
at org.apache.derby.client.am.PreparedStatement.executeUpdate(PreparedStatement.java:382)
... 2 more
[C:/kmarsden/repro/DERBY-2073]
> Inserting BigDecimal value in PreparedStatement into double column loses fractional digits
> ------------------------------------------------------------------------------------------
>
> Key: DERBY-2073
> URL: https://issues.apache.org/jira/browse/DERBY-2073
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.1.2.1
> Environment: windows xp
> Reporter: Lukasz P
> Assignee: Kathey Marsden
> Attachments: BigDecimalProblemTester.java, NegativeScaleTest.java
>
>
> Create table with column of type double.
> Create a prepared statement which inserts value to this column.
> use preparedStatement#setBigDecimal method of the statement to set value of the column.
> Execution of such statement succeeds, but actual value in db has no fractional digits.
> You can use attached test class to reproduce this problem
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.