Hi All,
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?
