Store excel files in postgresql via NiFi groovy script

Brenigan — Thu, 11 May 2023 11:48:36 GMT

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!

Re: Store excel files in postgresql via NiFi groovy script

Brenigan — Thu, 11 May 2023 11:46:46 GMT

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()

question Store excel files in postgresql via NiFi groovy script in Support Questions

Store excel files in postgresql via NiFi groovy script

Re: Store excel files in postgresql via NiFi groovy script