[jira] [Created] (NIFI-3372) PutSQL cannot insert True for a BIT field when using ConvertJSONToSQL

["Ryan Persaud (JIRA)" <ji...@apache.org>]

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)