Charles Bryan Clifford created NIFI-2625:
--------------------------------------------
Summary: ConvertJsonToSql truncates SQL timestamp values
Key: NIFI-2625
URL: https://issues.apache.org/jira/browse/NIFI-2625
Project: Apache NiFi
Issue Type: Bug
Affects Versions: 0.7.0
Environment: Ubuntu 16.04
Reporter: Charles Bryan Clifford
The ConvertJsonToSql processor is incorrectly initializing colSize.
In the ColumnDescription constructor method, ResultSet.getInt("COLUMN_SIZE") is
used to initialize colSize. That method appear to return a size of a Java
TimeStamp data type (which is in milliseconds).
When generateInsert and generateUpdate parse the in-flowing JSON field node's
text value in this manner:
fieldValue = fieldValue.substring(0, colSize);
And, next, do the following:
attributes.put("sql.args." + fieldCount + ".value", fieldValue);
Nanoseconds in timestamp field values, present in the in-flowing JSON content,
are not making it into the timestamp field values stored in sql.args.N.value
FlowFile attribute.
My source timestamp values are in nano seconds, and all target database
timestamp columns are likewise in nano seconds.
For your consideration, here's a potential fix to the
ConvertJsonToSql.ColumnDescription timestamp value truncation problem:
public static ColumnDescription from(final ResultSet resultSet) throws
SQLException {
final ResultSetMetaData md = resultSet.getMetaData();
List<String> columns = new ArrayList<>();
HashMap<String,Int> columncache = new HashMap<String,Int>(); // NEW - used to
store column size, as per database service
for (int i = 1; i < md.getColumnCount() + 1; i++) {
columns.add(md.getColumnName(i));
columncache.put(md.getColumnName(i),md.getPrecision(i)); // NEW - get physical
column size as per the database service
}
final String columnName = resultSet.getString("COLUMN_NAME");
final int dataType = resultSet.getInt("DATA_TYPE");
//final int colSize = resultSet.getInt("COLUMN_SIZE");
final int colSize = columncache.get(columnName); // NEW
In this way, the target data type lengths used by the database service (not by
Java) will be used to initalize colSize. This could fix the timestamp value
truncation problem, and other Java-data-type-size conflicts with target
database-service-data-type-size.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
