[teiid-issues] [JBoss JIRA] (TEIID-5396) Querying SQL Server variant type for a string fails

[Steven Hawkins (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-5396?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins resolved TEIID-5396.
-----------------------------------
    Fix Version/s: 10.3.2
                   10.2.3
       Resolution: Done


To preserve the old behavior a change was needed to the ConvertModifier.  However this also means that putting unicode strings in the column will return just ascii data.  A workaround is to change the native type from sql_variant to nvarchar.  Otherwise a code fix would be needed to add sql_variant as type that is possibly unicode.

> Querying SQL Server variant type for a string fails
> ---------------------------------------------------
>
>                 Key: TEIID-5396
>                 URL: https://issues.jboss.org/browse/TEIID-5396
>             Project: Teiid
>          Issue Type: Bug
>          Components: JDBC Connector
>    Affects Versions: 8.12.14.6_4
>            Reporter: Andrej Šmigala
>            Assignee: Steven Hawkins
>             Fix For: 11.0, 10.3.2, 10.2.3
>
>
> Table on SQL Server source created as 
> {code:sql}CREATE TABLE table_with_variant(id INT, var SQL_VARIANT){code}
> and loaded with int, float and string data.
> Running the following query against a dynamic VDB with metadata import
> {code:sql}SELECT cast(var AS string) FROM table_with_variant WHERE id = 1{code}
> fails with 
> {noformat}java.sql.SQLException: Remote com.microsoft.sqlserver.jdbc.SQLServerException: The "variant" data type is not supported.{noformat}
> This query used to work with DV 6.4.2, it appears it is a further regression from TEIID-5313 . Querying rows with non-string data works as expected.
> SOURCE SRC command with 6.4.2 (working):
> {code:sql}SELECT cast(g_0."var" AS varchar(4000)) FROM "dballo05"."dbo"."table_with_variant" g_0 WHERE g_0."id" = 1{code}
> SOURCE SRC command with 6.4.3 (not working), note missing cast to varchar:
> {code:sql}SELECT g_0."var" FROM "dballo06"."dbo"."table_with_variant" g_0 WHERE g_0."id" = 1{code}



--
This message was sent by Atlassian JIRA
(v7.5.0#75005)