Parameter binding in Batch

[dmitry-s]

In the current implementation Batch.add(...) method only accepts String sql parameter. Is there a way to bind parameters?
It probably would make sense to make add(...) method accept Statement instead of String.

[mp911de]

Batch is intended for unparametrized statements. Statement.add() is intended for parametrized statement batching.

[dmitry-s]

Statement.add() is only useful if there is one statement and multiple sets of parameters. But if we have multiple statements and each of them has multiple parameter sets - I think that would be good use case for Batch.
Also, even if you have only one set of parameters wouldn't it make sense to bind them instead of having to embed them into the actual sql statement string (and doing all type conversions and escaping manually)?

[mp911de]

That’s not how databases work. Database protocols (At least Postgres and SQL Server) provide typically a method to run a batch or a single parametrized statement. Running different parameterized statements needs to happen in multiple roundtrips.

IIRC, Postgres also allows sending the SQL and a table of parameters in a single roundtrip. Combining multiple statements basically would just shortcut multiple Statement executions. So we would create an expectation of running multiple statements in a single go without having implementations that are capable of such a feature.

[dmitry-s]

That's actually how Spanner works, so I assumed it would make sense for other databases as well.

[ChengyuanZhao]

Mark, I see what you're saying about Postgres and SQL Server and indeed we're seeing a lot of specialization in the SPI around those traditional servers. We are just trying to see if there is flexibility to support these newer SQL servers like Spanner.

[mp911de]

lot of specialization in the SPI around those traditional servers

R2DBC was built with traditional SQL databases in mind. R2DBC SPI isn't frozen. It stands for a standard SPI that should be backed by a sufficient number of implementations that find a particular feature useful. If we get 3 or more implementations that natively support that feature, we can include it into SPI. It does not make sense to call a feature standard when it's only implemented by a single database.

We are just trying to see if there is flexibility to support these newer SQL servers like Spanner.

R2DBC SPI is intended to expose standard functionality through a standard SPI where functionality can be broadly consumed by clients. If you want to provide additional functionality in addition to what R2DBC exposes, then adding these features in your classes is the best way how to expose these.

When working with JDBC, it's quite common to obtain a specific interface (see Postgres, Oracle, MySQL) of the driver and then working with that interface directly.

TL;DR: Just because a feature isn't exposed in R2DBC SPI, this does not mean that it's discouraged or should not be implemented.

That being said, let's keep this ticket open and revisit it at a later time when more implementations pick up R2DCBC.

[mirromutth]

... (At least Postgres and SQL Server) ... Running different parameterized statements needs to happen in multiple roundtrips.

MySQL is also needs to happen in multiple roundtrips when running different prepared statements.