In nifi groovy script how to execute stored procedure

Question

Using nifi groovy script I am executing stored procedure.

import java.sql.Connection;
import java.sql.PreparedStatement;
import org.apache.nifi.controller.ControllerService

def i;
def lookup = context.controllerServiceLookup
def dbServiceName = databaseConnectionPoolName.value
def dbcpServiceId = lookup.getControllerServiceIdentifiers(ControllerService).find { 
    cs -> lookup.getControllerServiceName(cs) == dbServiceName
}
def conn = lookup.getControllerService(dbcpServiceId)?.getConnection()
flowFile = session.get()
if(!flowFile) return

try {
flowFile = session.create()
PreparedStatement prepareStatement = conn.prepareStatement("exec dbo.terms 1,'test','test'");
i = prepareStatement.executeUpdate();
  conn?.close()
  }
 catch(e) {
    log.error('Scripting error', e)
}
session.transfer(flowFile, REL_SUCCESS)
session.commit();

Below is the exception I'm getting evethough I have mentioned the relationship. And because of these exception even one flow file is generated by generate flow file and sent to executeSQL processor it is executing the same procedure many times as yield time is 1 sec. So, for one flow file same record is inserted many time.

So, how to avoid this.

Bryan Bende Bryan Bende · Accepted Answer · 2019-08-05T13:23:17

You shouldn't be calling flowFile = session.create() because you already got a flow file from session.get(), so you are basically overwriting the original reference and thus losing track of the original flow file which was never transferred anywhere.

In nifi groovy script how to execute stored procedure

1 Answers