[FEATURE REQUEST] Add a way to execute batches that return ResultSets

[rdicroce]

Is your feature request related to a problem? If so, please give a short summary of the problem and how the feature would resolve it

It is currently extremely difficult to do batch INSERTs when you need to get back the auto-increment row IDs (#245 has quite a few people complaining about this) or any other generated column. As far as I can tell, there are currently only two solutions:

1. Do the inserts one at a time, thus losing all the performance benefits of batching.
2. Create a temp table and use OUTPUT INTO to add rows to that table during the batch insert, then read and drop the temp table at the end.

It is currently not possible to use OUTPUT without INTO when using batched statements, because mssql-jdbc follows the JDBC spec, which specifically bans batched statements from returning ResultSets.

Describe the preferred solution

Create a way to execute batches that are allowed to return ResultSets. This could maybe be done by adding a config property that allows the driver to violate the JDBC spec during executeBatch(), or by adding a new executeBatchWithResults() method to ISQLServerStatement. That way, it's possible to use OUTPUT without INTO when using batched statements, which makes batch inserts a lot easier and provides a straightfoward (though vendor specific) workaround for #245.

[tkyc]

I'll need to discuss this with the team as issue 245 was point of very hot contention.

[rdicroce]

To be clear, I brought up 245 as an example of a situation where this would help, but this would be useful in any situation where the user wants to do a batched INSERT/UPDATE/DELETE with an OUTPUT clause.

I'm not asking the driver to do any magic here. Doing a non-batched INSERT/UPDATE/DELETE with an OUTPUT clause is already possible: just use executeQuery() to get back the ResultSet. What I'm asking for is an equivalent to that for batched queries, so a temp table is not needed.

[lilgreenbird]

hi @rdicroce

Thanks we understand the frustration but as mentioned in #245 this is a server limitation. Your suggestion is a possible enhancement which I have marked as such and will be triaged along with other bugs/features when we do planning for our next semester.

[rdicroce]

I'm regretting that I even mentioned 245. Please keep in mind that this is not really about 245. This is about the fact that OUTPUT clauses are incompatible with batch execution. If this is implemented, it happens to provide a solution to 245. But if 245 were implemented, that would NOT solve all cases that this request would solve.