Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

JDBC failing in custom processor

Highlighted

JDBC failing in custom processor

Expert Contributor

NiFi 1.1.1

Microsoft seems to have released a jdbc driver recently. I am using the sqljdbc42.jar(that is downloaded using that link) in standalone code as well as added the same to the NiFi lib.

In a stand-alone Java class, the following code(reading the return value of a SQL Server built-in function) works fine :

public byte[] getMaxLSN(Connection connection, String containerDB) {
String dbMaxLSN = "{? = CALL sys.fn_cdc_get_max_lsn()}";
byte[] maxLSN = null;
try (final CallableStatement cstmt = connection.prepareCall(dbMaxLSN);) {
cstmt.registerOutParameter(1, java.sql.JDBCType.BINARY);
cstmt.execute();
if (cstmt.getBytes(1) == null || cstmt.getBytes(1).length <= 0) {
System.out.println("Coudln't retrieve the max lsn for the db "
+ containerDB);
} else {
maxLSN = cstmt.getBytes(1);
}
} catch (SQLException sqlException) {
System.out.println("sqlException !!!");
sqlException.printStackTrace();
}
return maxLSN;
}

This previous thread has the processor information.

I added the same code to my custom processor but when I start the processor, it fails quoting the feature is not supported by the driver :

*****Edit-1*****

Some questions about the ways to include the JDBC drivers :

  1. I want to avoid adding the jdbc jar in the NiFi lib and add/bundle it in the nar file itself. Is this possible and is this the standard/recommended way ?
  2. By looking at the code of the standard processors like ExecuteSQL, I couldn't establish how these processors use the JDBC drivers, any pointers ?
  3. Is storing the absolute path of the JDBC driver in the DBCPConnectionPool controller service a standard way ?
2017-03-13 14:09:39,705 ERROR [Timer-Driven Process Thread-8] c.s.d.processors.SQLServerCDCProcessor SQLServerCDCProcessor[id=c7c8f0a8-015a-1000-71e1-09ab42e46c55] Coudln't retrieve the max lsn for the db test
2017-03-13 14:09:39,706 ERROR [Timer-Driven Process Thread-8] c.s.d.processors.SQLServerCDCProcessor 
java.sql.SQLFeatureNotSupportedException: registerOutParameter not implemented
at java.sql.CallableStatement.registerOutParameter(CallableStatement.java:2613) ~[na:1.8.0_71]
at com.datalake.processors.SQLServerCDCProcessor$SQLServerCDCUtils.getMaxLSN(SQLServerCDCProcessor.java:677) ~[nifi-NiFiCDCPoC-processors-1.0-SNAPSHOT.jar:1.0-SNAPSHOT]
at com.datalake.processors.SQLServerCDCProcessor.getChangedTableQueries(SQLServerCDCProcessor.java:602) [nifi-NiFiCDCPoC-processors-1.0-SNAPSHOT.jar:1.0-SNAPSHOT]


at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27) [nifi-api-1.1.1.jar:1.1.1]
at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1099) [nifi-framework-core-1.1.1.jar:1.1.1]
at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:136) [nifi-framework-core-1.1.1.jar:1.1.1]
at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:47) [nifi-framework-core-1.1.1.jar:1.1.1]
at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:132) [nifi-framework-core-1.1.1.jar:1.1.1]
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [na:1.8.0_71]
at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) [na:1.8.0_71]
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) [na:1.8.0_71]
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) [na:1.8.0_71]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_71]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_71]
at java.lang.Thread.run(Thread.java:745) [na:1.8.0_71]
2017-03-13 14:09:39,714 ERROR [Timer-Driven Process Thread-8] c.s.d.processors.SQLServerCDCProcessor SQLServerCDCProcessor[id=c7c8f0a8-015a-1000-71e1-09ab42e46c55] Process or SQL exception in <configure logger template to pick the code location>
2017-03-13 14:09:39,715 ERROR [Timer-Driven Process Thread-8] 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(SQLServerCDCProcessor.java:692) ~[nifi-NiFiCDCPoC-processors-1.0-SNAPSHOT.jar:1.0-SNAPSHOT]
at com.datalake.processors.SQLServerCDCProcessor.getChangedTableQueries(SQLServerCDCProcessor.java:602) ~[nifi-NiFiCDCPoC-processors-1.0-SNAPSHOT.jar:1.0-SNAPSHOT]
at com.datalake.processors.SQLServerCDCProcessor.onTrigger(SQLServerCDCProcessor.java:249) ~[nifi-NiFiCDCPoC-processors-1.0-SNAPSHOT.jar:1.0-SNAPSHOT]
at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27) [nifi-api-1.1.1.jar:1.1.1]
at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1099) [nifi-framework-core-1.1.1.jar:1.1.1]
at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:136) [nifi-framework-core-1.1.1.jar:1.1.1]
at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:47) [nifi-framework-core-1.1.1.jar:1.1.1]
at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:132) [nifi-framework-core-1.1.1.jar:1.1.1]
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [na:1.8.0_71]
at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) [na:1.8.0_71]
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) [na:1.8.0_71]
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) [na:1.8.0_71]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_71]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_71]
at java.lang.Thread.run(Thread.java:745) [na:1.8.0_71]
Caused by: java.sql.SQLFeatureNotSupportedException: registerOutParameter not implemented
at java.sql.CallableStatement.registerOutParameter(CallableStatement.java:2613) ~[na:1.8.0_71]
at com.datalake.processors.SQLServerCDCProcessor$SQLServerCDCUtils.getMaxLSN(SQLServerCDCProcessor.java:677) ~[nifi-NiFiCDCPoC-processors-1.0-SNAPSHOT.jar:1.0-SNAPSHOT]
2 REPLIES 2

Re: JDBC failing in custom processor

Super Guru

I couldn't reproduce this; using that driver I can call registerOutParameter() with no error. Are you sure the custom processor is using the correct driver? Are you using a DBCPConnectionPool to get the Connection to the SQL Server database? In general, you should not add the driver to NiFi's lib/ folder: the DBCPConnectionPool properties allow you to point at a separate path where your driver JAR is location.

Re: JDBC failing in custom processor

Expert Contributor

Yeah I am using a DBCPConnectionPool to get the Connection to the SQL Server database. I tested a sample flow using it and could retrieve data from a table and write it to a local file(PutFile processor).

Can you check :

  1. The link to the previous post mentioned in the original question - it has screenshots. I have created a DBCPConnectionPool controller service to a SQL Server db and trying to use it as a property in my custom processor
  2. 'Edit-1' in the original question
Don't have an account?
Coming from Hortonworks? Activate your account here