As maintainer of java.jdbc I'd say this is a more appropriate feature
for a DSL library like SQLingvo or HoneySQL (which may already support
something like this - I haven't checked).
Sean
On Sun, Feb 9, 2014 at 12:40 PM, Jan Herich <jan.her...@gmail.com> wrote:
> Hello folks,
>
> In the last days, i was working with clojure/java.jdbc and yesql libraries
> (which are both great piece of work),
> the experience was overall very positive, but one thing bothered me, that i
> was unable to use plain prepared
> statements (and sadly, yesql) when working with IN clauses and collection
> values as arguments for prepared
> statements. So i created following helper function to help with generating
> correct prepared statements:
>
> (ns db-util
> (:require [clojure.string :as str]))
>
> (def ^:private placeholders-for (comp (partial str/join ",") #(repeat % '?)
> count))
>
> (defn in-statement
> "Takes a prepared SQL statement and variable number of arguments, which
> may be
> also collection values. Replace all occurences of IN (?) with spliced out
> values
> such as IN (?,?,?) where number of placeholder characters is the same as
> count
> of elements in corresponding argument which is assumed to be a
> collection.
> In case that collection argument has only one element, IN (?) is
> transformed
> into more effective = ? form. Placeholders in query which don't
> corresponds to
> collection arguments are unnafected. Returns vector, with first item of
> the
> vector as transformed prepared SQL statement and rest as spliced out
> arguments."
> [statement & args]
> (let [in-placeholders-positions (->> (re-seq #"\?|IN \(\?\)" statement)
> (map vector (iterate inc 0))
> (filter #(= (second %) "IN (?)"))
> (map first)
> (set))
> in-placeholders-args (->> args
> (map vector (iterate inc 0))
> (filter #(contains?
> in-placeholders-positions (first %)))
> (map second))
> expanded-statement (reduce (fn [acc arg]
> (str/replace-first acc #"IN \(\?\)"
> (if (> (count arg)
> 1)
> (str "IN ("
> (placeholders-for arg) ")")
> "= ?")))
> statement in-placeholders-args)
> unspliced-args (->> args
> (map #(if (coll? %) (seq %) %))
> (flatten))]
> (into [] (cons expanded-statement unspliced-args))))
>
> ;; following holds true
> (= (in-statement "id = ? AND user_id IN (?) AND msg_id IN (?)" 1 #{2 3 4}
> #{5})
> ["id = ? AND user_id IN (?,?,?) AND msg_id = ?" 1 2 3 4 5])
>
> Now my question is, do you think that something in this flavor would be good
> addition to clojure/java.jdbc
> or yesql libraries (the latter one is probably more appropriate for
> inclusion) ? If so, i will try to refine and
> generalize my solution, think about how to integrate it and then issue an
> pull request.
>
> Cheers Jan
>
> --
> You received this message because you are subscribed to the Google
> Groups "Clojure" group.
> To post to this group, send email to clojure@googlegroups.com
> Note that posts from new members are moderated - please be patient with your
> first post.
> To unsubscribe from this group, send email to
> clojure+unsubscr...@googlegroups.com
> For more options, visit this group at
> http://groups.google.com/group/clojure?hl=en
> ---
> You received this message because you are subscribed to the Google Groups
> "Clojure" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to clojure+unsubscr...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.
--
Sean A Corfield -- (904) 302-SEAN
An Architect's View -- http://corfield.org/
World Singles, LLC. -- http://worldsingles.com/
"Perfection is the enemy of the good."
-- Gustave Flaubert, French realist novelist (1821-1880)
--
You received this message because you are subscribed to the Google
Groups "Clojure" group.
To post to this group, send email to clojure@googlegroups.com
Note that posts from new members are moderated - please be patient with your
first post.
To unsubscribe from this group, send email to
clojure+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/clojure?hl=en
---
You received this message because you are subscribed to the Google Groups
"Clojure" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to clojure+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
