SQL Server 的 DBCPConnectionPool 控制器服务，jdbc 异常 [英] DBCPConnectionPool controller service for SQL Server, jdbc exception

查看：23
本文介绍了SQL Server 的 DBCPConnectionPool 控制器服务，jdbc 异常的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

NiFi 1.1.1 在 Windows 7 和 RHEL 7 上均经过测试.

后台线程在

当我将池用作自定义处理器中的属性时，挑战就出现了.在处理器的代码中，我需要调用数据库中的一个函数，但这会导致 SQLException 指向 JDBC 驱动程序.请注意，相同的驱动程序在独立的 Java 代码中正常运行(在后台线程中提供以避免本文混乱)，并且我从函数中获取返回值.我怀疑控制器服务没有正确配置 - 它可以执行选择查询，但是当代码调用一个函数时，它会抛出一个异常.我错过了什么?

中的进程或SQL异常2017-03-17 09:25:30,717 错误 [定时器驱动的进程线程-6] c.s.d.processors.SQLServerCDCProcessororg.apache.nifi.processor.exception.ProcessException:无法检索数据库测试的最大 lsn在 com.datalake.processors.SQLServerCDCProcessor$SQLServerCDCUtils.getMaxLSN(SQLServerCDCProcessor.java:692) ~[nifi-NiFiCDCPoC-processors-1.0-SNAPSHOT.jar:1.0-SNAPSHOT]在 com.datalake.processors.SQLServerCDCProcessor.getChangedTableQueries(SQLServerCDCProcessor.java:602) ~[nifi-NiFiCDCPoC-processors-1.0-SNAPSHOT.jar:1.0-SNAPSHOT]在 com.datalake.processors.SQLServerCDCProcessor.onTrigger(SQLServerCDCProcessor.java:249) ~[nifi-NiFiCDCPoC-processors-1.0-SNAPSHOT.jar:1.0-SNAPSHOT]在 org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27) [nifi-api-1.1.1.jar:1.1.1]在 org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1099) [nifi-framework-core-1.1.1.jar:1.1.1]在 org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:136) [nifi-framework-core-1.1.1.jar:1.1.1]在 org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:47) [nifi-framework-core-1.1.1.jar:1.1.1]在 org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:132) [nifi-framework-core-1.1.1.jar:1.1.1]在 java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [na:1.8.0_71]在 java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) [na:1.8.0_71]在 java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) [na:1.8.0_71]在 java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) [na:1.8.0_71]在 java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_71]在 java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_71]在 java.lang.Thread.run(Thread.java:745) [na:1.8.0_71]引起:java.sql.SQLFeatureNotSupportedException:registerOutParameter 未实现在 java.sql.CallableStatement.registerOutParameter(CallableStatement.java:2613) ~[na:1.8.0_71]在 com.datalake.processors.SQLServerCDCProcessor$SQLServerCDCUtils.getMaxLSN(SQLServerCDCProcessor.java:677) ~[nifi-NiFiCDCPoC-processors-1.0-SNAPSHOT.jar:1.0-SNAPSHOT]...省略了 14 个常用帧

解决方案

"java.sql.SQLFeatureNotSupportedException: registerOutParameter not implementation" 您的代码正在使用驱动程序中不可用的功能.

具体来说，根据堆栈跟踪，您的驱动程序正在调用 registerOutParameter(int parameterIndex, SQLType sqlType)，这是在 JDBC 4.2 (Java 8) 中引入的.此方法在 java.sql.CallableStatement 接口中具有以下默认实现:

default void registerOutParameter(int parameterIndex, SQLType sqlType)抛出 SQLException {throw new SQLFeatureNotSupportedException("registerOutParameter 未实现");}

其中 throw 是 java.sql.CallableStatement 中的第 2613 行，与堆栈跟踪匹配.

作为 此方法在最新版本的 Microsoft SQL Server JDBC 驱动程序中实现，您要么使用旧版本，要么驱动程序的对象包装在代理中不支持 JDBC 4.2.

我建议您升级到最新版本的驱动程序，目前为 v6.1.0.正如你的另一个问题建议你使用maven，那么您应该确保驱动程序的 新 Maven 坐标(他们在开源驱动程序时更改了它):

<依赖><groupId>com.microsoft.sqlserver</groupId><artifactId>mssql-jdbc</artifactId><version>6.1.0.jre8</version></依赖>

如果问题仍然存在，那么 com.datalake.processors.SQLServerCDCProcessor 是您自己的代码(如您的 其他问题) ，您应该将其更改为不调用 registerOutParameter(int parameterIndex, SQLType sqlType)，而是调用旧的 registerOutParameter(int parameterIndex, int sqlType).

NiFi 1.1.1 tested on both Windows 7 and RHEL 7.

The background thread is 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).

My code:

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;
}

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 not implemented" Your code is using a feature not available in the driver.

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");
}

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.

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>

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屋！

查看全文
相关文章
Java开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