PutSQL in combination with ConvertJSONToSQL gives java.sql.SQLException: Invalid column type for Orcale DataType BINARY_DOUBLE

[Sven Ritter]

Hi all,

 

It’s my first post here and I hope I give all needed information to help me 😊

 

We are using PutSQL (1.15.2) in combination with ConvertJSONToSQL to perform 
inserts/updates into Oracle 19 DB.

It seems that Nifi has problems to handle the BINARY_DOUBLE data type.

 

The ConvertJSONToSQL processor produces the attributes sql.args.N.type = ‘101’ 
and sql.args.N.value = ‘0.32’.

The PutSQL processor fails with java.sql.SQLException: Invalid column type

 

When I pass the sql statement directly as SQL Statement in PutSQL it works fine.

 

As a workaround I change by groovy script all sql.args.N.type = ‘101’ 
(BINARY_DOUBLE) to sql.args.N.type = ‘6’ (FLOAT), which is working for now.

 

When I read the source code correctly, it seems that there is no case for 
BINARY_DOUBE and it jumps to the default (at 
org.apache.nifi.util.db.JdbcCommon.setParameter(JdbcCommon.java:863))

 

Did I forget something essential or is the data type BINARY_DOUBLE not 
supported?

 

Here the log entry:

 

2022-01-18 13:10:38,802 ERROR [Timer-Driven Process Thread-5] 
o.apache.nifi.processors.standard.PutSQL 
PutSQL[id=40bb12a1-cf65-3999-6d1d-d98c308f52fb] Failed to update database for 
StandardFlowFileRecord[uuid=7fd2db89-c559-44ce-b934-d47a8b6195a9,claim=StandardContentClaim
 [resourceClaim=StandardResourceClaim[id=1642429584073-1, container=default, 
section=1], offset=599401, 
length=761],offset=0,name=2beadf83-626f-4ec6-aa32-e1232db34a68,size=761] due to 
java.sql.SQLException: Invalid column type; it is possible that retrying the 
operation will succeed, so routing to retry: java.sql.SQLException: Invalid 
column type

java.sql.SQLException: Invalid column type

                at 
oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:8148)

                at 
oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:7639)

                at 
oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:8213)

                at 
oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:227)

                at 
org.apache.commons.dbcp2.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:529)

                at 
org.apache.commons.dbcp2.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:529)

                at sun.reflect.GeneratedMethodAccessor919.invoke(Unknown Source)

                at 
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

                at java.lang.reflect.Method.invoke(Method.java:498)

                at 
org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:254)

                at 
org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.access$100(StandardControllerServiceInvocationHandler.java:38)

                at 
org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler$ProxiedReturnObjectInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:240)

                at com.sun.proxy.$Proxy192.setObject(Unknown Source)

                at 
org.apache.nifi.util.db.JdbcCommon.setParameter(JdbcCommon.java:863)

                at 
org.apache.nifi.util.db.JdbcCommon.setParameters(JdbcCommon.java:696)

                at 
org.apache.nifi.processors.standard.PutSQL.lambda$null$4(PutSQL.java:344)

                at 
org.apache.nifi.processor.util.pattern.ExceptionHandler.execute(ExceptionHandler.java:127)

                at 
org.apache.nifi.processors.standard.PutSQL.lambda$new$5(PutSQL.java:342)

                at 
org.apache.nifi.processors.standard.PutSQL.lambda$new$7(PutSQL.java:387)

                at 
org.apache.nifi.processor.util.pattern.PutGroup.putFlowFiles(PutGroup.java:91)

                at 
org.apache.nifi.processor.util.pattern.Put.onTrigger(Put.java:103)

                at 
org.apache.nifi.processors.standard.PutSQL.lambda$onTrigger$19(PutSQL.java:635)

                at 
org.apache.nifi.processor.util.pattern.PartialFunctions.onTrigger(PartialFunctions.java:120)

                at 
org.apache.nifi.processor.util.pattern.RollbackOnFailure.onTrigger(RollbackOnFailure.java:184)

                at 
org.apache.nifi.processors.standard.PutSQL.onTrigger(PutSQL.java:635)

                at 
org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1273)

                at 
org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:214)

                at 
org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:103)

                at org.apache.nifi.engine.FlowEngine$2.run(FlowEngine.java:110)

                at 
java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)

                at 
java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)

                at 
java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)

                at 
java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)

                at 
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)

                at 
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)

                at java.lang.Thread.run(Thread.java:748)

 

Thanks in advance 

Sven