Support Questions

Find answers, ask questions, and share your expertise

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

Explorer

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?

 

Techie123_0-1663816257746.png

 

0 REPLIES 0