I wanted to convert a JSON string to an SQL statement using the 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 );
The configuration used for the MySQL database:
Database Connection URL: jdbc: mysql: // localhost: 3306 / testnifi
Database Driver Name: com.mysql.jdbc.Driver
I was successfully connected to MySQL using the (DBCP connection pool) JDBC url, username and password. The ConvertJSONtoSQL processor worked successfully there, and I get a valid sql insert statement as output. But when I did the same with Oracle Database, I get
ERROR [Thread-6 Timer-Driven Process] oanpstandard.ConvertJSONToSQL java.sql.SQLException: thread is already closed
My configuration for connecting Oracle db:
I searched for an error in google, but found that this error will occur when Long Datatypes are used in the database tables, but I do not use them. I went through the source code of the ConvertJSONtoSQL processor (after the stack trace) and tried to implement the same thing in eclipse, where I get no errors, I can connect to the database and make queries.
So is there an error in my configuration?
Nifi version is 0.7.0 / 1.0 (I get the same error in both)
java version - java8
Oracle DB Version - Oracle Database 11g Express Edition
Full stack trace:
2016-10-19 07: 10: 06,557 ERROR [Timer-Driven Process Thread-6] oanpstandard.ConvertJSONToSQL java.sql.SQLException: stream is already 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) ~ [ojdbc 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-processor-0.7.0.ja r: 0.7.0] at org.apache.nifi.processors.standard.ConvertJSONToSQL $ TableSchema.from (ConvertJSONToSQL.java:621) ~ [nifi-standard-processor-0.7.0.jar: 0.7.0] on org. apache.nifi.processors.standard.ConvertJSONToSQL.onTrigger (ConvertJSONToSQL.java:267) ~ [nifi-standard-processor-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.con current.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 $ ScheduledFutureask. access $ 301 (Unknown source) [na: 1.7.0_40] in java.util.concurrent.ScheduledThreadPoolExecutor $ ScheduledFutureTask.run (Unknown source) [na: 1.7.0_40] in java.util.concurrent.ThreadPoolExecutor.runWorker (Unknown source) [na: 1.7.0_40] in java.util.concurrent.ThreadPoolExecutor $ Worker.run (Unknown source) [na: 1.7.0_40] on java.lang.Thread.run (Unknown source) [na: 1.7.0_40