[ https://issues.apache.org/jira/browse/NIFI-3372?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Pierre Villard resolved NIFI-3372. ---------------------------------- Resolution: Fixed Fix Version/s: 1.2.0 > 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: Extensions > Affects Versions: 1.0.1 > Environment: SqlServer > Reporter: Ryan Persaud > Fix For: 1.2.0 > > > 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.15#6346)