Support Questions

Find answers, ask questions, and share your expertise

DBCPConnectionPool controller service for SQL Server, jdbc exception

Expert Contributor

NiFi 1.1.1 tested on both Windows 7 and RHEL 7.

The background thread can be found here.

I have created a DBCPConnectionPool controller service pointing to a SQL Server db, I am able to fetch data from a table and write it to the local disk(ExecuteSQL -> ConvertAvroToJSON -> PutFile).


The challenge arises when I use the pool as a property in my custom processor. In the processor's code, I need to invoke a function in the db but this lands into a SQLException pointing to the JDBC driver. Note that the same driver functions properly in a standalone Java code(provided in the background thread to avoid cluttering this post) and I get the return value from the function.

Process or SQL exception in <configure logger template to pick the code location>
2017-03-17 09:25:30,717 ERROR [Timer-Driven Process Thread-6] c.s.d.processors.SQLServerCDCProcessor 
org.apache.nifi.processor.exception.ProcessException: Coudln't retrieve the max lsn for the db test
at com.datalake.processors.SQLServerCDCProcessor$SQLServerCDCUtils.getMaxLSN( ~[nifi-NiFiCDCPoC-processors-1.0-SNAPSHOT.jar:1.0-SNAPSHOT]
at com.datalake.processors.SQLServerCDCProcessor.getChangedTableQueries( ~[nifi-NiFiCDCPoC-processors-1.0-SNAPSHOT.jar:1.0-SNAPSHOT]
at com.datalake.processors.SQLServerCDCProcessor.onTrigger( ~[nifi-NiFiCDCPoC-processors-1.0-SNAPSHOT.jar:1.0-SNAPSHOT]
at org.apache.nifi.processor.AbstractProcessor.onTrigger( [nifi-api-1.1.1.jar:1.1.1]
at org.apache.nifi.controller.StandardProcessorNode.onTrigger( [nifi-framework-core-1.1.1.jar:1.1.1]
at [nifi-framework-core-1.1.1.jar:1.1.1]
at [nifi-framework-core-1.1.1.jar:1.1.1]
at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$ [nifi-framework-core-1.1.1.jar:1.1.1]
at java.util.concurrent.Executors$ [na:1.8.0_71]
at java.util.concurrent.FutureTask.runAndReset( [na:1.8.0_71]
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301( [na:1.8.0_71]
at java.util.concurrent.ScheduledThreadPoolExecutor$ [na:1.8.0_71]
at java.util.concurrent.ThreadPoolExecutor.runWorker( [na:1.8.0_71]
at java.util.concurrent.ThreadPoolExecutor$ [na:1.8.0_71]
at [na:1.8.0_71]
Caused by: java.sql.SQLFeatureNotSupportedException: registerOutParameter not implemented
at java.sql.CallableStatement.registerOutParameter( ~[na:1.8.0_71]
at com.datalake.processors.SQLServerCDCProcessor$SQLServerCDCUtils.getMaxLSN( ~[nifi-NiFiCDCPoC-processors-1.0-SNAPSHOT.jar:1.0-SNAPSHOT]
... 14 common frames omitted

I suspect that the Controller Service is not configured properly - it can execute select queries but when a code invokes a function, it throws an Exception. What am I missing ?


Super Guru

The DBCPConnectionPool uses Apache DBCP to pool connections, statements, etc. Your standalone Java code does not use DBCP, so I suspect there is a bug in DBCP or some additional configuration needed to support CallableStatements.

Expert Contributor

I have circumvented the issue by using a select instead of a CallableStatement 😞

New Contributor

could you please share how did you do that

Expert Contributor

Did what - the configuration or ... ? The original question has link to another question that has the config details.

Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.