Scala Doobie query hardcoded values

Question

When writing the following query in Doobie :

(SELECT id FROM (VALUES(?),(?),(?)) table(id))
UNION
SELECT id FROM table

I have list of data , e.g. List(1,2,3,4) that is varied-size. How could I interpolate the list of values into SQL VALUES CLAUSE using Doobie?

You have three `?` for interpolation but a list of four values... can you give more detail on this? — Lasf, May 29 '18 at 10:40
Unfortunately I don't think it is possible to have variable-length parameterized queries. — Lasf, May 29 '18 at 10:59

score 0 · Answer 1 · answered Jan 03 '19 at 09:37

Hi you can refer below reference from doobie official community page:

Consider a below table DDL:     
CREATE TABLE country (
          code       character(3)  NOT NULL,
          name       text          NOT NULL,
          population integer       NOT NULL,
          gnp        numeric(10,2)
          -- more columns, but we won't use them here
    )

SQL using doobie syntax where codes is varied-size list:

sql"""
    select code, name, population, gnp 
    from country
    where code in (${codes : codes.type})
""".query[Country]

Can you link that community page, this gives me `type mismatch;` error ` required: doobie.syntax.SqlInterpolator.SingleFragment[_]` — ehsun7b, Sep 21 '22 at 06:35

Scala Doobie query hardcoded values

1 Answers1