Binding an array type to an update query using database client execute

[barbyq]

Hi, Im trying to do the following

val rowsUpdated = databaseClient.execute(
            """
        UPDATE listings 
        SET issuer_id = :issuerId,
            title = :title,
            description = :description,
            tags = :tags
        WHERE id = :id
        """
        ).bind("issuerId", listing.issuerId)
            .bind("title", listing.title)
            .bind("description", listing.description)
            .bind("tags", listing.tags)
            .bind("id", listing.id!!)
            .fetch()
            .awaitRowsUpdated()

where tags is declared in the database as type text[]
when I run I get the following

Oct. 06, 2020 17:51:07 +1100 [Test worker @coroutine#1]  DEBUG o.s.d.r.core.NamedParameterExpander: Expanding SQL statement [
        UPDATE listings 
        SET issuer_id = :issuerId,
            title = :title,
            description = :description,
            tags = :tags
        WHERE id = :id
        ] to [
        UPDATE listings 
        SET issuer_id = $1,
            title = $2,
            description = $3,
            tags = $4, $5
        WHERE id = $6
        ]

Oct. 06, 2020 17:51:07 +1100 [tc-okhttp-stream-370411758]  ERROR c.a.a.u.postgres.DatabaseContainer: 2020-10-06 06:51:07.294 UTC [70] ERROR:  syntax error at or near "$5" at character 446

I think the following is because substituteNamedParameters in NamedParameterUtils
where there is a condition that checks if (value instanceof Collection) and this creates separate markers for each item in the list. The case mentioned in the docs mentions queries such as select id, name, state from table where (name, age) in (('John', 35), ('Ann', 50))} where you do want each item to be a different parameter but for my case I want it to be treated as one array type, is this supported at the moment?

When I try to pass it as a string in the format of arrays {new,here} the error returned instead is
ERROR: column "tags" is of type text[] but expression is of type character varying at character 441
is there a workaround?

Thanks

[mp911de]

Naming parameter support unrolls Collection parameters to individual parameters to support IN(…) clauses. Collection arguments aren't supported by R2DBC drivers because they can't encode these as they lack type information on the Collection<T> level.

The only thing that is supported is passing array values as bind values. I'm not sure about:

When I try to pass it as a string in the format of arrays {new,here} the error returned instead is

Do you mean .bind("tags", "{new, here}") or do you mean .bind("tags", arrayOf("new", "here"))? The latter should work since the given value should be passed thru to the driver.

[barbyq]

Yes, that works, thanks a lot closing the issue !

[mp911de]

Do you think we should add a note in the reference documentation to outline this distinction?

[barbyq]

I guess I got confused by
https://docs.spring.io/spring-data/r2dbc/docs/current-SNAPSHOT/reference/html/#r2dbc.datbaseclient.binding
seeing the example for queries with IN

The following example shows a simpler variant using IN predicates:

db.execute("SELECT id, name, state FROM table WHERE age IN (:ages)")
    .bind("ages", Arrays.asList(35, 50))

I assumed support for lists worked as well on other types of queries, but it makes sense that it takes in a native array 🤦‍♀️.
The other thing was that when just using the CrudRepository implementation the Record can have a List and it handles it for you so it wasn't as clear to me. So overall, in my opinion it would help to add a note but I'm also pretty new to r2dbc and even kotlin development so might've just been me, but thanks so much for the quick help!

[mp911de]

Thanks a lot. That section went into Spring Framework so I'll file a ticket/submit a PR to improve the documentation and make it less confusing.