Support Questions
Find answers, ask questions, and share your expertise

Issue with date datatype of PutSQL processor for NIFI1.1.1

New Contributor

Hi,

I'm using nifi to transfer data from one database to another. Both database is psql.

The template is like: ExecuteSQL -> ConvertAvrotoJson -> ConvertJsontoSQL -> PutSQL.

One of the columns's datatype is date and if the processor include this colums the PutSQL will return an error:

"ProcessException: The value of the sql.args.2.value is '2017-01-10', which cannot be converted into the necessary data type; routing to failure: org.apache.nifi.processor.exception.ProcessException: The value of the sql.args.2.value is '2017-01-10', which cannot be converted into the necessary data type"

If I remove this column(not select this column in ExecuteSQL) and then Nifi will work fine. Anyone has idea about this?

Thanks!

2 REPLIES 2

Re: Issue with date datatype of PutSQL processor for NIFI1.1.1

Super Guru

Dates are always tricky. You need to make sure that the conversion to JSON and to SQL is getting the correct date format.

You are getting a number format exception

https://github.com/apache/nifi/blob/master/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-proce...

Either the table has the wrong type or it's converting it to a number. Check the logs and data provenance.

http://apache-nifi.1125220.n5.nabble.com/Failure-to-insert-update-into-SQL-integer-field-td13054.htm...

See:

https://community.hortonworks.com/questions/48905/date-problems-with-convertjsontosql-or-putsql-in-n...

https://nifi.apache.org/docs/nifi-docs/html/user-guide.html#data-provenance

Re: Issue with date datatype of PutSQL processor for NIFI1.1.1

Explorer

can you please provide, what exactly needs to done( process) even I'm getting same issue

problem with date, datetime, varbinary datatypes.

please help me\

Thanks

Ravi