Skip to main content
Brenigan
Gold ⭐️
May 11, 2023
Solved

Store excel files in postgresql via NiFi groovy script

Hello!
So I am having a problem with the script I wrote. 
I am trying to save  content of a flowfiles I am getting, which are excel files.
But I am getting an error

(had to delete image due to corporate safety)



Here's my script:

 

 

 

import java.nio.charset.StandardCharsets
import org.apache.nifi.controller.ControllerService
import groovy.sql.Sql

def ff = session.get()
if(!ff)return


def lookup = context.controllerServiceLookup
//def dbServiceName = databaseConnectionPoolName.value


def dbcpServiceId = lookup.getControllerServiceIdentifiers(ControllerService).find
{ cs -> lookup.getControllerServiceName(cs) == 'DBCPConnectionPool_GP_prj_gistek_preprod' }

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

session.read(ff, {inputStream ->
def statement = "INSERT INTO publish.templates (excel_template) VALUES (?)"
def params = [inputStream]
sql.executeInsert(statement, params)} as InputStreamCallback)

conn?.close()


session.transfer(ff, REL_SUCCESS)
session.commit()

 

 

 


I tested connection with data base by trying to insert some test values and it worked fine.
Apparently there's some problems with content of a flow file, or should I decode/encode it fist?

Any help would be appriciated, thank you!



Brenigan
BreniganAnswer
Gold ⭐️
May 11, 2023

Ok i fixed it myself actually 🙂 

In case someone is interested here's my solution:


import java.nio.charset.StandardCharsets
import org.apache.nifi.controller.ControllerService
import groovy.sql.Sql

def ff = session.get()
if(!ff)return

def lookup = context.controllerServiceLookup
//def dbServiceName = databaseConnectionPoolName.value

def dbcpServiceId = lookup.getControllerServiceIdentifiers(ControllerService).find
{ cs -> lookup.getControllerServiceName(cs) == 'DBCPConnectionPool_GP_prj_gistek_preprod' }

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

session.read(ff, {inputStream ->
//def statement = "INSERT INTO publish.templates (excel_template) VALUES (?)"
sql.executeInsert "INSERT INTO publish.templates (excel_template) VALUES (${inputStream.getBytes()})"
} as InputStreamCallback)

conn?.close()
session.transfer(ff, REL_SUCCESS)
session.commit()

1 reply

Brenigan
BreniganAuthorAnswer
Gold ⭐️
May 11, 2023

Ok i fixed it myself actually 🙂 

In case someone is interested here's my solution:


import java.nio.charset.StandardCharsets
import org.apache.nifi.controller.ControllerService
import groovy.sql.Sql

def ff = session.get()
if(!ff)return

def lookup = context.controllerServiceLookup
//def dbServiceName = databaseConnectionPoolName.value

def dbcpServiceId = lookup.getControllerServiceIdentifiers(ControllerService).find
{ cs -> lookup.getControllerServiceName(cs) == 'DBCPConnectionPool_GP_prj_gistek_preprod' }

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

session.read(ff, {inputStream ->
//def statement = "INSERT INTO publish.templates (excel_template) VALUES (?)"
sql.executeInsert "INSERT INTO publish.templates (excel_template) VALUES (${inputStream.getBytes()})"
} as InputStreamCallback)

conn?.close()
session.transfer(ff, REL_SUCCESS)
session.commit()
Translate