How to pass uncertain parameters to preparedQuery while using reactive-postgresql-client?

Question

Anyone has idea how to pass uncertain parameters to client.preparedQuery? Pls look at the image below, appreciate any help!

@Override
public Future<List<User>> getByIds(List<String> ids) {
    Promise<List<User>> promise = Promise.promise();
    // How to pass the uncertain parameters to preparedQuery?
    pool.preparedQuery("SELECT * FROM users WHERE id IN ($1)", Tuple.wrap(ids.toArray()), ar -> {
        if (ar.succeeded()) {
            RowSet<Row> rows = ar.result();
            List<User> users = new ArrayList<>();
            for (Row row : rows) {
                User user = User.fromDatabaseRow(row);
                users.add(user);
            }
            promise.complete(users);
        } else {
            promise.fail(ar.cause());
        }
    });
    return promise.future();
}

@Suge What makes the image more clear than pasting the code in as text and explaining the problem? — Andreas, Feb 14 '20 at 11:00
@Suge Nope, I see no "debug message" that adds any clarity. The only "clarity" I can see, is that `ar` / `result` / `list` / `size = 0`, meaning that the query executed ok but didn't return any rows, which could easily be written in text, so the image is *less* clear than it would have been if you's explicitly told us that, instead of us having to interpret the `Variables` pane to infer that. — Andreas, Feb 14 '20 at 11:01
[Why not upload images of code on SO when asking a question?](https://meta.stackoverflow.com/q/285551/5221149) — Andreas, Feb 14 '20 at 11:06
@Andreas So you don't have any idea of `preparedQuery` usage? — Suge, Feb 14 '20 at 11:09
@Suge I do. Why do you ask? Isn't answer by YCF_L good enough? If not, say so. — Andreas, Feb 14 '20 at 11:13

Youcef LAIDANI · Accepted Answer · 2020-02-14T11:31:06.007

2

To passe an array to PostgreSQL then you have to use = ANY(params) instead of IN(params)

SELECT * FROM users WHERE id = ANY($1)

Also, you have to passe an array of String to ANY, your code should be :

pool.preparedQuery(
        "SELECT * FROM users WHERE id = ANY($1)", 
        Tuple.of(ids.toArray(String[]::new)), 
        ar -> {..}
)

edited Feb 14 '20 at 11:31

answered Feb 14 '20 at 11:01

Youcef LAIDANI

55,661
15
90
140

Thank you, I tried `pool.preparedQuery("SELECT * FROM users WHERE id = ANY($1)", Tuple.wrap(ids), ar -> {}` and it doesn't work, how should I pass `List ids` to the `preparedQuery`? Thank you! – Suge Feb 14 '20 at 11:13
@Suge try `ids.toArray(i -> new String[i])` or `ids.toArray(String[]::new)` in Java11 I think – Youcef LAIDANI Feb 14 '20 at 11:18
thank you, but not work, it throws error `Future{cause=Parameter at position[0] with class = [[Ljava.lang.Object;] and value = [[Ljava.lang.Object;@403d58df] can not be coerced to the expected class = [[Ljava.lang.String;] for encoding.}` – Suge Feb 14 '20 at 11:21
@Suge sorry you are so quick then me :) I edit my comment late can you read it again please? – Youcef LAIDANI Feb 14 '20 at 11:22
@Suge beside, are you sure that id is of type String? and not other type? If the id is of type Long(as we know all), then try `ids.stream().mapToLong(Long::valueOf).toArray()` – Youcef LAIDANI Feb 14 '20 at 11:25
Thank you very much, it does work, code: `pool.preparedQuery("SELECT * FROM users WHERE id = ANY($1)", Tuple.of(ids.toArray(String[]::new)), ar -> {}`; But if I make the SQL manually , `SELECT .... WHERE id IN (...)` also works: `String args = ids.stream().map(id -> "'" + id +"'").collect(Collectors.joining(",")); String sql = String.format("SELECT * FROM projects WHERE id IN (%s)", args); pool.query(sql, ar -> {})`, but we can't use it like that: `pool.preparedQuery("SELECT * FROM users WHERE id IN ($1)", Tuple.of(ids.toArray(String[]::new)), ar -> {}`, that's strange – Suge Feb 14 '20 at 11:27
Yes, ids is a string list – Suge Feb 14 '20 at 11:28

How to pass uncertain parameters to preparedQuery while using reactive-postgresql-client?

1 Answers1