Fork me on GitHub
#sql
<
2019-08-29
>
Ashley Smith17:08:06

(let [result (sql/query @db/spec
          [ "SELECT 
              tags.tag_id, 
              post_tags.base_value
            FROM post_tags
            INNER JOIN tags ON post_tags.tag_id=tags.tag_id
            WHERE post_tags.post_id IN ?"
            (vec post-ids)])]
I'm having trouble with array parameters and jdbc-next? Is this possible at all? I've heard that using parameterised statements with ? makes things a lot safer so I'm a little uncomfortable concatonating a string together if I can get this to work? (`post-ids` is a lazy-seq, I tried a set and vector too) I get the error:
org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of clojure.lang.PersistentVector. Use setObject() with an explicit Types value to specify the type to use.
	at org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:955)
	at next.jdbc.prepare$eval7663$fn__7664.invokePrim(prepare.clj:36)
	at next.jdbc.prepare$eval7663$fn__7664.invoke(prepare.clj)
	at next.jdbc.prepare$eval7642$fn__7643$G__7633__7652.invoke(prepare.clj:23)

noisesmith17:08:53

sadly you need to construct the ? placeholders individually

noisesmith17:08:03

this is why people use things like honeysql

Ashley Smith17:08:23

could I maybe use apply?

noisesmith17:08:43

if you are using apply to construct a string containing multiple "?", sure :D

seancorfield17:08:43

You have to have a ? for each element of the vector

noisesmith17:08:51

that's what I meant, yeah

Ashley Smith17:08:13

yeah, well I'll write it in a concatted string for now and I might move to honeysql and tidy up my backened later 🙂

seancorfield17:08:28

(into [(str "SELECT ... IN (" (clojure.string/join "," (repeat (count post-ids) "?") ")")] post-ids)

Ashley Smith17:08:46

okay, let me try that ,

Ashley Smith17:08:49

that was quick hahaah

seancorfield17:08:50

This is still safe because you're using parameters ?

seancorfield17:08:13

It's quick because it's a common SQL question and I write that code several times a week 🙂

seancorfield17:08:02

HoneySQL lets you just say (where ... [:in post_tags.post_id post-ids]) as I recall.

noisesmith17:08:40

I guess you could have (defn place-fill [coll] (string/join "," (repeat (count coll) "?")))

seancorfield17:08:35

(! 657)-> clj -Sdeps '{:deps {honeysql {:mvn/version "RELEASE"}}}'
Clojure 1.10.1
user=> (require '[honeysql.helpers :refer :all] '[honeysql.core :refer [format]])
...
nil
user=> (def post-ids (range 20))
#'user/post-ids
user=> (-> (select :tags.tag_id :post_tags.base_value) (from :post_tags) (join :tags [:= :post_tags.tag_id :tags.tag_id]) (where [:in :post_tags.post_id post-ids]) (format))
["SELECT tags.tag_id, post_tags.base_value FROM post_tags INNER JOIN tags ON post_tags.tag_id = tags.tag_id WHERE (post_tags.post_id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?))" 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19]
user=> 
Like so

Ashley Smith17:08:30

Sorry wasn't ignoring you I was just implementing it 🙂 Thank you for your help as always you guys!

Ashley Smith17:08:47

its working, thank you!

isak17:08:32

For postgres, you should use select ... from ... where id = ANY(?), then send a long-array as the value for the ?

Ashley Smith18:08:32

@isak Thank you that actually worked nicely as well! So our tags are now entirely from the database now 🙂

simple_smile 8