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 20:23:59 UTC
svn commit: r584836 - in /db/derby/code/branches/10.3/java:
client/org/apache/derby/client/net/NetStatementRequest.java
testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ParameterMappingTest.java
Author: kmarsden
Date: Mon Oct 15 11:23:58 2007
New Revision: 584836
URL: http://svn.apache.org/viewvc?rev=584836&view=rev
Log:
DERBY-3126 Inserting BigDecimal value in PreparedStatement with setBigDecimal into double column loses fractional digits
merge from trunk
Modified:
db/derby/code/branches/10.3/java/client/org/apache/derby/client/net/NetStatementRequest.java
db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ParameterMappingTest.java
Modified: db/derby/code/branches/10.3/java/client/org/apache/derby/client/net/NetStatementRequest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/client/org/apache/derby/client/net/NetStatementRequest.java?rev=584836&r1=584835&r2=584836&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/client/org/apache/derby/client/net/NetStatementRequest.java (original)
+++ db/derby/code/branches/10.3/java/client/org/apache/derby/client/net/NetStatementRequest.java Mon Oct 15 11:23: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/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ParameterMappingTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ParameterMappingTest.java?rev=584836&r1=584835&r2=584836&view=diff
==============================================================================
--- db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ParameterMappingTest.java (original)
+++ db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/jdbcapi/ParameterMappingTest.java Mon Oct 15 11:23: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();