I think Toivo had some thoughts on this, but IIRC the basic idea is not to use getColumnWidth when it doesn't align with literals. Either convert and trim or skip the trim I think?
> On Sep 10, 2016, at 10:38 PM, Peter Wicks (pwicks) <[email protected]> wrote: > > Bryan/Matt, > > Wouldn't a JDBC truncation blacklist be easiest? If the destination type is > timestamp/any other applicables, then don't take the substring? > > --Peter > > -----Original Message----- > From: Bryan Bende [mailto:[email protected]] > Sent: Saturday, September 10, 2016 1:00 PM > To: [email protected] > Subject: Re: Re: PutSQL error sql.arg3.value cannot convert to timestamp > > Good find Matt, from a quick glance it looks like NIFI-2625 is definitely > related, although it is more specific to when the timestamp is in > milliseconds vs. nano-seconds. > > Part of the problem is that PutSQL can handle the epoch OR a date string, but > the Avro produced by ExecuteSQL/QueryDatabaseTable always calls > value.toString() for timestamps which seems to usually be yyyy-MM-dd HH:mm:ss. > We end up passing around a date string with a JDBC type of 93 (timestamp), so > comparing the size of the column in the target database would never line up. > > Should we modify JDBCCommon so that if rs.getObject(i) is a date/timestamp we > take epoch and put that as the string value, rather than relying on > value.toString() ?? > > https://github.com/apache/nifi/blob/master/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/main/java/org/apache/nifi/processors/standard/util/JdbcCommon.java#L153-L157 > >> On Sat, Sep 10, 2016 at 2:46 PM, Matt Burgess <[email protected]> wrote: >> >> 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 <[email protected]> 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 <[email protected]> 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 [email protected]<mailto: >>>> [email protected]> 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. >>>> >>>> >>>> >>
