SQL Server的DBCPConnectionPool控制器服务，jdbc异常 [英] DBCPConnectionPool controller service for SQL Server, jdbc exception
问题描述
NiFi 1.1.1已在Windows 7和RHEL 7上进行了测试.
NiFi 1.1.1 tested on both Windows 7 and RHEL 7.
后台线程位于此处.
我已经创建了一个指向SQL Server数据库的DBCPConnectionPool控制器服务，我能够从表中获取数据并将其写入本地磁盘(ExecuteSQL-> ConvertAvroToJSON-> PutFile).
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).
我的代码:
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;
}
当我将池用作自定义处理器中的属性时，就会出现挑战.在处理器的代码中，我需要在db中调用一个函数，但这会放入指向JDBC驱动程序的 SQLException中.请注意，相同的驱动程序可以在独立的Java代码中正常运行(在后台线程中提供，以避免使本文混乱)，并且我从该函数获取了返回值.我怀疑Controller Service的配置不正确-它可以执行选择查询，但是当代码调用函数时，它将引发Exception.我想念什么?
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. 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 ?
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(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]
... 14 common frames omitted
推荐答案
"java.sql.SQLFeatureNotSupportedException:registerOutParameter未实现" 您的代码正在使用驱动程序中不可用的功能.
"java.sql.SQLFeatureNotSupportedException: registerOutParameter not implemented" Your code is using a feature not available in the driver.
具体地说，基于堆栈跟踪，驱动程序正在调用registerOutParameter(int parameterIndex, SQLType sqlType)，这是JDBC 4.2(Java 8)中引入的.此方法在java.sql.CallableStatement界面中具有以下默认实现:
Specifically, based on the stacktrace, your driver is calling registerOutParameter(int parameterIndex, SQLType sqlType), which was introduced in JDBC 4.2 (Java 8). This method has the following default implementation in the java.sql.CallableStatement interface:
default void registerOutParameter(int parameterIndex, SQLType sqlType)
throws SQLException {
throw new SQLFeatureNotSupportedException("registerOutParameter not implemented");
}
throw在java.sql.CallableStatement中的第2613行，与堆栈跟踪相匹配.
Where the throw is line 2613 in java.sql.CallableStatement, which matches the stacktrace.
As this method is implemented in the latest version of the Microsoft SQL Server JDBC driver, you are either using an older version, or the objects of the driver are wrapped in a proxy that doesn't support JDBC 4.2.
我建议您升级到当前版本为v6的最新版本的最新版本. 1.0.由于您的另一个问题建议您使用maven，那么您应该确保驱动程序的 new Maven坐标(当他们将驱动程序开源时，它们已更改):
I suggest you upgrade to the latest version of the driver, currently v6.1.0. As another question of yours suggests you use maven, then you should should ensure the new Maven coordinates for the driver (they changed it when they open-sourced the driver):
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
<version>6.1.0.jre8</version>
</dependency>
如果问题仍然存在，那么com.datalake.processors.SQLServerCDCProcessor是您自己的代码(如您的 )，您应该将其更改为不调用registerOutParameter(int parameterIndex, SQLType sqlType)，而是调用较旧的registerOutParameter(int parameterIndex, int sqlType).
If the problem persist, then as com.datalake.processors.SQLServerCDCProcessor is your own code (as shown in your other question) , you should just change it to not call registerOutParameter(int parameterIndex, SQLType sqlType), but the older registerOutParameter(int parameterIndex, int sqlType).
这篇关于SQL Server的DBCPConnectionPool控制器服务，jdbc异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
