[jira] [Commented] (NIFI-5835) Add support to use SQL Server rowversion as maximum value column

["Matt Burgess (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/NIFI-5835?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16704198#comment-16704198 ] 

Matt Burgess commented on NIFI-5835:
------------------------------------

I suspect the driver returns the type ROWID or something similar, we'd just need to support that type in the code as well. For these cases it's kind of an "add support when you run into it" kind of thing, it's fairly difficult to handle all the types generically as no DB seems to do things the same way :)

> Add support to use SQL Server rowversion as maximum value column
> ----------------------------------------------------------------
>
>                 Key: NIFI-5835
>                 URL: https://issues.apache.org/jira/browse/NIFI-5835
>             Project: Apache NiFi
>          Issue Type: Improvement
>          Components: Core Framework
>    Affects Versions: 1.8.0
>            Reporter: Charlie Meyer
>            Priority: Major
>
> I'm attempting to do incremental fetch from a Microsoft SQL Server database and would like to use rowversion [1] as my maximum value column. When I configured the processor to use that column, it threw an exception [2].
>  
> [1] [https://docs.microsoft.com/en-us/sql/t-sql/data-types/rowversion-transact-sql?view=sql-server-2017] 
> [2] [https://github.com/apache/nifi/blob/d8d220ccb86d1797f56f34649d70a1acff278eb5/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/main/java/org/apache/nifi/processors/standard/AbstractDatabaseFetchProcessor.java#L456]
>  
> [~alopresto]'s reply on the mailing list:
>  
> Looking at this issue briefly, it seems that the NiFi code explicitly lists the accepted datatypes which can be used, and rowversion is not enumerated. Therefore it throws an exception. I suggest you open a feature request on our Jira page to support this. While it seems proprietary to Microsoft SQL versions, it says on the documentation page:
>  
> Is a data type that exposes automatically generated, unique binary numbers within a database. rowversion is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The rowversion data type is just an incrementing number and does not preserve a date or a time.
>  
> I think we could handle this datatype the same way we handle INTEGER, SMALLINT, TINYINT (or TIMESTAMP, as that is the functional equivalent from MS SQL which is now deprecated) in that switch statement, as it is simply an incrementing 8 byte natural number. However, I would welcome input from someone like [~mattyb149] to see if maybe there is a translation that can be done in the Microsoft-specific driver to a generic integer datatype before it reaches this logic. I would expect SQLServerResultSetMetaData#getColumnType(int column) to perform this translation; perhaps the version of the driver needs to be updated?



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)