[
https://issues.apache.org/jira/browse/NIFI-5819?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17641524#comment-17641524
]
ASF subversion and git services commented on NIFI-5819:
-------------------------------------------------------
Commit db2bf5f64530e66763894efcfa702d2b4bae1e39 in nifi's branch
refs/heads/support/nifi-1.19 from Matt Burgess
[ https://gitbox.apache.org/repos/asf?p=nifi.git;h=db2bf5f645 ]
NIFI-5819: Support SQLServer sql_variant type
This closes #6699.
Signed-off-by: Peter Turcsanyi <turcsa...@apache.org>
> Support SQLServer sql_variant type
> ----------------------------------
>
> Key: NIFI-5819
> URL: https://issues.apache.org/jira/browse/NIFI-5819
> Project: Apache NiFi
> Issue Type: Bug
> Components: Core Framework
> Affects Versions: 1.8.0
> Reporter: Charlie Meyer
> Assignee: Matt Burgess
> Priority: Major
> Fix For: 1.20.0, 1.19.1
>
> Time Spent: 20m
> Remaining Estimate: 0h
>
> If i run a query using executesqlrecord against a mssql database such as
> {{SELECT SERVERPROPERTY ('ProductVersion') AS MajorVersion}} nifi logs the
> following stack trace:
> {code:java}
> { "cause": { "cause": null, "stackTrace": [ { "methodName": "createSchema",
> "fileName": "JdbcCommon.java", "lineNumber": 677, "className":
> "org.apache.nifi.processors.standard.util.JdbcCommon", "nativeMethod": false
> }, { "methodName": "writeResultSet", "fileName": "RecordSqlWriter.java",
> "lineNumber": 68, "className":
> "org.apache.nifi.processors.standard.sql.RecordSqlWriter", "nativeMethod":
> false }, { "methodName": "lambda$onTrigger$1", "fileName":
> "ExecuteSqlRecordWithoutSwallowingErrors.java", "lineNumber": 362,
> "className":
> "com.civitaslearning.collect.nifi.processor.ExecuteSqlRecordWithoutSwallowingErrors",
> "nativeMethod": false }, { "methodName": "write", "fileName":
> "StandardProcessSession.java", "lineNumber": 2648, "className":
> "org.apache.nifi.controller.repository.StandardProcessSession",
> "nativeMethod": false }, { "methodName": "onTrigger", "fileName":
> "ExecuteSqlRecordWithoutSwallowingErrors.java", "lineNumber": 360,
> "className":
> "com.civitaslearning.collect.nifi.processor.ExecuteSqlRecordWithoutSwallowingErrors",
> "nativeMethod": false }, { "methodName": "onTrigger", "fileName":
> "AbstractProcessor.java", "lineNumber": 27, "className":
> "org.apache.nifi.processor.AbstractProcessor", "nativeMethod": false }, {
> "methodName": "onTrigger", "fileName": "StandardProcessorNode.java",
> "lineNumber": 1165, "className":
> "org.apache.nifi.controller.StandardProcessorNode", "nativeMethod": false },
> { "methodName": "invoke", "fileName": "ConnectableTask.java", "lineNumber":
> 203, "className": "org.apache.nifi.controller.tasks.ConnectableTask",
> "nativeMethod": false }, { "methodName": "run", "fileName":
> "TimerDrivenSchedulingAgent.java", "lineNumber": 117, "className":
> "org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1",
> "nativeMethod": false }, { "methodName": "call", "fileName":
> "Executors.java", "lineNumber": 511, "className":
> "java.util.concurrent.Executors$RunnableAdapter", "nativeMethod": false }, {
> "methodName": "runAndReset", "fileName": "FutureTask.java", "lineNumber":
> 308, "className": "java.util.concurrent.FutureTask", "nativeMethod": false },
> { "methodName": "access$301", "fileName": "ScheduledThreadPoolExecutor.java",
> "lineNumber": 180, "className":
> "java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask",
> "nativeMethod": false }, { "methodName": "run", "fileName":
> "ScheduledThreadPoolExecutor.java", "lineNumber": 294, "className":
> "java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask",
> "nativeMethod": false }, { "methodName": "runWorker", "fileName":
> "ThreadPoolExecutor.java", "lineNumber": 1149, "className":
> "java.util.concurrent.ThreadPoolExecutor", "nativeMethod": false }, {
> "methodName": "run", "fileName": "ThreadPoolExecutor.java", "lineNumber":
> 624, "className": "java.util.concurrent.ThreadPoolExecutor$Worker",
> "nativeMethod": false }, { "methodName": "run", "fileName": "Thread.java",
> "lineNumber": 748, "className": "java.lang.Thread", "nativeMethod": false }
> ], "message": "createSchema: Unknown SQL type -156 / sql_variant (table:
> NiFi_ExecuteSQL_Record, column: MajorVersion) cannot be converted to Avro
> type", "localizedMessage": "createSchema: Unknown SQL type -156 / sql_variant
> (table: NiFi_ExecuteSQL_Record, column: MajorVersion) cannot be converted to
> Avro type", "suppressed": [] }, "stackTrace": [ { "methodName":
> "lambda$onTrigger$1", "fileName":
> "ExecuteSqlRecordWithoutSwallowingErrors.java", "lineNumber": 364,
> "className":
> "com.civitaslearning.collect.nifi.processor.ExecuteSqlRecordWithoutSwallowingErrors",
> "nativeMethod": false }, { "methodName": "write", "fileName":
> "StandardProcessSession.java", "lineNumber": 2648, "className":
> "org.apache.nifi.controller.repository.StandardProcessSession",
> "nativeMethod": false }, { "methodName": "onTrigger", "fileName":
> "ExecuteSqlRecordWithoutSwallowingErrors.java", "lineNumber": 360,
> "className":
> "com.civitaslearning.collect.nifi.processor.ExecuteSqlRecordWithoutSwallowingErrors",
> "nativeMethod": false }, { "methodName": "onTrigger", "fileName":
> "AbstractProcessor.java", "lineNumber": 27, "className":
> "org.apache.nifi.processor.AbstractProcessor", "nativeMethod": false }, {
> "methodName": "onTrigger", "fileName": "StandardProcessorNode.java",
> "lineNumber": 1165, "className":
> "org.apache.nifi.controller.StandardProcessorNode", "nativeMethod": false },
> { "methodName": "invoke", "fileName": "ConnectableTask.java", "lineNumber":
> 203, "className": "org.apache.nifi.controller.tasks.ConnectableTask",
> "nativeMethod": false }, { "methodName": "run", "fileName":
> "TimerDrivenSchedulingAgent.java", "lineNumber": 117, "className":
> "org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1",
> "nativeMethod": false }, { "methodName": "call", "fileName":
> "Executors.java", "lineNumber": 511, "className":
> "java.util.concurrent.Executors$RunnableAdapter", "nativeMethod": false }, {
> "methodName": "runAndReset", "fileName": "FutureTask.java", "lineNumber":
> 308, "className": "java.util.concurrent.FutureTask", "nativeMethod": false },
> { "methodName": "access$301", "fileName": "ScheduledThreadPoolExecutor.java",
> "lineNumber": 180, "className":
> "java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask",
> "nativeMethod": false }, { "methodName": "run", "fileName":
> "ScheduledThreadPoolExecutor.java", "lineNumber": 294, "className":
> "java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask",
> "nativeMethod": false }, { "methodName": "runWorker", "fileName":
> "ThreadPoolExecutor.java", "lineNumber": 1149, "className":
> "java.util.concurrent.ThreadPoolExecutor", "nativeMethod": false }, {
> "methodName": "run", "fileName": "ThreadPoolExecutor.java", "lineNumber":
> 624, "className": "java.util.concurrent.ThreadPoolExecutor$Worker",
> "nativeMethod": false }, { "methodName": "run", "fileName": "Thread.java",
> "lineNumber": 748, "className": "java.lang.Thread", "nativeMethod": false }
> ], "message": "java.lang.IllegalArgumentException: createSchema: Unknown SQL
> type -156 / sql_variant (table: NiFi_ExecuteSQL_Record, column: MajorVersion)
> cannot be converted to Avro type", "localizedMessage":
> "java.lang.IllegalArgumentException: createSchema: Unknown SQL type -156 /
> sql_variant (table: NiFi_ExecuteSQL_Record, column: MajorVersion) cannot be
> converted to Avro type", "suppressed": [] }
> {code}
> The workaround is to modify the query to be like:
> {{SELECT CONVERT(VARCHAR(128), SERVERPROPERTY ('ProductVersion')) AS
> MajorVersion}}
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
