[
https://issues.apache.org/jira/browse/NIFI-5780?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16678538#comment-16678538
]
ASF GitHub Bot commented on NIFI-5780:
--------------------------------------
Github user patricker commented on a diff in the pull request:
https://github.com/apache/nifi/pull/3156#discussion_r231603266
--- Diff:
nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/test/java/org/apache/nifi/processors/standard/TestExecuteSQLRecord.java
---
@@ -350,6 +352,141 @@ public void invokeOnTriggerRecords(final Integer
queryTimeout, final String quer
assertEquals(durationTime, fetchTime + executionTime);
}
+ @Test
+ public void testPreQuery() throws Exception {
+ // remove previous test database, if any
+ final File dbLocation = new File(DB_LOCATION);
+ dbLocation.delete();
+
+ // load test data to database
+ final Connection con = ((DBCPService)
runner.getControllerService("dbcp")).getConnection();
+ Statement stmt = con.createStatement();
+
+ try {
+ stmt.execute("drop table TEST_NULL_INT");
+ } catch (final SQLException sqle) {
+ }
+
+ stmt.execute("create table TEST_NULL_INT (id integer not null,
val1 integer, val2 integer, constraint my_pk primary key (id))");
+
+ runner.setIncomingConnection(true);
+ runner.setProperty(ExecuteSQL.SQL_PRE_QUERY, "insert into
TEST_NULL_INT values(1,2,3);insert into TEST_NULL_INT values(4,5,6)");
--- End diff --
I know these tests were easy to write with insert/delete, but they don't
really show why this feature is needed.
The idea is that we need to configure the DBCP connection in some way, but
that putting two ExecuteSQL processors together might cause us to use different
connections from the connection pool.
Could you try changing them to set Derby session properties?Maybe something
from here? https://db.apache.org/derby/docs/10.1/ref/rrefsetdbpropproc.html
This one looked good, it tells Derby to capture runtime statistics for the
current connection, and then turns them back off after, so a legitimate use
case.
Pre: `CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)`
Post: `CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0)`
> Add pre and post statements to ExecuteSQL and ExecuteSQLRecord
> --------------------------------------------------------------
>
> Key: NIFI-5780
> URL: https://issues.apache.org/jira/browse/NIFI-5780
> Project: Apache NiFi
> Issue Type: Improvement
> Affects Versions: 1.8.0
> Reporter: Deon Huang
> Assignee: Deon Huang
> Priority: Minor
>
> Sometimes we might need to set up session relate configuration before or
> after query.
> For example:
> Pre query can be used for session relate setting like our use case Teradata
> Query Banding.
> Same feature (pre query and post query) is added to SelectHiveQL in
> https://issues.apache.org/jira/browse/NIFI-5044
> Planning to add this feature to ExecuteSQL and ExecuteSQLRecord processors.
> If pre or post statement fail, will not produce resultset flowfile.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
