Support Questions
Find answers, ask questions, and share your expertise

Nifi Execute Groovy Script

Nifi Execute Groovy Script

Explorer

Hi All, 

I am facing issues with Execute Script processor. I am executing Groovy script which have oracle  DB in Execute Script processor but its not closing the DB connection.

Issues: Database connection are not closing and its making Inactive connection in database. 

The Groovy script we added proper open and close connections. Could you help fix this issues, also let me know where I am not correct . 

 

DB Connection Configuration is like that :

Capture.JPG

Execute Script Process are configured as fallowing: 

ExecuteScriptProcessor.JPG

Groovy Script as following : 

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 oracle.jdbc.OracleTypes
import java.sql.ResultSet


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

def conn = lookup.getControllerService(dbcpServiceId)?.getConnection();
def sql = new Sql(conn);

def flowFile = session.get()
if(!flowFile) return
//Session Values
strApplication = flowFile.getAttribute('application')
strUsername = flowFile.getAttribute('username')
strPassword = flowFile.getAttribute('password')


// SQL Query Creation and execution

def ValidOrNot

String sqlString = "select FN_HP_IS_VALID_APP('${strApplication}','${strUsername}','${strPassword}') as ValidOrNot from dual";
try {
rowNum = 0
sql.eachRow(sqlString, { dataResult ->
//println(dataResult.ValidOrNot)
ValidOrNot = dataResult.ValidOrNot
})

}
catch(Exception ex){

conn?.close()
}
finally {

conn?.close()
}


conn?.close()
// Assign values to session object
def attrMap = ['VALIDORNOT':ValidOrNot,'sqlString':sqlString]
flowFile = session.putAllAttributes(flowFile, attrMap)
session.transfer(flowFile, REL_SUCCESS)

 

3 REPLIES 3

Re: Nifi Execute Groovy Script

Explorer

 

@mburgess  Hope you doing well, could you help on my issues. My script running fine but its process keeping oracle connection inactive and not closing, when i am checking in database its showing inactive  session which NIFI created by the NIFI user. 

 

 

 

 

Re: Nifi Execute Groovy Script

New Contributor

++

We are facing same problem.

Are there anyone who can fix this?

Re: Nifi Execute Groovy Script

New Contributor

I am getting the same issues.could you help
Please