addParameter for IN operator

[mig...@gmail.com]

Hi,

I have in my sql statement a clause like:  WHERE col IN (:param). When I tried to pass a List<Integer> for addParameter I got an exception:
org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of java.util.ArrayList. Use setObject() with an explicit Types value to specify the type to use.

When I tried to pass int[] I got an exception:
org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of [J. Use setObject() with an explicit Types value to specify the type to use.

Is there a way to use addParameter to substitute :param with a list?

Thanks,
Michael

[lars....@sonat.no]

Hi Michael,

It is currently not possible to use lists or arrays as parameters. You will have to do the work of building the sql query and adding parameters one at the time yourself.

But you are not the first to ask for this feature, so I will add it on the todo list.

Regards
Lars Aaberg

[Michael Kahane]

Hi  Lars,

Thanks for considering this feature. For now I found out that (at least with postgresql) I can pass the list as a string parameter on the java side, and then on the server side split it and cast it to the proper type. On the java side I'm using something like: addParameter("param", StringUtils.join(intList, ",")); and in the sql query: WHERE col = ANY (string_to_array(:param, ',')::INT[]).

Michael

--
You received this message because you are subscribed to a topic in the Google Groups "sql2o" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sql2o/dFLXGZjLtr4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sql2o+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.