Error when using DB Insert with nullable columns and NaN

[lucaszane]

Hi guys,

I am having a big headache with this. I am trying to insert data into my SQL Server Database with the DB Insert Node. The situation is this: I have some double columns in Knime in wich I may have some NaN values and I have some columns in SQL Server that are of Nullable Decimal type, but when I try to insert, it gives me two types of error:

With no Output Type Mapping changes:
ERROR DB Insert 0:85 Execute failed: Error while adding rows #-100 - #0, reason: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 1 (""): The supplied value is not a valid instance of data type float. Check the source data for invalid values. An example of an invalid value is data of numeric type with scale greater than precision.

When I change the Output Type Mapping for “-> Double -> DECIMAL”, it shows this error:
ERROR DB Insert 0:85 Execute failed: (“NumberFormatException”): null

Could you please help me with this?

Thanks,
Lucas Zane

[pigreco]

Pay attention.
NaN and NULL are different things!
You can insert NULL on double nullable field but you cannot insert a NaN.

Are your NaN values “” (empty string) values.
If the answer is “yes” then you have to substitute these values with NULL (missing value) before DBInsert node. (you can use this hint: replace empty cell with missing (?) globally in table)

[lucaszane]

Thank you, @pigreco

This helped me to solve this out.

My columns were already of Double Type. Then I casted to string, replaced all ‘NaN’ to None with the Python node and then casted again to double with the Number to Double Node and it worked.

It was a bit trick, maybe should be a node to do this kind of replacement.

Thanks,
Lucas Zane