PutSQL Processor

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

PutSQL Processor

shankhamajumdar
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/
Reply | Threaded
Open this post in threaded view
|

Re: PutSQL Processor

Matt Burgess-2
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
<[hidden email]> 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/