[jira] [Commented] (NIFI-1409) ExecuteSQL gives transfer error when result set has no columns

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

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

Matt Burgess commented on NIFI-1409:
------------------------------------

Two distinct issues here:

- For the JdbcCommon.createSchema() issue, my thought is that an empty result set is totally legitimate, so the resulting flow file should be created and routed to success, even if it has no content. I'm not sure if Avro supports empty schemas, will have to find that out.

- For the ExecuteSQL issue, regardless of how the other issue is handled, if an exception is thrown during onTrigger() and there was no incoming flowfile, then the "transfer relationship" error will occur. The current logic either reuses the incoming flowfile or creates a new one if absent.  That resulting flowfile (called outgoing in code) is the one that should be transferred, not the "incoming" reference.

As an additional point, perhaps it would be helpful to add attribute(s) to the flowfile representing the size (in rows and/or columns) of the result set. 

> ExecuteSQL gives transfer error when result set has no columns
> --------------------------------------------------------------
>
>                 Key: NIFI-1409
>                 URL: https://issues.apache.org/jira/browse/NIFI-1409
>             Project: Apache NiFi
>          Issue Type: Bug
>            Reporter: Matt Burgess
>
> When the result set from ExecuteSQL has no columns, a bulletin is issued saying a flowfile was not transferred:
> 13:35:12 ESTERROR05845c66-73a4-49ff-a973-ad986fb651bf
> ExecuteSQL[id=05845c66-73a4-49ff-a973-ad986fb651bf] Unable to execute SQL select query SELECT * FROM test due to org.apache.nifi.processor.exception.ProcessException: org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.. No incoming flow file to route to failure: org.apache.nifi.processor.exception.ProcessException: org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.
> 13:35:12 ESTERROR05845c66-73a4-49ff-a973-ad986fb651bf
> ExecuteSQL[id=05845c66-73a4-49ff-a973-ad986fb651bf] ExecuteSQL[id=05845c66-73a4-49ff-a973-ad986fb651bf] failed to process due to org.apache.nifi.processor.exception.FlowFileHandlingException: StandardFlowFileRecord[uuid=c9077d5c-d873-4bf2-a8c0-4cc92a6f3309,claim=,offset=0,name=258316166470532,size=0] transfer relationship not specified; rolling back session: org.apache.nifi.processor.exception.FlowFileHandlingException: StandardFlowFileRecord[uuid=c9077d5c-d873-4bf2-a8c0-4cc92a6f3309,claim=,offset=0,name=258316166470532,size=0] transfer relationship not specified
> The problem seems to be in JdbcCommon.createSchema():
> https://github.com/apache/nifi/blob/nifi-0.4.1/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/main/java/org/apache/nifi/processors/standard/util/JdbcCommon.java#L130
> This line asks for the table name for the first column, and when there are no columns, a SQLException is thrown, which becomes a ProcessException in the ExecuteSQL processor. In addition, the exception handling tries to route the flowfile to failure, but for the newly created flowfile (to contain the result set), the reference has since been lost, and thus the flowfile is never transferred.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)