Ryan Persaud created NIFI-3372:
----------------------------------
Summary: PutSQL cannot insert True for a BIT field when using
ConvertJSONToSQL
Key: NIFI-3372
URL: https://issues.apache.org/jira/browse/NIFI-3372
Project: Apache NiFi
Issue Type: Bug
Components: Core Framework
Affects Versions: 1.0.1
Environment: SqlServer
Reporter: Ryan Persaud
As noted in NIFI-1613, using the column width to truncate fields often yields
incorrect and undesired results for non-string fields in ConvertJSONToSQL. I
have encountered a situation where it is impossible to populate a BIT field in
SqlServer with true (1) using ConvertJSONToSQL and PutSQL. The notable
snippets of code are:
org.apache.nifi.processors.standard.ConvertJSONToSQL (449-455):
if (!fieldNode.isNull()) {
String fieldValue = fieldNode.asText();
if (colSize != null && fieldValue.length() > colSize) {
fieldValue = fieldValue.substring(0, colSize);
}
attributes.put("sql.args." + fieldCount + ".value",
fieldValue);
}
org.apache.nifi.processors.standard.PutSQL (757-761):
switch (jdbcType) {
case Types.BIT:
case Types.BOOLEAN:
stmt.setBoolean(parameterIndex,
Boolean.parseBoolean(parameterValue));
break;
java.lang.Boolean (121-123):
public static boolean parseBoolean(String s) {
return ((s != null) && s.equalsIgnoreCase("true"));
}
In PutSQL, the case for BIT has no body or break, so execution proceeds to the
BOOLEAN case. Here, parseBoolean() attempts to parse parameterValue into a
boolean value. Looking at parseBoolean(), we can see that the parameterValue
must contain "true" in order for true to be returned. Since the code in
ConvertJSONToSQL will truncate the string to its first character, the string
sent to PutSQL will never be equal to "true", and parseBoolean() will never
return true.
One easy fix for this issue (below) while ConvertJSONToSQL gets sorted out is
to allow 1 and t (case-insensitive) to also represent true in the BIT case in
PutSQL. Then, we can pass true/false 1/0 to ConvertJSONTOSQL, and still be
able to correctly populate BIT columns. Since we are also ORing a call to
parseBoolean(), this modification should not break any existing NiFi flows that
depend on the current BIT handling of PutSQL.
switch (jdbcType) {
case Types.BIT:
stmt.setBoolean(parameterIndex, "1".equals(parameterValue)
|| "t".equalsIgnoreCase(parameterValue) ||
Boolean.parseBoolean(parameterValue));
break;
case Types.BOOLEAN:
As a stopgap, I am currently using the following Python ExecuteScript processor
in between my ConvertJSONToSQL and PutSQL processors in order to properly
populate BIT fields:
flowFile = session.get()
properties = context.getProperties()
if (flowFile != None):
attributes = flowFile.getAttributes()
for key in attributes.keys():
if attributes[key] == "-7":
value_key = key.replace(".type", ".value")
new_value = "true" if attributes[value_key] == "1" or
attributes[value_key].lower() == "t" or attributes[value_key].lower() == "true"
else "false"
flowFile = session.putAttribute(flowFile, value_key, new_value)
session.transfer(flowFile, REL_SUCCESS)
I modified PutSQL and created some tests in TestPutSQL, and I'll create a PR
shortly.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
