[
https://issues.apache.org/jira/browse/NIFI-2829?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15892563#comment-15892563
]
ASF GitHub Bot commented on NIFI-2829:
--------------------------------------
Github user mattyb149 commented on a diff in the pull request:
https://github.com/apache/nifi/pull/1524#discussion_r103969033
--- Diff:
nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/test/java/org/apache/nifi/processors/standard/TestPutSQL.java
---
@@ -352,6 +352,77 @@ public void
testUsingTimestampValuesWithFormatAttribute() throws InitializationE
}
@Test
+ public void testUsingDateTimeValuesWithFormatAttribute() throws
InitializationException, ProcessException, SQLException, IOException,
ParseException {
+ final TestRunner runner = TestRunners.newTestRunner(PutSQL.class);
+ try (final Connection conn = service.getConnection()) {
+ try (final Statement stmt = conn.createStatement()) {
+ stmt.executeUpdate("CREATE TABLE TIMESTAMPTEST3 (id
integer primary key, ts1 TIME, ts2 DATE)");
+ }
+ }
+
+ runner.addControllerService("dbcp", service);
+ runner.enableControllerService(service);
+ runner.setProperty(PutSQL.CONNECTION_POOL, "dbcp");
+
+ final String dateStr = "2002-02-02";
+ final String timeStr = "12:02:02";
+
+ Map<String, String> attributes = new HashMap<>();
+ attributes.put("sql.args.1.type", String.valueOf(Types.TIME));
+ attributes.put("sql.args.1.value", timeStr);
+ attributes.put("sql.args.1.format", "ISO_LOCAL_TIME");
+ attributes.put("sql.args.2.type", String.valueOf(Types.DATE));
+ attributes.put("sql.args.2.value", dateStr);
+ attributes.put("sql.args.2.format", "ISO_LOCAL_DATE");
+
+ runner.enqueue("INSERT INTO TIMESTAMPTEST3 (ID, ts1, ts2) VALUES
(1, ?, ?)".getBytes(), attributes);
+
+ attributes = new HashMap<>();
+ attributes.put("sql.args.1.type", String.valueOf(Types.TIME));
+ attributes.put("sql.args.1.value", "68522000");
+ attributes.put("sql.args.2.type", String.valueOf(Types.DATE));
+ attributes.put("sql.args.2.value", "1012633200000");
+
+ runner.enqueue("INSERT INTO TIMESTAMPTEST3 (ID, ts1, ts2) VALUES
(2, ?, ?)".getBytes(), attributes);
+
+ attributes = new HashMap<>();
+ attributes.put("sql.args.1.type", String.valueOf(Types.TIME));
+ attributes.put("sql.args.1.value", "120202000");
+ attributes.put("sql.args.1.format", "HHmmssSSS");
+ attributes.put("sql.args.2.type", String.valueOf(Types.DATE));
+ attributes.put("sql.args.2.value", "20020202");
+ attributes.put("sql.args.2.format", "yyyyMMdd");
+
+ runner.enqueue("INSERT INTO TIMESTAMPTEST3 (ID, ts1, ts2) VALUES
(3, ?, ?)".getBytes(), attributes);
+
+ runner.run();
+
+ runner.assertAllFlowFilesTransferred(PutSQL.REL_SUCCESS, 3);
+
+ try (final Connection conn = service.getConnection()) {
+ try (final Statement stmt = conn.createStatement()) {
+ final ResultSet rs = stmt.executeQuery("SELECT * FROM
TIMESTAMPTEST3 ORDER BY ID");
+ assertTrue(rs.next());
+ assertEquals(1, rs.getInt(1));
+ assertEquals(68522000L, rs.getTime(2).getTime());
+ assertEquals(1012633200000L, rs.getDate(3).getTime());
--- End diff --
Same goes for this guy. Are the second and third flow files supposed to
put in UTC-based dates/times? Or will they be local as well? If the latter then
the same change of constants will be needed for the asserts below.
> PutSQL assumes all Date and Time values are provided in Epoch
> -------------------------------------------------------------
>
> Key: NIFI-2829
> URL: https://issues.apache.org/jira/browse/NIFI-2829
> Project: Apache NiFi
> Issue Type: Bug
> Components: Core Framework
> Reporter: Paul Gibeault
> Assignee: Peter Wicks
> Original Estimate: 24h
> Remaining Estimate: 24h
>
> This bug is the same as NIFI-2576 only extended to data types DATE and TIME.
> https://issues.apache.org/jira/browse/NIFI-2576
> When PutSQL sees a DATE or TIME data type it assumes that it's being provided
> as a Long in Epoch format.
> This doesn't make much sense since the Query Database tools that return Avro
> return DATES and TIME values as strings; and thus following the
> Avro->JSON->JSON To SQL Route leads to DATE and TIME fields as being strings.
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
