Hi Peng, In the stack trace it looks like it thinks the value of sql.args.3.value is "2016-04" rather than "2016-04-29 00:00:00.000".
Can you double check the value of sql.args.3.value? You can look at the flow file in provenance and look at the attributes. -Bryan On Fri, Sep 9, 2016 at 7:15 AM, Li, Peng <[email protected]> wrote: > Bryan, > > I correct the date in json file to "2016-04-29 00:00:00.000" but still get > the same error in PutSQL, in log the error looks like: > 2016-09-09 13:04:34,939 ERROR [Timer-Driven Process Thread-8] > o.apache.nifi.processors.standard.PutSQL > org.apache.nifi.processor.exception.ProcessException: The value of the > sql.args.3.value is '2016-04', which cannot be converted to a timestamp > at org.apache.nifi.processors. > standard.PutSQL.setParameters(PutSQL.java:630) > ~[nifi-standard-processors-1.0.0.jar:1.0.0] > at > org.apache.nifi.processors.standard.PutSQL.onTrigger(PutSQL.java:241) > ~[nifi-standard-processors-1.0.0.jar:1.0.0] > at > org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27) > [nifi-api-1.0.0.jar:1.0.0] > at org.apache.nifi.controller.StandardProcessorNode. > onTrigger(StandardProcessorNode.java:1064) [nifi-framework-core-1.0.0. > jar:1.0.0] > at org.apache.nifi.controller.tasks. > ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:136) > [nifi-framework-core-1.0.0.jar:1.0.0] > at org.apache.nifi.controller.tasks. > ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:47) > [nifi-framework-core-1.0.0.jar:1.0.0] > at org.apache.nifi.controller.scheduling. > TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:132) > [nifi-framework-core-1.0.0.jar:1.0.0] > at > java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) > [na:1.8.0_72] > at > java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) > [na:1.8.0_72] > at java.util.concurrent.ScheduledThreadPoolExecutor$ > ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) > [na:1.8.0_72] > at java.util.concurrent.ScheduledThreadPoolExecutor$ > ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) > [na:1.8.0_72] > at > java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) > [na:1.8.0_72] > at > java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) > [na:1.8.0_72] > at java.lang.Thread.run(Thread.java:745) [na:1.8.0_72] > Caused by: java.text.ParseException: Unparseable date: "2016-04" > at java.text.DateFormat.parse(DateFormat.java:366) > ~[na:1.8.0_72] > at org.apache.nifi.processors. > standard.PutSQL.setParameter(PutSQL.java:798) > ~[nifi-standard-processors-1.0.0.jar:1.0.0] > at org.apache.nifi.processors. > standard.PutSQL.setParameters(PutSQL.java:626) > ~[nifi-standard-processors-1.0.0.jar:1.0.0] > ... 13 common frames omitted > > > Thanks > Peng > > From: Li, Peng > Sent: Friday, 9. September 2016 12:53 > To: '[email protected]' <[email protected]> > Subject: Re: PutSQL error sql.arg3.value cannot convert to timestamp > > Bryan, > > Thank you for pointing to the issue with date format (three digits). > I will test whether it is the cause. > > As you guess, I use ConvertAvroToJSON first and then ConvertJSONToSQL for > that task. > > Thanks, > Peng > > > > -----Original Message----- > > On Mon, 05 Sep 2016 18:52:33 GMT Bryan Bende [email protected]<mailto: > [email protected]> wrote: > > > Hello, > > PutSQL should be able to accept a long as an epoch timestamp, or a string > with the format yyyy-MM-dd HH:mm:ss.SSS > > I'm wondering if its the precision on the milliseconds, in your example > there is one zero, but the date format has three digits. > > Also, how are you producing the flow files going into PutSQL? are you > converting the Avro from table A to JSON, then ConvertJSONToSQL? > > Thanks, > > Bryan > > > > On Sat, Sep 3, 2016 at 4:28 PM, Li, Peng <[email protected]<mailto:peng. > [email protected]>> wrote: > > > Hi NIFI expect, > > > > > > > > During my play with NIFI, I got an issue by using PutSQL with the message > > "...sql.arg3.value cannot convert to timestamp... ". > > > > The flow looks like: > > > > What plan to do is : run sql select from table A and insert the > > sql-results again into another DB table B as backup. Table B has exactly > > the same definition as table A. > > > > The output JSON file (through PutFile) shows the correct content with > DATE > > output like "2016-04-29 00:00:00.0". But the PutSQL Processor at the end > > failed with the error given above. > > > > > > > > Do I forget something by setting or is it an internal bug? NIFI 1.0.0 and > > Oracle 11gR2 are used. > > > > > > > > Thank you! > > > > > > > > Best regards > > > > Peng > >
