[
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)
