Batch Return Does not work with SQL Server

[deusaquilus]

Version: (e.g. 3.0.0-SNAPSHOT)
Module: (e.g. quill-jdbc)
Database: (e.g. sql server)

Expected behavior

This should return a list of IDs

testContext.run(liftQuery(productEntries).foreach(e => productInsert(e)))

Actual behavior

Instead SQL shows the following error:

The statement must be executed before any results can be obtained.

Workaround

No good workaround. Need to insert records one-by-one.

Solution:

This is happening because getGeneratedKeys won't work for SQL server unless you do:

conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS)

Fix this in the SQL Server context. Then change this:

productEntries.map(product => testContext.run(productInsert(lift(product))))

Back to this:

testContext.run(liftQuery(productEntries).foreach(e => productInsert(e)))

In ProductJdbcSpec (in jdbc context, and monix jdbc context)

(I noticed the solution here)

Edit:
Actually it looks like getGeneratedKeys is not supported after executeBatch (mssql issue is here). That means that we have to do just execute for now.

@getquill/maintainers

[deusaquilus]

So I figured this stuff out with SQL Server in ProtoQuill. The issue is that SQL Server does not work correctly (here) with the combination of preparedStatement.addBatch -> preparedStatement.executeBatch -> preparedStatement.getGeneratedKeys as has been mentioned above.

However, there is one possibly recourse which is to use a combination of the OUTPUT clause together with preparedStatement.executeQuery after each call to preparedStatement.addBatch as I have implemented in ProtoQuill here. This approach has also been mentioned here on a post in a related mssql-jdbc issue.

The problem is doing preparedStatement.executeQuery after every single-row-insert is the very antithesis of doing batching. You're literally executing a query on every single row-insert which is the very definition of N+1! That means that the only way that this workaround makes any sense is to implement it together with VALUES-clause batch optimization i.e. make every addBatch call insert MORE than one row (1000 rows works bet in practice). That way, the preparedStataement after each addBatch will return 1000 ids instead of one.

This functionality was already implemented in ProtoQuill here: zio/zio-protoquill#172. I am investigating the possibility of implementing it in Scala2-Quill.