BigDecimal has mismatching scale value for given Decimal schema about kafka-connect-jdbc HOT 10 CLOSED

I am also encountered this error message when I try to use JDBC connector of oracle db. According to Stackoverflow-Difference between number and integer datatype in oracle dictionary views, I found out the "number" type in oracle table caused this issue. In my case, I have a field "ShipAmt" (Type: Number , Scale: 3) defined in oracle, and have one record "ShipAmt=10". From oracle jdbc resultset meta data, the scale (3) is correct but the decimal value using resultset.getBigDecimal() only show scale(0) which cause value / schema mismatch validation error.
Below is a quick fix: Modify "DataConvert.convertFieldValue" method to enable "scale" check and force setting right scale number on BigDecimal object.

public class DataConverter {
private static void convertFieldValue(ResultSet resultSet, int col, int colType,
                                        Struct struct, String fieldName)
      throws SQLException, IOException {
.....
.....
case Types.NUMERIC:
      case Types.DECIMAL: {
        //check scale from meta and reflect on BigDecimal object
        int scale = resultSet.getMetaData().getScale(col);
        colValue = resultSet.getBigDecimal(col).setScale(scale);;
        break;
      }
....

from kafka-connect-jdbc.

We get this on postgres too.

from kafka-connect-jdbc.

This is prohibiting us from using kafka connect jdbc as a source. We are running into incorrect avro typing for decimal and boolean fields. Is this in the pipeline to get fixed soon? Or should we look to running PR code or looking for other solutions.

Thanks.

from kafka-connect-jdbc.

@ryananguiano There's a PR outstanding but it still needs some fixes since the current version can cause ArithmeticExceptions by reducing the precision of the BigDecimal.

from kafka-connect-jdbc.

I had a similar issue and as far as I was able to investigate - setting scale won't work for some JDBC sources.
One problem I had in particular was that Oracle DB will not provide proper metadata for numeric columns - especially if the column was constructed via aggregates (like sum, min, etc.). It will say the scale is 0 even though you have a floating point value as a result.

The way I was able to address some of the issues was to use an explicit CAST in my queries, where I was able to specify the precision and scale or use another data type (DOUBLE instead of NUMERIC).

Hope that helps.

from kafka-connect-jdbc.

@clumsy: how did you fix the oracle cast?
I tried Select cast( ... as integer) from tab, and nothing seems to work!

from kafka-connect-jdbc.

@ynaguib Using current kafka-connect-jdbc I was only able to get rid of all BigDecimals and their scale problems for Oracle jdbc by casting to FLOAT and DOUBLE (casting to INTEGER will still result in NUMERIC type).
Later I was able to come up with a more refine converter for NUMERIC type that I've described in #101.
So currently I'm using a custom build with my patch and it works just fine.

from kafka-connect-jdbc.

Any progress on pull request https://github.com/confluentinc/kafka-connect-jdbc/pull/89? Encountered same issue for data type Number in an Oracle DB using kafka-connect-jdbc-3.1.1.jar.

ERROR Task test-booking-source-0 threw an uncaught and unrecoverable exception (org.apache.kafka.connect.runtime.WorkerTask:142)
org.apache.kafka.connect.errors.DataException: BigDecimal has mismatching scale value for given Decimal schema
	at org.apache.kafka.connect.data.Decimal.fromLogical(Decimal.java:69)
	at org.apache.kafka.connect.json.JsonConverter$17.convert(JsonConverter.java:232)
[...]

from kafka-connect-jdbc.

I think @cotedm may have fixed this here https://github.com/confluentinc/kafka-connect-jdbc/pull/177/files#diff-d5992997144b4c23572648ca6331eac5R412

from kafka-connect-jdbc.

As @shikhar has pointed out PR #177 should remedy this. Since it has recently been merged I'll mark this as closed for now.

from kafka-connect-jdbc.