[jira] [Commented] (NIFI-7171) GenerateTableFetch produces SQLException when MS SQL Server table is empty

["Gregory Kline (Jira)" <ji...@apache.org>]

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

Gregory Kline commented on NIFI-7171:
-------------------------------------

Expanding on the behavior and expectations:

When this exception is encountered, the processor rolls back the session, returning the incoming flow file to the queue.  This behavior is ultimately what is most problematic for me, as there is no way to provide alternate processing for the file.

For other database types such as MySQL, the processor successfully generates a sql statement and transfers to the success relationship, and ideally it should behave the same for the SQL Server adapter.

It would be acceptable, however, for the processor to transfer the flow file to "failure" relationship with an attribute set containing the error message.  In this way, at least an alternate processing path can be provided.

> GenerateTableFetch produces SQLException when MS SQL Server table is empty
> --------------------------------------------------------------------------
>
>                 Key: NIFI-7171
>                 URL: https://issues.apache.org/jira/browse/NIFI-7171
>             Project: Apache NiFi
>          Issue Type: Bug
>          Components: Extensions
>    Affects Versions: 1.10.0, 1.11.2
>            Reporter: Gregory Kline
>            Priority: Major
>
> When GenerateTableFetch executes with the following conditions ...
>  # partition size = 0
>  # max value columns are unset
>  # connecting to MS SQL Server
>  # the target table has 0 rows
> ... the processor throws a SQLException "Unable to execute SQL select query SELECT -1 FROM [mydb].[dbo].[tbl_test_01] due to java.sql.SQLException: No rows returned from metadata query: SELECT -1 FROM [mydb].[dbo].[tbl_test_01]" and rolls back the flow file. 
> The issue appears to be introduced in the fix for https://issues.apache.org/jira/browse/NIFI-5855 when -1 was used in place of "COUNT( * )".  For an empty table, the SQL Server driver returns 0 rows for the query "SELECT -1 FROM <table>".  GenerateTableFetch throws a SQLException when the resultset contains 0 rows.
>  
> Version details:
> MS SQL Server: 12.0.6108.1
> JDBC Driver: com.microsoft.sqlserver:mssql-jdbc:7.4.1.jre8
> JDBC Class: com.microsoft.sqlserver.jdbc.SQLServerDriver
>  



--
This message was sent by Atlassian Jira
(v8.3.4#803005)