[
https://issues.apache.org/jira/browse/NIFI-5819?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17636906#comment-17636906
]
Matt Burgess commented on NIFI-5819:
------------------------------------
I'm not sure we should try to convert all unknown SQL types to String; however
we do have handling already in place for custom SQL types from Oracle
(timestamps for example). This is also a problem with PutDatabaseRecord but not
in the same section of code, and not for the same exact reason. One thing that
is highly unfortunate is that the driver has a different mapping of SQL types
than the server, such as -150 instead of -156 for sql_variant and 93 instead of
-150 for SMALLDATETIME. Since there is precedence for adding custom SQL types
ad-hoc, we can look at adding handling of this type as well. However for
sql_variant we may want to try to do something smarter than assuming a String
since any value can go in, perhaps we fall back to use the RecordFieldType of
the NiFi record field instead of the SQL type when setting parameters, or
something like that.-
> JDBCCommon does not handle converting SQLServer sql_variant type to avro
> ------------------------------------------------------------------------
>
> 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
> Priority: Major
>
> 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)
