is it possible to substitute an array into an `IN (?)`

[BrianHicks]

I have a query that looks like:

SELECT column FROM table WHERE other_column IN (?)

I'm trying to port this code to sqlite-simple, but I can't find a way to provide an array to be substituted into the (?) above—I've tried providing it directly, but I don't see any typeclass instances for arrays. Is there something simple I'm just missing here?

[cideM]

Same problem, the part about In was removed from the SQLite docs and I can't find any mention of it in code so I guess this was just never implemented :(

[nurpax]

I don’t think sqlite parameter substitution supports this. When I wrote the code, I only added arg substitution support for things that sqlite supports natively. This way the SQL query string can also be directly passed down without modifying or parsing it in Haskell. This was an important design principle for me. (I don’t work on this library anymore and have given up maintainership but thought I’d give some context.)

[robinp]

Didn't try in the end, but if you run into this, can be worked around with loading stuff into a temporary table (don't forget to start a transaction to avoid weird races):

        SQ.execute_ conn [sql| DROP TABLE IF EXISTS temp.tps |]
        SQ.execute_ conn [sql| CREATE TABLE temp.tps (tp TEXT) |]
        SQ.executeMany conn [sql| INSERT INTO temp.tps VALUES (?) |] concatTps
        res <- SQ.query conn [sql|
                SELECT .....
                WHERE ........ IN (SELECT * FROM temp.tps)
                |] (......)

[wraithm]

I think if sqlite-direct supported sqlite3_carray_bind() (https://sqlite.org/carray.html) and your version of sqlite version is compiled with carray support (or run-time loaded), it looks like sqlite-simple could support the postgresql-simple style IN binding.

That's a lot of ifs, haha. It might not be worth the effort, but just something to think about.

[jchia]

I think if sqlite-direct supported sqlite3_carray_bind() (https://sqlite.org/carray.html) and your version of sqlite version is compiled with carray support (or run-time loaded), it looks like sqlite-simple could support the postgresql-simple style IN binding.

That's a lot of ifs, haha. It might not be worth the effort, but just something to think about.

carray is accessed from a loadable extension. direct-sqlite does not directly support it. The macro SQLITE_ENABLE_CARRAY was proposed by someone some time ago for enabling native carray, but I don't think the proposal got accepted.