[
https://issues.apache.org/jira/browse/NIFI-626?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14554931#comment-14554931
]
Toivo Adams commented on NIFI-626:
----------------------------------
When using PreparedStatement processor should obtain database connection from
pool during
@OnScheduled and create PreparedStatement.
Both connection and PreparedStatement instance will be keep open until closed
during @OnUnscheduled.
OnTrigger will reuse prepared statement, provide query parameters and execute
statement.
Correct?
But there two drawbacks when we keep connection always open.
1. Each processor will occupy connection which might be used rarely.
Usually database systems have only limited number open connections available.
And some database vendors license allow only limited number of connections can
be used.
Want more? - pay more!
So some users may prefer to sacrifice some performance but reuse connections to
keep cost down.
2. We can't expect connection is open forever. Temporary network errors may
break connection. When database server is behind firewall, firewall may be
configured in the way what open connections are terminated after specified time
(for example after 10 minutes).
Database connections pools are mostly immune to such problems because they can
check is connection valid and return only valid connections.
If we still want to keep PreparedStatement and connection open over long
period, maybe we should:
1. Give user an option to keep connection open long time or open /return to
pool each time (each onTrigger). Some UI property.
2. Implement some recovery mechanism when prepared statement execution fails
because connection is not valid any more. This should allow to retry query
using new connection and new prepared statement.
Thanks
Toivo
> ExecuteSQL processor for executing arbitrary SQL queries
> --------------------------------------------------------
>
> Key: NIFI-626
> URL: https://issues.apache.org/jira/browse/NIFI-626
> Project: Apache NiFi
> Issue Type: Sub-task
> Reporter: Toivo Adams
> Assignee: Toivo Adams
>
> For example query can be:
> SELECT * FROM orders WHERE orderId = '${order.id}'
> where ${order.id} is FlowFile attribute.
> Result will serialized using Avro.
> Avro gives as possibility to have query Resultset metadata (column name and
> types) included in FlowFile.
> Also Avro should allow streaming.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
