Using clojure.java.jdbc to process multiple result sets from a SQL Server instance.

multiple-resultsets.clj

;; Does not handle parametized queries for the 
;; simple reason that I was dealing with a pre-formatted
;; string to call a stored procedure.
;;
;; Feel free to fork and improve. :-)

(require '[clojure.java.jdbc :as j]
         '[clojure.java.jdbc.sql :as s])


(def db {:classname "com.microsoft.jdbc.sqlserver.SQLServerDriver"
         :subprotocol "sqlserver"
         :subname (db-connect-string :your-connect-string-here)})

(defn get-multiple-result-sets [cmd]
  (j/with-connection db
    (let [stmt (.createStatement (j/connection))]
      (if (.execute stmt cmd)
        (loop [rslt (into [] (j/result-set-seq (.getResultSet stmt)))]
          (if (.getMoreResults stmt)
            (recur (conj rslt (into [] (j/result-set-seq (.getResultSet stmt)))))
            rslt))))))

Great stuff, thanks!

The below version will handle parameters, set-parameters is just copied from the clojure.java.jdbc source.

(defn- set-parameters
  "Add the parameters to the given statement."
  [stmt params]
  (dorun (map-indexed (fn [ix value]
                        (jdbc/set-parameter value stmt (inc ix)))
                      params)))


(defn query-multiple [sql params]
  (jdbc/with-db-connection [c handle]
    (let [stmt (jdbc/prepare-statement (:connection c) sql)]
      (set-parameters stmt params)
      (if (.execute stmt)
        (loop [rslt [(into [] (jdbc/result-set-seq (.getResultSet stmt)))]]
          (if (.getMoreResults stmt)
            (recur (conj rslt (into [] (jdbc/result-set-seq (.getResultSet stmt)))))
            rslt))))))