Add support for SQL Server's `INSERT … OUTPUT` clause

[vasilhsfoto]

Current setup

dependencies
spring boot v 2.4.3
<dependency> <groupId>org.springframework.data</groupId> <artifactId>spring-data-r2dbc</artifactId> </dependency> <dependency> <groupId>io.r2dbc</groupId> <artifactId>r2dbc-mssql</artifactId> </dependency> <dependency> <groupId>io.r2dbc</groupId> <artifactId>r2dbc-pool</artifactId> </dependency>
Repository
ItemRepository extends ReactiveCrudRepository<Item, UUID>

DB schema in mssql server
[id] UNIQUEIDENTIFIER default NEWID(),
...
code
var itemToSave = new Item().setName(); RegistrationRoute newSavedEntity = itemRepo.save(itemToSave) .log() .block();
The entity is the same instance and the id is not populated.

Notes:
The above works as expected when running on H2 data base. Although the instance is mutated as opposed a brand new one to be created which I would expect.

The type of the id has the same behaviour declared either UUID or String. Which is as expected since the mapping between DB type and Java type comes from the mssql r2dbc driver which supports UUID/String -> uniqueIdentifier

Trying the varchar() (with auto generated GUID) -> String mapping for key. Still doesn't work
Trying the INT (with IDENTITY => autogenerate sequence)-> Integer/int mapping for key it does work as expected

[mp911de]

This is by design. SQL Server doesn't support by default echoing of inserted values through select SCOPE_IDENTITY() AS GENERATED_KEYS.

The R2DBC driver aligns with the JDBC driver, see:

https://github.com/microsoft/mssql-jdbc/blob/89bb744675941113a0ca9bcd7dc3c8f1310bb23c/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerStatement.java#L860-L863

Capturing output values requires a different INSERT statement that declares an OUTPUT clause:

https://docs.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql?view=sql-server-ver15#t-using-output-with-an-insert-statement

[vasilhsfoto]

My goal is to use the repository.
AFAIU, I can't generate the id of the entity in the java code. there is no generated ID annotation or something like that.
At the same time, the Insert statement generated by repository doesn't return the generated id as per your comment.
So looks like for now the only option is to use template or DatabaseClient. And since there is a bug currently with using template on insert (DB errors are not thrown as exception). Looks like the only option is to use DatabaseClient for the insert.
could you please advice