欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

SQL Server 报错:com.microsoft.sqlserver.jdbc.SQLServerException: The "variant" data type is not suppor...

程序员文章站 2024-02-29 11:34:34
...

查询 SQL SERVER 中某张表结构,sql 语句如下:

SELECT
    tb.name AS tableName,
    col.name AS columnName,
    col.max_length AS length,
    col.is_nullable AS isNullable,
    t.name AS type,
    (
    SELECT
        TOP 1 ind.is_primary_key
    FROM
        sys.index_columns ic
        LEFT JOIN sys.indexes ind ON ic.object_id = ind.object_id AND ic.index_id= ind.index_id AND ind.name LIKE 'PK_%'
    WHERE
        ic.object_id = tb.object_id AND ic.column_id= col.column_id
    ) AS isPrimaryKey,
    com.value AS comment
FROM
    sys.TABLES tb
    INNER JOIN sys.columns col ON col.object_id = tb.object_id
    LEFT JOIN sys.types t ON t.user_type_id = col.user_type_id
    LEFT JOIN sys.extended_properties com ON com.major_id = col.object_id
    AND com.minor_id = col.column_id
WHERE
    tb.name = '表名'

该 sql 可以正常执行,但是当把 sql 放到 jdbcTemplate 中执行时报一下错误:

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The "variant" data type is not supported.

原因是 sql 语句 select 后面有 sql_variant 类型的属性,在 JDBC 中不支持它。使用 sp_columns 命令最终查出 sys.extended_properties 表的 value 属性的 TYPE_NAMEsql_variant 类型的,sql 如下:

sp_columns extended_properties

解决方法是使用 CONVERT 函数将该属性转成 varchar 类型。

CONVERT 函数的用法参考:SQL Server 中 CONVERT() 函数的使用

修改后的 sql 语句为:

SELECT
    tb.name AS tableName,
    col.name AS columnName,
    col.max_length AS length,
    col.is_nullable AS isNullable,
    t.name AS type,
    (
    SELECT
        TOP 1 ind.is_primary_key
    FROM
        sys.index_columns ic
        LEFT JOIN sys.indexes ind ON ic.object_id = ind.object_id AND ic.index_id= ind.index_id AND ind.name LIKE 'PK_%'
    WHERE
        ic.object_id = tb.object_id AND ic.column_id= col.column_id
    ) AS isPrimaryKey,
    CONVERT(varchar(200), com.value) AS comment
FROM
    sys.TABLES tb
    INNER JOIN sys.columns col ON col.object_id = tb.object_id
    LEFT JOIN sys.types t ON t.user_type_id = col.user_type_id
    LEFT JOIN sys.extended_properties com ON com.major_id = col.object_id
    AND com.minor_id = col.column_id
WHERE
    tb.name = '表名'

参考:

com.microsoft.sqlserver.jdbc.SQLServerException: The "variant" data type is not supported.