Thank you for your explanation Kris, i will test this functionality in Yesql.
Dňa pondelok, 10. februára 2014 14:40:18 UTC+1 Kris Jenkins napísal(-a): > > 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.
