clojure.java.jdbc Oracle clob

gistfile1.txt

(defn clob-to-string [clob]
  "Turn an Oracle Clob into a String"
  (with-open [rdr (java.io.BufferedReader. (.getCharacterStream clob))]
    (apply str (line-seq rdr))))

(with-connection mydb
  (transaction
   (with-query-results rs ["select documentation from station"] 
     ; rs will be a sequence of maps, 
     ; one for each record in the result set. 
     (doseq [row rs] (println (clob-to-string (:documentation row)))))))

(comment "documentation" is stored as an Oracle clob in the table "station". 
The above works in clojure.java.jdbc if I def mydb. 
How do I use this with a connection defdb'd in korma?)

;; A partial solution:
(with-connection (get-connection mydb)
  (transaction
   (with-query-results rs ["select documentation from station"] 
     ; rs will be a sequence of maps, 
     ; one for each record in the result set. 
     (doseq [row rs] (println (clob-to-string (:documentation row)))))))

(comment is there a more elegant way to do this?)

Some slight modification required to fit with newer versions of the java.jdbc API

(let [rs (query mydb ["SELECT documents FROM station"] :row-fn #(clob-to-string (:documents %)))
(println (str "Docs: " (first rs))))

This ensures the clob is processed while the db connection is still open.

Thanks, clob-to-string works with SQL Server+jTDS too.

With the latest java.jdbc API (0.6.1 onwards) this whole example would become:

(defn clob-to-string [clob]
  "Turn an Oracle Clob into a String"
  (with-open [rdr (java.io.BufferedReader. (.getCharacterStream clob))]
    (apply str (line-seq rdr))))

(query mydb ["select documentation from station"]
       {:row-fn (comp clob-to-string :documentation)
        :result-set-fn #(doseq [doc %] (println doc))})

Or if you just wanted to expand the documentation column in the rows:

(let [rs (query mydb ["select * from station"]
                {:row-fn #(update % :documentation clob-to-string)})]
  ;; process the result set
  (doseq [row rs] (println (:documentation row))))