Error to cast String or BigInt when the column type is UInt64/Int128/UInt128/Int256/UInt256 on Nifi

[HanchiMed]

Description

Steps to reproduce

1. create a Clickhouse table with columns :

create table test
(
numliv UInt32,
cdc_changesequence UInt128
)
ENGINE = SharedReplacingMergeTree(cdc_changesequence)
ORDER BY (numliv)

2. create à PutDatabaseRecord on Nifi

3. Insert json with column cdc_changesequence filled by a BigInt

{"numliv" : 54878 , "cdc_changesequence " : 20260219145041580000000000403034229 }

Error Log or Exception StackTrace

Failed to put Records to database for FlowFile[filename=c797411d-7c71-4403-9f92-e204c26849a2]. Routing to failure.: java.io.IOException: Unable to setObject() with value 20260219145041580000000000403034240 at index 208 of type 1111

• Caused by: java.sql.SQLException: Cannot convert OTHER to a ClickHouse one. Consider using java.sql.JDBCType or com.clickhouse.data.ClickHouseDataType

Expected Behaviour

Insertion is functionnal in case of cdc_changesequence is typed as String or BigInt

cdc_changesequence as BigInt
{"numliv" : 54878 , "cdc_changesequence " : 20260219145041580000000000403034229 }

cdc_changesequence as BigInt
{"numliv" : 54878 , "cdc_changesequence " : "20260219145041580000000000403034229" }

Code Example

com.clickhouse.jdbc.JdbcTypeMapping:
public int toSqlType : .......
case UInt64:
case Int128:
case UInt128:
case Int256:
case UInt256:
sqlType = Types.NUMERIC;
break;

com.clickhouse.jdbc.internal.JdbcUtils:

private static Map<ClickHouseDataType, SQLType> generateTypeMap() { ...........
map.put(ClickHouseDataType.Int128, JDBCType.NUMERIC);
map.put(ClickHouseDataType.Int256, JDBCType.NUMERIC);
map.put(ClickHouseDataType.UInt64, JDBCType.NUMERIC);
map.put(ClickHouseDataType.UInt128, JDBCType.NUMERIC);
map.put(ClickHouseDataType.UInt256, JDBCType.NUMERIC);

[chernser]

@HanchiMed
thank you for reporting this!

What version of the driver do you use?
Is it V1 or V2?

Thanks!

[HanchiMed]

Hello,

I used the V1 and the fix is working.

com.clickhouse.jdbc.JdbcTypeMapping:
public int toSqlType : .......
case UInt64:
case Int128:
case UInt128:
case Int256:
case UInt256:
sqlType = Types.NUMERIC;
break;

For V2, I haven’t tested it yet, but I believe we need to apply the same logic (to be verified)

com.clickhouse.jdbc.internal.JdbcUtils:

private static Map<ClickHouseDataType, SQLType> generateTypeMap() { ...........
map.put(ClickHouseDataType.Int128, JDBCType.NUMERIC);
map.put(ClickHouseDataType.Int256, JDBCType.NUMERIC);
map.put(ClickHouseDataType.UInt64, JDBCType.NUMERIC);
map.put(ClickHouseDataType.UInt128, JDBCType.NUMERIC);
map.put(ClickHouseDataType.UInt256, JDBCType.NUMERIC);

Regards

[chernser]

@HanchiMed
thank you!

In support for your fix https://groups.google.com/g/firebird-java/c/g3xZLhIs15Y
This is a valid point.

V1 previously mapped everything to BigDecimal what is not correct.
V2 use OTHER as indicator for an user to use getObject()