Cannot Bind Parameters in GenericExecuteSpec.filters

[hciq-justin-michalczak]

Here's the concept:

final Integer parameter = 5; // Or maybe null.
final String sql = (parameter == null) ? "SELECT 0" : "SELECT :param";
return databaseClient.sql(sql)
    .filter(statement -> {
        if (parameter == null) {
            return statement;
        } else {
            return statement.bind("param", parameter);
        }
    })
    .all();

(Obviously this simple example could be worked around via other means. The real code involves dynamic SQL over possibly-provided filter parameters.)

Expected

The query functions with the binding provided during the filter chain.

Actual

The statement fails before the filter chain is invoked, indicating that the param binding is not set.

org.springframework.dao.InvalidDataAccessApiUsageException: No parameter specified for [param] in query [SELECT :param]

---

I cannot find any documentation on how the DatabaseClient.GenericExecuteSpec filter chain is intended to work. So I'm unsure whether this would be an enhancement request or bug report.

[hciq-justin-michalczak]

My current workaround is to drop into a Reactor flow instead:

return Mono.just(databaseClient.sql(sql))
    .map(spec -> {
        if (parameter == null) {
            return spec;
        } else {
            return spec.bind("param", parameter);
        }
    })
    .map(spec -> spec.map(transform))
    .flatMapMany(RowsFetchSpec::all);

[mp911de]

spec.bind(…) is the intended usage. DatabaseClient handles named parameter substitution and therefore, you need to provide parameters to DatabaseClient and not Statement.

If you do not use Spring named parameters, then you can set parameters in the filter callback.

[hciq-justin-michalczak]

@mp911de Thanks for your time and response. To restate my understanding: The :param syntax is actually a Spring Data binding feature, and not a R2DBC feature. Thus those parameters must be bound on the GenericExecuteSpec and not the Statement.

It also appears that, unlike JDBC, R2DBC does not have a native parameter binding syntax. Thus any parameters bound on the Statement would use whatever binding dialect is supported by the underlying database.

Does that all look correct?

[mp911de]

R2DBC does not have a native parameter binding syntax.

R2DBC's native parameter binding syntax varies from driver to driver as R2DBC decided to avoid SQL parsing in every driver.