[jira] [Comment Edited] (NIFI-5819) JDBCCommon does not handle converting SQLServer sql_variant type to avro

["Matt Burgess (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/NIFI-5819?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17636906#comment-17636906 ] 

Matt Burgess edited comment on NIFI-5819 at 11/21/22 10:26 PM:
---------------------------------------------------------------

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.


was (Author: mattyb149):
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)