Apache NiFi :Convert JSONtoSQL (Oracle Database)

Question

I wanted to convert my JSON string to SQL statement by using ConvertJSONtoSQL processor. example: JSON string -

{"cpuwait":"0.0","servernamee":"mywindows","cpusys":"5.3","cpuidle":"77.6","datee":"29-SEP-2016","timee":"00:01:33","cpucpuno":"CPU01","cpuuser":"17.1"}

Table structure in oracle db -

CREATE TABLE cpu (
  datee varchar2(15) DEFAULT NULL,
  timee varchar2(10) DEFAULT NULL,
  servernamee varchar2(20) DEFAULT NULL,
  cpucpuno varchar2(4) DEFAULT NULL,
  cpuuser varchar2(5) DEFAULT NULL,
  cpusys varchar2(5) DEFAULT NULL,
  cpuwait varchar2(5) DEFAULT NULL,
  cpuidle varchar2(5) DEFAULT NULL
);

Configuration used for MySQL Database:

Database connection url:jdbc:mysql://localhost:3306/testnifi

Database Driver classname:com.mysql.jdbc.Driver

I was successfully connected to MySQL using(DBCP connection pool) JDBC url,username and password. ConvertJSONtoSQL processor successfully worked there and I'm getting valid sql insert statement as output. But when i was trying the same with Oracle Database I'm getting

ERROR [Timer-Driven Process Thread-6] o.a.n.p.standard.ConvertJSONToSQL java.sql.SQLException: Stream has already been closed

My configuration for Oracle db connection:

I searched for the error in google but I found that this error will occur when Long Datatypes were used in database tables but I'm not using them. I went through the source code of ConvertJSONtoSQL processor(following stack trace) and tried to implement the same in eclipse where I'm not getting any error ,I can connect to database and make queries.

So is there any mistake in my configuration?

Nifi version - 0.7.0/1.0(i'm getting same error in both)

java version - java8

Oracle DB version - Oracle Database 11g Express Edition

Complete Stack trace:

2016-10-19 07:10:06,557 ERROR [Timer-Driven Process Thread-6] o.a.n.p.standard.ConvertJSONToSQL java.sql.SQLException: Stream has already been closed at oracle.jdbc.driver.LongAccessor.getBytesInternal(LongAccessor.java:156) ~[ojdbc6.jar:11.2.0.1.0] at oracle.jdbc.driver.LongAccessor.getBytes(LongAccessor.java:126) ~[ojdbc6.jar:11.2.0.1.0] at oracle.jdbc.driver.LongAccessor.getString(LongAccessor.java:201) ~[ojdbc6.jar:11.2.0.1.0] at oracle.jdbc.driver.T4CLongAccessor.getString(T4CLongAccessor.java:427) ~[ojdbc6.jar:11.2.0.1.0] at oracle.jdbc.driver.OracleResultSetImpl.getString(OracleResultSetImpl.java:1251) ~[ojdbc6.jar:11.2.0.1.0] at oracle.jdbc.driver.OracleResultSet.getString(OracleResultSet.java:494) ~[ojdbc6.jar:11.2.0.1.0] at org.apache.commons.dbcp.DelegatingResultSet.getString(DelegatingResultSet.java:263) ~[na:na] at org.apache.nifi.processors.standard.ConvertJSONToSQL$ColumnDescription.from(ConvertJSONToSQL.java:677) ~[nifi-standard-processors-0.7.0.jar:0.7.0] at org.apache.nifi.processors.standard.ConvertJSONToSQL$TableSchema.from(ConvertJSONToSQL.java:621) ~[nifi-standard-processors-0.7.0.jar:0.7.0] at org.apache.nifi.processors.standard.ConvertJSONToSQL.onTrigger(ConvertJSONToSQL.java:267) ~[nifi-standard-processors-0.7.0.jar:0.7.0] at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27) [nifi-api-0.7.0.jar:0.7.0] at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1054) [nifi-framework-core-0.7.0.jar:0.7.0] at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:136) [nifi-framework-core-0.7.0.jar:0.7.0] at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:47) [nifi-framework-core-0.7.0.jar:0.7.0] at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:127) [nifi-framework-core-0.7.0.jar:0.7.0] at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source) [na:1.7.0_40] at java.util.concurrent.FutureTask.runAndReset(Unknown Source) [na:1.7.0_40] at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(Unknown Source) [na:1.7.0_40] at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source) [na:1.7.0_40] at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) [na:1.7.0_40] at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [na:1.7.0_40] at java.lang.Thread.run(Unknown Source) [na:1.7.0_40

Answer 1

It seems a bug in Oracle driver. See:

1. https://blog.jooq.org/2015/12/30/oracle-long-and-long-raw-causing-stream-has-already-been-closed-exception/
   2. Hibernate custom type to avoid 'Caused by: java.sql.SQLException: Stream has already been closed'

The item 2 give me the workaround. Basically add in bootstrap.conf the following argument:

java.arg.xx=-Doracle.jdbc.useFetchSizeWithLongColumn=true