You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by km...@apache.org on 2007/10/15 18:25:05 UTC
svn commit: r584815 - in /db/derby/code/trunk/java:
client/org/apache/derby/client/net/NetStatementRequest.java
testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ParameterMappingTest.java
Author: kmarsden
Date: Mon Oct 15 09:24:58 2007
New Revision: 584815
URL: http://svn.apache.org/viewvc?rev=584815&view=rev
Log:
DERBY-3126 nserting BigDecimal value in PreparedStatement with setBigDecimal into double column loses fractional digits
Get scale and precision from BigDecimal data value. Adjust scale to zero for BigDecimal with negative scale.
Modified:
db/derby/code/trunk/java/client/org/apache/derby/client/net/NetStatementRequest.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ParameterMappingTest.java
Modified: db/derby/code/trunk/java/client/org/apache/derby/client/net/NetStatementRequest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/client/org/apache/derby/client/net/NetStatementRequest.java?rev=584815&r1=584814&r2=584815&view=diff
==============================================================================
--- db/derby/code/trunk/java/client/org/apache/derby/client/net/NetStatementRequest.java (original)
+++ db/derby/code/trunk/java/client/org/apache/derby/client/net/NetStatementRequest.java Mon Oct 15 09:24:58 2007
@@ -30,6 +30,7 @@
import org.apache.derby.client.am.SqlException;
import org.apache.derby.client.am.Types;
import org.apache.derby.client.am.ClientMessageId;
+import org.apache.derby.client.am.Utils;
import org.apache.derby.shared.common.reference.SQLState;
// For performance, should we worry about the ordering of our DDM command parameters
@@ -1097,6 +1098,8 @@
// Consider refacctor so that this does not even have to look
// at the actual object data, and only uses tags from the meta data
// only have to call this once, rather than calling this for every input row
+ // Comment: I don't think that it is possible to send decimal values without looking at the data for
+ // precision and scale (Kathey Marsden 10/11)
// backburner: after refactoring this, later on, think about replacing case statements with table lookups
private java.util.Hashtable computeProtocolTypesAndLengths(Object[] inputRow,
ColumnMetaData parameterMetaData,
@@ -1208,32 +1211,35 @@
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);
+ // if null - guess with precision 1, scale 0
+ // if not null - get scale from data and calculate maximum precision.
+ // DERBY-2073. Get scale and precision from data so we don't lose fractional digits.
+ java.math.BigDecimal bigDecimal = (java.math.BigDecimal) inputRow[i];
+ int scale;
+ int precision;
+
+ if (bigDecimal == null)
+ {
+ scale = 0;
+ precision = 1;
}
- */
- // 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];
+ else
+ {
+ // adjust scale if it is negative. Packed Decimal cannot handle
+ // negative scale. We don't want to change the original
+ // object so make a new one.
+ if (bigDecimal.scale() < 0)
+ {
+ bigDecimal = bigDecimal.setScale(0);
+ inputRow[i] = bigDecimal;
+ }
+ scale = bigDecimal.scale();
+ precision = Utils.computeBigDecimalPrecision(bigDecimal);
+ }
lidAndLengths[i][0] = DRDAConstants.DRDA_TYPE_NDECIMAL;
- lidAndLengths[i][1] = (precision << 8) + (scale << 0);
+ lidAndLengths[i][1] = (precision << 8) + // use precision above
+ (scale << 0);
+
break;
case java.sql.Types.DATE:
// for input, output, and inout parameters
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ParameterMappingTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ParameterMappingTest.java?rev=584815&r1=584814&r2=584815&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ParameterMappingTest.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ParameterMappingTest.java Mon Oct 15 09:24:58 2007
@@ -24,6 +24,7 @@
import java.io.InputStream;
import java.io.Reader;
import java.math.BigDecimal;
+import java.math.BigInteger;
import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.Clob;
@@ -45,6 +46,7 @@
import org.apache.derbyTesting.junit.BaseJDBCTestCase;
import org.apache.derbyTesting.junit.JDBC;
import org.apache.derbyTesting.junit.TestConfiguration;
+import org.apache.derbyTesting.junit.Utilities;
/**
*
@@ -358,7 +360,103 @@
conn.commit();
}
-
+ /**
+ * Test setBigDecimal does not lose fractional digits
+ * @throws Exception
+ */
+ public void testDerby2073() throws Exception
+ {
+ // Cannot use setBigDecimal with J2ME
+ if (!JDBC.vmSupportsJDBC2())
+ return;
+
+
+ Statement s = createStatement();
+ s.executeUpdate("CREATE TABLE DERBY_2073_TAB (dc DECIMAL(10,2), db double, r real, i int)");
+ PreparedStatement ps = prepareStatement("INSERT INTO DERBY_2073_TAB VALUES(?,?,?,?)");
+ BigDecimal value = new BigDecimal("123.45");
+ ps.setBigDecimal(1, value);
+ ps.setBigDecimal(2, value);
+ ps.setBigDecimal(3, value);
+ ps.setBigDecimal(4, value);
+ ps.executeUpdate();
+ // Test with null values as the change sets precision/scale for null values differently
+ ps.setBigDecimal(1, null);
+ ps.setBigDecimal(2, null);
+ ps.setBigDecimal(3, null);
+ ps.setBigDecimal(4, null);
+ ps.executeUpdate();
+
+ // Test with negative scale.
+ value = new BigDecimal(new BigInteger("2"), -3);
+ ps.setBigDecimal(1,value);
+ ps.setBigDecimal(2,value);
+ ps.setBigDecimal(3,value);
+ ps.setBigDecimal(4,value);
+ ps.executeUpdate();
+
+ value = new BigDecimal("123.45");
+ // Test with setObject and scale of 2
+ ps.setObject(1, value,java.sql.Types.DECIMAL,2);
+ ps.setObject(2, value,java.sql.Types.DECIMAL,2);
+ ps.setObject(3, value,java.sql.Types.DECIMAL,2);
+ ps.setObject(4, value,java.sql.Types.DECIMAL,2);
+ ps.executeUpdate();
+
+ // Test with setObject and scale of 0
+ ps.setObject(1, value,java.sql.Types.DECIMAL,0);
+ ps.setObject(2, value,java.sql.Types.DECIMAL,0);
+ ps.setObject(3, value,java.sql.Types.DECIMAL,0);
+ ps.setObject(4, value,java.sql.Types.DECIMAL,0);
+ ps.executeUpdate();
+
+
+ // Test with setObject and type with no scale.
+ // should default to scale 0
+ ps.setObject(1, value,java.sql.Types.DECIMAL);
+ ps.setObject(2, value,java.sql.Types.DECIMAL);
+ ps.setObject(3, value,java.sql.Types.DECIMAL);
+ ps.setObject(4, value,java.sql.Types.DECIMAL);
+ ps.executeUpdate();
+
+ // Test with setObject and no type and no scale.
+ // Keeps the fractional digits.
+ ps.setObject(1, value);
+ ps.setObject(2, value);
+ ps.setObject(3, value);
+ ps.setObject(4, value);
+ ps.executeUpdate();
+
+ // Test with setObject and negative scale.
+ value = new BigDecimal(new BigInteger("2"), -3);
+ ps.setObject(1,value);
+ ps.setObject(2,value);
+ ps.setObject(3,value);
+ ps.setObject(4,value);
+ ps.executeUpdate();
+ ResultSet rs = s.executeQuery("SELECT * FROM DERBY_2073_TAB");
+ String [][] expectedResults = new String [][]
+ {{"123.45","123.45","123.45","123"},
+ {null,null,null,null},
+ {"2000.00","2000.0","2000.0","2000"},
+ {"123.45","123.45","123.45","123"},
+ {"123.00","123.0","123.0","123"},
+ {"123.00","123.0","123.0","123"},
+ {"123.45","123.45","123.45","123"},
+ {"2000.00","2000.0","2000.0","2000"}};
+
+
+ // Cannot run for embedded for now because embedded
+ // does not adjust the scale if the column is
+ // not BigDecimal (e.g. double or real or varchar.) (DERBY-3128)
+ if (usingDerbyNetClient())
+ JDBC.assertFullResultSet(rs, expectedResults);
+
+
+ s.executeUpdate("DROP TABLE DERBY_2073_TAB");
+
+ }
+
public void testParameterMapping() throws Exception {
Connection conn = getConnection();