[jira] [Updated] (NIFI-3372) PutSQL cannot insert True for a BIT field when using ConvertJSONToSQL
[
https://issues.apache.org/jira/browse/NIFI-3372?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ryan Persaud updated NIFI-3372:
---
Description:
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:
{code}
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"));
}
{code}
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.
{code}
switch (jdbcType) {
case Types.BIT:
stmt.setBoolean(parameterIndex, "1".equals(parameterValue)
|| "t".equalsIgnoreCase(parameterValue) ||
Boolean.parseBoolean(parameterValue));
break;
case Types.BOOLEAN:
{code}
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:
{code}
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)
{code}
I modified PutSQL and created some tests in TestPutSQL, and I'll create a PR
shortly.
was:
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.
[jira] [Updated] (NIFI-3372) PutSQL cannot insert True for a BIT field when using ConvertJSONToSQL
[
https://issues.apache.org/jira/browse/NIFI-3372?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Ryan Persaud updated NIFI-3372:
---
Description:
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.
was:
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
[jira] [Updated] (NIFI-3372) PutSQL cannot insert True for a BIT field when using ConvertJSONToSQL
[
https://issues.apache.org/jira/browse/NIFI-3372?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pierre Villard updated NIFI-3372:
-
Component/s: (was: Core Framework)
Extensions
> 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)
