Allow configuring the prepared statement cache

[vijayakumarsuraj]

Running into an error where the first time a query executes (using the cursored execution mode) it works, but running it again fails because my SQL Server didn't recognize the cached prepared statement handle.

In my use-case, disabling the statement cache would be sufficient - my queries don't run very often. But in general, perhaps having discovery options that can control this cache, or exposing PreparedStatementCache as a type that applications can provide their own implementation of, would be suitable?

As a workaround I am reflectively updating the ConnectionOptions.preparedStatementCache field with my own "NoCache" implementation. But this is obviously not ideal.

I'm using 0.8.5.RELEASE, and running on Java 11. SQL Server version is 2012.

[mp911de]

Sounds like a good enhancement. Do you want to submit a pull request?

[vijayakumarsuraj]

Yep. Can do.

[vijayakumarsuraj]

@mp911de , created https://github.com/r2dbc/r2dbc-mssql/pull/229 for this.

[stephanpelikan]

I see errors Could not find prepared statement with handle 1073741825 when repeating queries fast.

Actually not really fast, I do it by clicking a button repeatingly. If I click once per second, everything is fine. If I click faster I get the error sometimes. Sometimes it also seems to stuck and I get the error for every attempt.

In one of my applications I enabled the R2DBC pool configuration what solved the problems. In another of my application the enabling the pool didn't change the behavior at all.

What do you think. Can this PR solve this problem?