Flowfiles are stuck in queue while calling store procedure using Execute Script

userbOXi1 0
Viewed 37

enter image description here

I am trying to call store procedure using groovy script and the processor I am using is Execute Script (using groovy because i want to capture the response of store procedure). But the flow files are getting stuck and when I am restarting the processor it's getting passed The same code I am using on other environment it's working fine without an issue.

Below is code I am using to call the store procedure:

import org.apache.commons.io.IOUtils
import org.apache.nifi.controller.ControllerService
import org.apache.nifi.processor.io.StreamCallback
import java.nio.charset.*
import groovy.sql.OutParameter
import groovy.sql.Sql
import java.sql.ResultSet
import java.sql.Clob

try{
def lookup = context.controllerServiceLookup
def dbServiceName = ConncationPool.value
def dbcpServiceId = lookup.getControllerServiceIdentifiers(ControllerService).find {
cs -> lookup.getControllerServiceName(cs) == dbServiceName
}

def conn = lookup.getControllerService(dbcpServiceId).getConnection();
sql = Sql.newInstance(conn);
def flowFile = session.get()
if(!flowFile) return
attr1= flowFile.getAttribute('attr1')
attr2= flowFile.getAttribute('attr2')
attr3= flowFile.getAttribute('attr3')
def data = []

String sqlString ="""{call procedure_name(?,?,?,?)}""";

def OUT_JSON

def parametersList = [attr1,attr2,attr3,Sql.VARCHAR];


sql.call(sqlString, parametersList) {out_json_response ->
OUT_JSON = out_json_response
};

def attrMap = ['out_json_response':String.valueOf(OUT_JSON),'Conn':String.valueOf(conn)]
flowFile = session.putAllAttributes(flowFile, attrMap)
conn.close()
sql.close();
session.transfer(flowFile, REL_SUCCESS)
}
catch (e){
if (conn != null) conn.close();
if (sql != null) sql.close();
log.error('Scripting error', e)
flowFile = session.putAttribute(flowFile, "error", e.getMessage())
session.transfer(flowFile, REL_FAILURE)
} finally {
if (conn != null) conn.close();
if (sql != null) sql.close();
}

Can you please help me to solve the issue. Is anyone face the same issue?

apache-nifi groovy
1 Answers

I cannot see the run schedule in your screenshot. So first go to the configuration by right clicking on the processor.

enter image description here

There you'll find a tab named Scheduling. Click on it.

enter image description here

Now, check if the Scheduling Strategy is marked as CRON driven or Timer driven. There you can also check the Run Schedule.

When you mentioned that your script runs after restarting or every 15 minutes, I thought that your Run Schedule is set to run every ~15 minutes.

Just verify that and if that is the case, stop the processor and change the below configuration to:

Scheduling Strategy: Timer Driven

Run Schedule: 0 sec

enter image description here

NOTE: Before making any changes, if the initial flow was not developed by you, check if changing the Scheduling of the processor will not have any undesired effect. Or just make sure why it was set to run every 15 minutes.

Vectors0
Related