Translate SQL Exception with State S0001 and Vendor Code 2628 to a Spring Exception in MSSQL 2019

[NVelichkovski]

Issue Description:

In the Spring JDBC framework, SQL exceptions with state S0001 and vendor code 2628 for MSSQL 2019 are not properly translated to a Spring exception. This behavior differs from the previous version, MSSQL 2017, where the same exception was translated into a Data Integrity Violation exception by Spring. The issue can be reproduced by inserting a value larger in length by the max allowed value of a column.

Expected Behavior:

When encountering an SQL exception with state S0001 and vendor code 2628 message String or binary data would be truncated in table '%.*ls', column '%.*ls'. Truncated value: '%.*ls'. from MSSQL 2019, the Spring JDBC framework should accurately translate it into a corresponding Spring exception. Instead the exception ends uncategorised (UncategorizedSQLException).

In older version of the MSSQL server (2017) the same exception is defined with:

• state: 22001
• vendor code: 8152

then when the chain of the sql exception translators are executed the state class 22 is recognised as an DataIntegrityViolationException within the SQLErrorCodeSQLExceptionTranslator. But in versions above 2017th the state is changed to S0001, so none of the translators understand the exception.

Proposed solution:

Since i could not found another broken exception translation, the vendor code can be added in the sql-error-codes.xml.

[jhoeller]

We generally rely on SQLExceptionSubclassTranslator by default now in 6.0, based on the JDBC 4 exception subclasses, with a few extra tweaks on our side. Which SQLException subclass is being thrown in your scenario there?

That said, you may add a custom sql-error-codes.xml file to still get the error code mappings on 6.0, specifying some custom codes there or even just an empty file in the classpath root (since our legacy default error code mappings still apply then).

[NVelichkovski]

I am on a spring version 5.3.21, but i believe the issue exist in versions 6.0 as well . The subclass is an SQLServerException.
Regarding a solution, i created a temp custom exception translator implementing the SQLErrorCodeSQLExceptionTranslator in order not to be affected, but this now needs to be duplicated across our affected components, so i see it as a temporary solution.

[jhoeller]

So the SQL Server driver is not actually throwing JDBC 4 exception subclasses? Ouch. That might be a problem with the default behavior on Spring Framework 6.0 then.

In any case, we can certainly add 2628 to the dataIntegrityViolationCodes category for our MS-SQL entry in sql-error-codes.xml. With 5.3.x, this will automatically apply then; for 6.0, it requires opting in.