r2dbc named parameters binding

[mycroft11]

Hello,

Using the latest Spring Boot 3 RC2 and doing a simple query generates the following:

Caused by: java.lang.UnsupportedOperationException: An operation is not implemented: couldn't find mapping for class io.r2dbc.spi.Parameters$InParameter
	at com.github.jasync.sql.db.mysql.binary.BinaryRowEncoder.encoderFor(BinaryRowEncoder.kt:99) ~[jasync-mysql-2.1.7.jar:na]
	at com.github.jasync.sql.db.mysql.encoder.PreparedStatementExecuteEncoder.encodeValue(PreparedStatementExecuteEncoder.kt:62) ~[jasync-mysql-2.1.7.jar:na]
	at com.github.jasync.sql.db.mysql.encoder.PreparedStatementExecuteEncoder.encodeValues(PreparedStatementExecuteEncoder.kt:46) ~[jasync-mysql-2.1.7.jar:na]
	at com.github.jasync.sql.db.mysql.encoder.PreparedStatementExecuteEncoder.encode(PreparedStatementExecuteEncoder.kt:27) ~[jasync-mysql-2.1.7.jar:na]
	at com.github.jasync.sql.db.mysql.codec.MySQLOneToOneEncoder.encode(MySQLOneToOneEncoder.kt:67) ~[jasync-mysql-2.1.7.jar:na]
	at com.github.jasync.sql.db.mysql.codec.MySQLOneToOneEncoder.encode(MySQLOneToOneEncoder.kt:23) ~[jasync-mysql-2.1.7.jar:na]
	at io.netty.handler.codec.MessageToMessageEncoder.write(MessageToMessageEncoder.java:90) ~[netty-codec-4.1.84.Final.jar:4.1.84.Final]

The calling code is like this:

GenericExecuteSpec execute = databaseClient.sql("select title from table where id = ?");
execute.bind(0,34).fetch().all();

[github-actions]

Thank you for reporting an issue. See the wiki for documentation and gitter for questions.

[oshai]

Hi,
Is it an "id in list" query? Or is 0 the binding position? In some frameworks binding position starts from 1.

[mycroft11]

Hi oshai!

0 is the binding position. The fix is to force unrwap the Parameter object into the actual scalar (string/int/long etc) in the org.springframework.r2dbc.core.DefaultDatabaseClient, but of course that's not desirable. Support for this Parameter type would be great.

Another question, how can i map the returned (OUT) values from a mysql stored procedure?

Many thanks,
J

[oshai]

If you can please add a code snippet if the workaround, that would be great. I will take a look how that can be supported.

[oshai]

About the stored procedure. There are some tests here: https://github.com/jasync-sql/jasync-sql/blob/6de042b540b65ea76dbc9dd96e31657969ec322a/mysql-async/src/test/java/com/github/jasync/sql/db/mysql/StoredProceduresSpec.kt
If you can add a test that show what required it will help. Anyway it's better to open a separate issue.

[jamesHanKey]

I'm not sure, but after a little bit of debugging, I think the changes to the spring r2dbc framework which aligns r2dbc for 0.9 commit causes the error.

See here for code

public void bindTo(BindTarget target) {
	for (String namedParameter : this.parameterSource.getParameterNames()) {
		Object value = this.parameterSource.getValue(namedParameter);
		if (value == null) {
			bindNull(target, namedParameter, this.parameterSource.getType(namedParameter));
		}
		else {
			bind(target, namedParameter, value);
		}
	}
}

// changed after 0.9 update
public void bindTo(BindTarget target) {
	for (String namedParameter : this.parameterSource.getParameterNames()) {
		Parameter parameter = this.parameterSource.getValue(namedParameter);
		if (parameter.getValue() == null) {
			bindNull(target, namedParameter, parameter);
		}
		else {
			bind(target, namedParameter, parameter);
		}
	}
}

Before the 0.9 update, the parameters were mapped as original values(scalar) by the spring r2dbc framework. However, after the update, the values are wrapped in Parameters.

