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<javascript:>> > 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<javascript:> > > 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 <javascript:> > > 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 <javascript:>. > > 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.
