I think we're running into NIFI-2625 [1] and/or NIFI-1613 [2], the column size returned is for the underlying data type, not the String literal it corresponds to.
Regards, Matt [1] https://issues.apache.org/jira/browse/NIFI-2625 [2] https://issues.apache.org/jira/browse/NIFI-1613 On Sat, Sep 10, 2016 at 2:43 PM, Bryan Bende <bbe...@gmail.com> wrote: > Peng, > > Thanks for checking what the value for sql.args.3.value was... the fact > that it is already truncated there and not including the hours/mins/seconds > means something is going wrong before it even gets to PutSQL. > > I was looking at the code in ConvertJsonToSQL and found this code: > > final Integer colSize = desc.getColumnSize(); > > final JsonNode fieldNode = rootNode.get(fieldName); > > if (!fieldNode.isNull()) { > String fieldValue = rootNode.get(fieldName).asText(); > if (colSize != null && fieldValue.length() > colSize) { > fieldValue = fieldValue.substring(0, colSize); > } > attributes.put("sql.args." + fieldCount + ".value", fieldValue); > } > > > What could be happening is that the target DB is saying that the column > size is 7 (comes from desc.getColumnSize() above), and then it sees that > the string value is larger than 7 so it takes the substring from 0 to 6. > It seems weird to me that 7 would be size of a timestamp column, but I > can't come up with any other explanation, unless Oracle's driver is just > doing something unusual. > > If that is what is happening, we probably have a couple of options, and I'm > curious to here what others with more DB experience think... > > 1) We could decide to leave ConvertJsonToSQL as is, and recommend that for > timestamp columns people need to manipulate the JSON between > ConvertAvroToJson & ConvertJsonToSQL, this could probably be done using the > JOLT processor to transform the date string into a long, but not sure. This > would probably be confusing because no one would realize they need to do > this. > > 2) We could completely remove this size check from ConvertJSONToSQL which > can in some cases lead to insert statements that might fail downstream, but > that can be handled by routing the failure relationship of PutSQL > somewhere. We know that PutSQL has logic to handle the epoch or date > string, so it would only be other cases that could fail. > > 3) We could add special logic to ConvertJSONToSQL, similar to that in > PutSQL, where we determined if it was a timestamp column and if it was a > date string then don't perform this size check/truncation, but leave it for > other types. It could be a slippery slope starting to do special logic for > different types of columns. > > We need to consider that a lot of people are going to do > ExecuteSQL/QueryDatabaseTable -> ConvertAvroToJSON -> ConvertJSONToSQL -> > PutSQL but other people may be producing JSON in some other way and using > the ConvertJSONToSQL -> PutSQL part. In the first case we know that we are > storing the date as a string in Avro so we will always run into this case, > but in the second we don't really know. > > Thoughts? > > -Bryan > > > On Sat, Sep 10, 2016 at 9:59 AM, Li, Peng <peng....@hpe.com> wrote: > >> Hi Bryan, >> >> In provenance the attribute sql.args.3.value was given as "2016-04" and >> sql.args.3.type was given as "93". >> >> But as already said in previous mail, i checked the content of the output >> of ConvertAvroToJSON and "2016-04-29 00:00:00.0" was given there, so I >> think this value was given to the following ConvertJSONToSQL processor. >> >> >> >> Perhaps it has nothing to do with format "yyyy-MM-dd HH:mm:ss.SSS" but >> something else? >> >> >> >> Thanks >> >> Peng >> >> >> >> >> -----Original Message----- >> >> >> On Fri, 09 Sep 2016 12:06:35 GMT Bryan Bende bbe...@gmail.com<mailto: >> bbe...@gmail.com> wrote: >> >> Hi Peng, >> >> In the stack trace it looks like it thinks the value of sql.args.3.value is >> "2016-04" rather than "2016-04-29 00:00:00.000". >> >> Can you double check the value of sql.args.3.value? You can look at the >> flow file in provenance and look at the attributes. >> >> >>