Don't know if it's the right place, but could check if the values coming in from r2dbc as Parameters in JasyncStatement

    override fun bind(identifier: String, value: Any): Statement {
        // 
        // check if value class is spi.Parameters and if so, unwrap the value?
        //
        return bind(identifier.toInt(), value)
    }

    override fun bind(index: Int, value: Any): Statement {
        isPrepared = true
        bindings.current()[index] = value
        return this
    }

    override fun bindNull(identifier: String, type: Class<*>): Statement {
        return bindNull(identifier.toInt(), type)
    }

    override fun bindNull(index: Int, type: Class<*>): Statement {
        isPrepared = true
        bindings.current()[index] = null
        return this
    }

[oshai]

Hi, iiuc this is an issue with prepared statements?
if so, can you please provide a test case (for example by adding a test to JasyncR2dbcIntegTest)?

Also I didn't figure out if you suggested a fix, but if so, a PR is welcome!

[oshai]

Is this issues still relevant? Can you please check it with jasync 2.1.11?

[amoradabadi]

Yes, It's still there.

An operation is not implemented: couldn't find mapping for class io.r2dbc.spi.Parameters$InParameter

Tested with
spring-r2dbc:6.0.3
jasync-r2dbc-mysql:2.1.11

You can simply reproduce with Spring DatabaseClient where you use .bind method!

[oshai]

I created a test here: #360

But didn't manage to reproduce.

[amoradabadi]

Hey, try to use a named parameter.

connection
.createStatement("SELECT name FROM users where name in (:name)")
.bind("name", "Dambeldor")
.execute()

[oshai]

Hey, try to use a named parameter.

connection
.createStatement("SELECT name FROM users where name in (:name)")
.bind("name", "Dambeldor")
.execute()

Thanks, I added a test that reproduces it with the suggestion. The exception stacktrace is different in that case:

[ERROR][Test worker][2023-01-17 21:08:20,967][r.c.p.Operators] Operator called default onErrorDroppedreactor.core.Exceptions$ErrorCallbackNotImplemented: java.lang.NumberFormatException: For input string: "bindname"
Caused by: java.lang.NumberFormatException: For input string: "bindname"
	at java.base/java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)
	at java.base/java.lang.Integer.parseInt(Integer.java:652)
	at java.base/java.lang.Integer.parseInt(Integer.java:770)
	at com.github.jasync.r2dbc.mysql.JasyncStatement.bind(JasyncStatement.kt:58)
	at com.github.jasync.r2dbc.mysql.integ.JasyncR2dbcIntegTest$named bind test$1.invoke$lambda-0(JasyncR2dbcIntegTest.kt:152)
	at reactor.core.publisher.MonoFlatMapMany$FlatMapManyMain.onNext(MonoFlatMapMany.java:163)
	at reactor.core.publisher.FluxMapFuseable$MapFuseableSubscriber.onNext(FluxMapFuseable.java:129)
	at reactor.core.publisher.Operators$MonoSubscriber.complete(Operators.java:1816)
	at reactor.core.publisher.MonoCompletionStage.lambda$subscribe$0(MonoCompletionStage.java:92)
	at java.base/java.util.concurrent.CompletableFuture.uniWhenComplete(CompletableFuture.java:859)
	at java.base/java.util.concurrent.CompletableFuture.uniWhenCompleteStage(CompletableFuture.java:883)
	at java.base/java.util.concurrent.CompletableFuture.whenComplete(CompletableFuture.java:2251)
	at java.base/java.util.concurrent.CompletableFuture.whenComplete(CompletableFuture.java:143)
	at reactor.core.publisher.MonoCompletionStage.subscribe(MonoCompletionStage.java:67)
	at reactor.core.publisher.InternalMonoOperator.subscribe(InternalMonoOperator.java:64)
	at reactor.core.publisher.MonoDefer.subscribe(MonoDefer.java:52)
	at reactor.core.publisher.Flux.subscribe(Flux.java:8522)
	at reactor.core.publisher.Flux.subscribeWith(Flux.java:8695)
	at reactor.core.publisher.Flux.subscribe(Flux.java:8488)
	at reactor.core.publisher.Flux.subscribe(Flux.java:8412)
	at reactor.core.publisher.Flux.subscribe(Flux.java:8330)
	at com.github.jasync.r2dbc.mysql.integ.JasyncR2dbcIntegTest$named bind test$1.invoke(JasyncR2dbcIntegTest.kt:165)
	at com.github.jasync.r2dbc.mysql.integ.JasyncR2dbcIntegTest$named bind test$1.invoke(JasyncR2dbcIntegTest.kt:138)
	at com.github.jasync.r2dbc.mysql.integ.R2dbcConnectionHelper.withConfigurableConnection(R2dbcConnectionHelper.kt:148)
	at com.github.jasync.r2dbc.mysql.integ.R2dbcConnectionHelper.withConnection(R2dbcConnectionHelper.kt:130)
	at com.github.jasync.r2dbc.mysql.integ.JasyncR2dbcIntegTest.named bind test(JasyncR2dbcIntegTest.kt:138)

