Create vendor specific database dialect to handle ID creation specifics of MS-SQL Server [DATAJDBC-278]

[spring-projects-issues]

Thomas Lang opened DATAJDBC-278 and commented

When trying to insert values in a mssql server table the setting of IDs (defined as identity and primary key) with explicit values is not supported.
Here is the exception:

Caused by: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO manual (content, lego_set, id) VALUES (?, ?, ?)]; Cannot insert explicit value for identity column in table 'MANUAL' when IDENTITY_INSERT is set to OFF.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert explicit value for identity column in table 'MANUAL' when IDENTITY_INSERT is set to OFF.

The suggestion is to build a vendor specific database dialect which excludes the id generation on mssql server.

Scripts and tests can be found here:
https://github.com/THD-Thomas-Lang/spring-data-jdbc

---

Affects: 1.0.1 (Lovelace SR1)

Reference URL: https://stackoverflow.com/questions/3112579/identity-insert-is-set-to-off-how-to-turn-it-on

Issue Links:

• DATAJDBC-309 Add infrastructure for semantic SQL generation

1 votes, 4 watchers

[spring-projects-issues]

Jens Schauder commented

The underlying issue of MS-SQL not supporting the inserting columns that can be created by the database will also be resolved by DATAJDBC-224 since then the critical inserts won't happen anymore

[spring-projects-issues]

Mark Paluch commented

Related to this ticket:
Dialects should provide also details on array support and Dialects should provide rendering hooks for vendor-specific SQL to express e.g. pagination and other features.

Ideally, we can leverage RenderContext with specific statement-specific contexts (e.g. ObjectContext for escaping table/column names, SelectContext with hooks for afterSelect, beforeWhere, afterWhere and similar) that expose a Function<? super Select, ? extends CharSequence> (as example) so the dialect can contribute specific parts to the SQL rendering

[spring-projects-issues]

Jens Schauder commented

I created DATAJDBC-357 for the basic dialect support

[schauder]

We no longer include null values, therefore this problem no longer exists.

[cnockemann]

Hello @schauder i still get this Exception when trying to update an Aggregate when using MS-SQL server:

java.sql.BatchUpdateException: Ein expliziter Wert für die Identitätsspalte kann nicht in der TantiemePunkt-Tabelle eingefügt werden, wenn IDENTITY_INSERT auf OFF festgelegt ist.

[schauder]

That is a slightly different case. Could you please create a new issue and provide a reproducer?

[cnockemann]

Just to be clear: this happens to sub-entities of an aggregate who are first deleted and then newly inserted (this seems to be the strategy of spring data jdbc when updating an aggregate).

When i delete the IDs of the sub-entities before updating, it works. Although the sub-entities get new IDs for every Update then.

I will create an issue later.