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)
Hibernate Core 5.4.10, JDK 8 zulu220.127.116.11-ca-jdk8.0.212-linux_x64, Ubuntu 16.04, Sql Server 2014
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?
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