nifi-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Matt Burgess <mattyb...@apache.org>
Subject Re: PutSQL Processor
Date Tue, 17 Oct 2017 13:50:25 GMT
Shankha,

PutSQL works by creating batches using a PreparedStatement, then
executing those batches using the incoming flow files (up to the value
of the Batch Size parameter). In order to support that, auto-commit
has to be false or else each statement would have to be executed
individually, causing a performance problem. If your Oracle DB or
driver does not support setting auto-commit to false, then PutSQL will
not work. However if you are not using the sql.args.N.type/value
attributes to set the types/values of your columns, meaning you have
an explicit SQL statement in your flow file, you can try ExecuteSQL
instead. Prior to the (upcoming) release of NiFi 1.5.0, you should be
able to use ExecuteSQL as if it were PutSQL, and you should get empty
flow files out (for things like INSERTs that don't return a result
set). As of NiFi 1.5.0, I'm not sure you'll get flow files out using
this technique, due to support for multiple result sets (NIFI-3432
[1]), but it should still execute the SQL statements, you may just not
get any output from that processor.

A possible "improvement" might be to have a different code path if
only one flow file is retrieved from the session; that would not
require disabling auto-commit but would suffer from the performance
hit I mentioned before. Having said that, it seems like too much added
complexity to get around this particular issue, it's the first I've
seen of it, but if it is a common issue with various DBs then we can
take a look.

Regards,
Matt

[1] https://issues.apache.org/jira/browse/NIFI-3432


On Tue, Oct 17, 2017 at 5:53 AM, shankhamajumdar
<shankha.majumdar@lexmark.com> wrote:
> Hi,
>
> I am trying to insert some data in oracle table using PutSQL Processor. But
> getting the below error.
>
> 2017-10-17 14:53:58,409 ERROR [Timer-Driven Process Thread-1]
> o.apache.nifi.processors.standard.PutSQL
> PutSQL[id=24b8613d-015f-1000-2f70-88d0c843300b]
> org.apache.nifi.processors.standard.PutSQL$$Lambda$209/1062828301@1098de1
> failed to process due to
> org.apache.nifi.processor.exception.ProcessException: Failed to disable auto
> commit due to java.sql.SQLFeatureNotSupportedException: Disabling
> auto-commit mode not supported; rolling back session: {}
> org.apache.nifi.processor.exception.ProcessException: Failed to disable auto
> commit due to java.sql.SQLFeatureNotSupportedException: Disabling
> auto-commit mode not supported
>
> Regards,
> Shankha
>
>
>
>
> --
> Sent from: http://apache-nifi-developer-list.39713.n7.nabble.com/

Mime
View raw message