I am checking what can be done for that. I am also trying to understand if this is something used directly or by other frameworks like spring data etc'.

[oshai]

It looks like mysql don't have native support for named params: https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html

OTOH spring data do support that: https://docs.spring.io/spring-data/r2dbc/docs/current/reference/html/#r2dbc.repositories.queries

So to bridge that it's possible to implement it similar to here: https://github.com/mirromutth/r2dbc-mysql/blob/main/src/main/java/dev/miku/r2dbc/mysql/Query.java (this is an older version where the param started with ?, now it starts with :)

There are two downsides I see for doing it:

• parsing might get complicated with some syntax corner cases.
• parsing on the client side will make the driver slower.

But I don't see other alternatives. Suggestions are welcome.

[oshai]

For reference this is r2dbc data change: spring-projects/spring-data-r2dbc#47

@mp911de - any thoughts on this? is parsing on the driver (client) required for the Statement bind(String name, Object value); to work?

[amoradabadi]

It looks like mysql don't have native support for named params: https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html

Named parameters work with Spring boot 2.7 and jasync-r2dbc-mysql (basically with spring-r2dbc 5)
There is something wrong when we use spring boot 3 which uses spring-r2dbc 6

[oshai]

Interesting...
The code for the bind method that throws the exception didn't change during the migration to spi 1 (actually since 2019)

But it doens't seems like it was originally worked in any manner (calling toInt() on string of the param name). I think spring is doing something also so we are reproducing the wrong case with the test.

[moradabadi]

I created a sample application to reproduce the main error
(An operation is not implemented: couldn't find mapping for class io.r2dbc.spi.Parameters$InParameter).

https://github.com/moradabadi/jasync-mysql

[oshai]

Thanks for the reproduction! I understand the issue now.

When setting logs to trace I see the following:

<mysql-connection-1> sendPreparedStatement() - PreparedStatementParams(query=select * from user where name = ?, values=[In{Inferred: java.lang.String}], release=false)

I will create a fix for it soon.

[amoradabadi]

That's awesome, looking forward to it.

[oshai]

I created version 2.1.13 with the fix. Once it's available you're welcome to test it.

I want to also create a test that reproduces it before closing the issue.

[amoradabadi]

In the test application that I created for you the indexed parameter was also not working, so with 2.1.13 , I would think using a named parameter should throw an exception and indexed parameter should work!

I will test it once it's available

[oshai]

I think that also the named parameter will work. I think spring is doing the name binding before sending the query to jasync (that's what I saw on logs).

[amoradabadi]

It worked with both named and indexed parameters, thank you so much!

[oshai]

Adding test in #364.

[oshai]

Fixed in 2.1.13.

[duyleekun]

This library is using name binding. I've been using this library with jasync 2.1.12, it's not working with newer version because of the throw so what should I do now?

The library do provide a way to customize this but I'm wondering why are we not supporting this at all. Seems like a norm in other library

[oshai]

Can you provide a test case for what you're seeing (with latest jasync version)? please open another issue with it.