Hi Sven, That is interesting. Looking at the JDBC Types class, there is no BINARY_DOUBLE type - and no constant with a value of 101. So it appears to be a non-standard type.
So the script that you have in place is certainly one alternative. It is worth noting, though, that ConvertJsonToSql and PutSQL are older implementations an PutDatabaseRecord should be preferred. This allows you to send your JSON directly to the database without that intermediate step of converting the JSON into SQL. Recommend you give that a try and see if it works for you. Thanks -Mark On Jan 18, 2022, at 9:12 AM, Sven Ritter <[email protected]<mailto:[email protected]>> wrote: 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
