Dialect is not autodetected on Sql Server 2014 with custom INT sequences

Description

Dialect is not detected when a Microsoft Sql Server 2014 database is used in combination with custom sequences of INT type.
The sequence was created the following way: create sequence example as INT start with 42 increment by 1 no cache

See attached sample project (just execute NativeApiIllustrationTest). You need an Sql Server database with the mentioned sequence in it.

This is the relevant log: WARN: HHH000342: Could not obtain connection to query metadata : java.lang.Integer cannot be cast to java.lang.Long

After debugging I found out that hibernates SequenceInformationExtractorLegacyImpl#extractMetadata fails.
This is caused because in SequenceInformationExtractorLegacyImpl#resultSetStartValueSize() it calls resultSet.getLong()
which fails with 'java.lang.Integer' to 'java.lang.Long' exception from the Microsoft Jdbc driver (SqlServerResultSet#getLong(String))

See also the issue I created on spring boot (https://github.com/spring-projects/spring-boot/issues/19579)

Environment

Hibernate Core 5.4.10, JDK 8 zulu8.38.0.13-ca-jdk8.0.212-linux_x64, Ubuntu 16.04, Sql Server 2014

Activity

Show:
Nathan Xu
March 15, 2020, 12:46 PM

seems the dialect is detected otherwise you won’t be able to get sequence info in the first place. Technically, SQLServer2012Dialect` is detected but it seems it should be enough for the sequence purpose. The real problem is the sqlserver jdbc driver is not robust enough to transform int type to long type, as in other more robust drivers.

Not sure we need to fix the issue. Is there any roadblock to update sequence to long type, btw?

Manuel Mall
April 29, 2020, 1:20 AM

I don’t agree with the assessment that this is a bug in the Microsoft JDBC driver. There is no requirement for a driver to automatically cast results. The problem is that SQL Server sequences are typed and can be of any integer type (https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-ver15). That is reflected in the types of the values returned when querying the schema (INFORMATION_SCHEMA.SEQUENCES). The type of value returned by the columns holding the min, max, and start sequence values reflects the type of the sequence as they are of type sql_variant. However, Hibernate assumes it always returns a Long which is an incorrect assumption. I recently had to deal with the issue and wrote a small custom dialect that works around the issue.

See also: https://discourse.hibernate.org/t/java-lang-classcastexception-java-math-cannot-be-cast-to-java-lang-long-while-starting-server/4074 and https://discourse.hibernate.org/t/sql-server-could-not-fetch-the-sequenceinformation-from-the-database/4083

Assignee

Unassigned

Reporter

C. B.

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Blocker
Configure