Fork me on GitHub
#sql
<
2019-01-29
>
martinklepsch11:01:37

I'm trying to achieve something like this:

select * from versions where (group_id, artifact_id, name) IN (VALUES ('reagent', 'reagent', '0.8.1'), ('re-frame', 're-frame', '0.10.4'));
but am unsure about the right way to write this. Tried the following but that gives me [SQLITE_ERROR] SQL error or missing database (near "?": syntax error)
(sql/query db-spec
             ["select * from versions where (group_id, artifact_id, name) IN ?"
              [["re-frame" "re-frame" "0.10.4"]]])
Do I need to extend ISQLValue for this type of thing or am I missing something?

martinklepsch11:01:36

found this old mailing list post which says that IN isn't supported by clojure.jdbc: https://groups.google.com/forum/#!topic/clojure/7A_HAAISvEk

curtis.summers14:01:24

HugSQL supports IN clauses with its Value List Parameter: https://www.hugsql.org/#param-value-list

curtis.summers14:01:24

And, based on your usage of SQL tuples, you could use HugSQL's Tuple List Parameters as well: https://www.hugsql.org/#param-tuple-list

martinklepsch12:01:09

Thanks, that's very helpful! 🙂

martinklepsch11:01:03

In the same vein I'm also wondering how I can see the final string representation of a fully prepared statement

martinklepsch12:01:24

@jumar damn, almost expected that to be the case 🙂 thanks

🙂 5
bja15:01:33

Iirc, I used a dynamic variable to print the jdbc array in helper functions that wrapped honeysql's format. The code is here: https://github.com/curiosity/curiosity.components/blob/master/src/curiosity/components/jdbc.clj

bja15:01:02

Note with-query-logged and query-runner/exec-runner