Actually, Yesql already supports this, albeit unofficially. Here's an example, but please read the caveat at the end before you use it...
Define an IN-query with one parameter: -- name: find-by-age SELECT * FROM person WHERE age IN (:age) Call it, supplying a vector for age: (find-by-age db-spec [18 21 35]) The query will get expanded to ["SELECT * FROM person WHERE age IN (?,?,?)" 18 21 35] ...for the underlying jdbc call, and behave as you hope. So what's the caveat? I haven't tested it sufficiently<https://github.com/krisajenkins/yesql/blob/master/test/yesql/named_parameters_test.clj#L54-56>. And until I do, I'm not going to document it, or make it official. But it's there in v0.3.0, so if you want to field-test it, do let me know your experiences. :-) (And, FWIW, I completely agree with Sean. This kind of thing definitely belongs in a layer above clojure.java.jdbc.) On Monday, 10 February 2014 10:41:55 UTC, Jan Herich wrote: > > The "problem" with libraries you mentioned is that those are fully > featured DSLs for embedding SQL queries > as clojure macros/function calls in your program. > > However, i like the philosophy of Yesql > library<https://github.com/krisajenkins/yesql>, > which leverages clojure/java.jdbc library but encourages you > to keep SQL queries in .sql files for many different reasons (all stated > in README.md <https://github.com/krisajenkins/yesql/blob/master/README.md>of > this library). > > There is a whole class of real-world environments, where this approach is > preferable to full-power DSLs. > My problem is, that i'm forced to abadon Yesql approach whenever my query > has "IN ()" clause within and > i have to write the query as a String in clojure program (or use some > DSLs, but that's what i'm trying to avoid). > If there would be some hook to attach in my proposed behavior to either > clojure/java.jdbc or Yesql, i would > be able to continue writing all of my queries as plain .sql files. > > Of course you can argue that whenever someone needs to do this, it's the > right time to start using DSLs, > i'm not sure on this, that's why i want to discuss this topic. > > Dňa pondelok, 10. februára 2014 0:16:01 UTC+1 Sean Corfield napísal(-a): >> >> 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.h...@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 clo...@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+u...@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+u...@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.
