Search code examples
sqlapache-nifiminify

ExecuteSQL not working after replacetext for dynamic query input

I am trying use executeSQL processor in nifi to run a dynamic query,but the processor always queued to failure stating 'invalid column index'

my configuration is as below,

fetchsftp->updateattribute->putsql->wait->replacetext->executesql

fetchsftp-> take csv file from sftp folder
updateattribute->to set batch_id as attribute to flowfile
putsql-> process the csv and put into database
wait -> wait until all data are inserted into database
replacetext-> set batch_id attribute as dynamic value to the query
   **select * from table where column_name=${batch_id}**
executesql->only the database connection pooling is given and select query is set as empty.

Error:

Unable to execute SQL select query SELECT * FROM TABLE_NAME WHERE COLUMN_NAME=1111111000 for StandardFlowFileRecord[uuid=85310e67-8b4f-4548-b6b0-a584583e0b39,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1683890472369-111, container=default, section=111], offset=772649, length=40],offset=0,name=csvfilename,size=40] due to Invalid column index; routing to failure: java.sql.SQLException: Invalid column index

Solution

  • Actually Executesql is appending Sql.args available from incoming flowfile,which is not vallid in my scenario.

    So i added updateAttribute between wait and replaceText processor, Inside updateAttribute i have set the below property to delete the sql.args from incoming flowfile.

    Delete Attributes Expression=sql\.args\..*

    And this resolved the issue.