0

My query is something like

(def list [1,2,3,4])

(def result (exec-raw
   ["SELECT * from table_name where table_id in (?)"],[list] :results))

I keep getting this error that Clojure can't infer SQL type for persistent vector, use setObject to tell the type, now how do I use setObject with CLojure?

Amogh Talpallikar
  • 12,084
  • 13
  • 79
  • 135

2 Answers2

3

If you want a raw sql you'll need to concatenate '?':

(def list ["AAPL" "GOOG"])
(def questions
  (->> (repeat (count list) "?")
       (interpose ",")
       (apply str)))

(def q (str "SELECT * FROM ta_indicators WHERE ticker IN ("
            questions ")"))

(println (exec-raw [q list] :results))

It looks korma does the same thing and works:

(defentity ta_indicators)
(-> (select* ta_indicators)
    (fields :ticker)
    (where {:ticker [in ["GOOG" "TSLA"]]})
    (as-sql))

;; "SELECT \"ta_indicators\".\"ticker\" FROM \"ta_indicators\" WHERE (\"ta_indicators\".\"ticker\" IN (?, ?))"
edbond
  • 3,921
  • 19
  • 26
  • 1
    that's what I had in mind (except for `clojure.string/join` instead of `(apply str (interpose ...))`). well done! – xsc Dec 06 '13 at 08:46
1

Shouldn't the function be called like so:

(exec-raw ["select ..." list] ...)

You might have list in the wrong place.

xsc
  • 5,983
  • 23
  • 30
  • 1
    Still the query doesn't works. It seems array params are tricky in jdbc - http://stackoverflow.com/questions/2861230/what-is-the-best-approach-using-jdbc-for-parameterizing-an-in-clause – edbond Dec 06 '13 at 07:49
  • I am concatenating and strings at the moment. :( – Amogh Talpallikar Dec 06 '13 at 07:56
  • Interesting. So, it probably comes down to using `clojure.string/join` to construct a `IN (?,?, ...)` clause and then flatten the input seq. – xsc Dec 06 '13 at 07:59
  • yeah.. and i am worried about sql injection now. any idea, how can i take more security measures ? – Amogh Talpallikar Dec 06 '13 at 18:48
  • If you create a query like `SELECT ... WHERE x IN (?,?,?)` and supply the parameters separately, korma will handle the replacement of `?` with the respective, escaped values. This will prevent SQL injection as long as korma is working correctly. Just take a look @edbond's answer; it might not be a generic solution but you can easily apply to your case. – xsc Dec 06 '13 at 19:00