How to use dynamic values for :in clause

[simkoc]

I am having a similar issue to #74, however, proposed solution using (:set) does not work.

I create a prepared query that requires a list of values as the only parameter, bu however I try I am running into issues.

Attempt A:

(defprepared example-a
  (:select 'a 'b
   :from 'example
   :where (:in 'a '$1)))
(example-a '(1 2 3))
Database error 42601: syntax error at or near "$1"

Attempt B:

(defprepared example-b
  (:select 'a 'b
   :from 'example
   :where (:in 'a (:set '$1))))
(example-b '(1 2 3))
ERROR: Value (1 2 3) can not be converted to an SQL literal.

How can I solve this?

[simkoc]

My current solution is to avoid :in and use :any* instead. But this cannot be the answer, as I also need to convert the list to a vector.

(defprepared example-working-any
  (:select 'a 'b
   :from 'example
   :where (:= 'a (:any* '$1))))
(example-working-any (coerce '(1 2 3) 'vector))

[sabracrolleton]

https://marijnhaverbeke.nl/postmodern/s-sql.html#sql-op-in

"IMPORTANT: If you are trying to use a list in a parametized statement, you can't. You have to convert the list to a vector and use "any" rather than "in." "

[simkoc]

Ok, thank you for the quick response. This is hidden deep inside related documentation.

[sabracrolleton]

Good Point. I will add it to other parts of the documentation.