mssql-jdbc: Cast exception when getting value of a sql_variant

Driver version

• 7.4.1
• 8.2.1

SQL Server version

12.0.2000.8

Client Operating System

Win10

JAVA/JVM version

Oracle’s 1.8.0

Table schema

INFORMATION_SCHEMA

Problem description

1. Expected behaviour: resultset.getLong(colName) to return numeric value as long
2. Actual behaviour: java.lang.ClassCastException: java.lang.Integer cannot be cast to java.lang.Long
3. Error message/stack trace:

java.lang.ClassCastException: java.lang.Integer cannot be cast to java.lang.Long
	at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getLong(SQLServerResultSet.java:2340)

1. Any other details that can be helpful:

• Detected while investigating problem why StoredProcedureQuery parameters were not bound by name, but instead indexes.
• Currently Hibernate is using select * from INFORMATION_SCHEMA.SEQUENCES for obtaining sequence information - using getLong(...) for start_value etc.
• BUT since start_value is a sql_variant and if particular sequence is of int data type class cast exception is thrown, when reading that value and Hibernate falls back to using safe defaults instead of actual capabilities (including supportsNamedParameters = false)
• Quick workaround in the project was to correct the datatype of two sequences. But clearly there is a problem in the driver with sql_variant support.
• normally a BIGINT parameter is propagated down the call stack, but if the the actual column type is sql_variant another logic overrides the expected BIGINT type with the type detected from sql_variant. So an Integer is being returned up the stack where explicit casting is performed to Long. Which fails.
• sql_variant reading logic should consider the data type provided by the caller to avoid class cast exception. Normally it is expected that a wider data type should contain the narrow data type implicitly.

JDBC trace logs

jdbcTrace.log

Reproduction code

ResultSet rs = conn
                .prepareStatement("select cast(1 as sql_variant) as [sad_face]")
                .executeQuery();
if (rs.next()) {
     println ("Long value: " + rs.getLong("sad_face"));
}