Transform Oracle CLOB to string

[Tom Wigington]

Hello,

I'm having problems with getting sqlKorma to transform an Oracle Clob into a String. I've read the old thread (https://groups.google.com/d/msg/sqlkorma/SoMAteoUpug/iBobstw69sYJ), but am getting an error. "SQLRecoverableException Closed Connection  oracle.sql.CLOB.getDBAccess (CLOB.java:1714)"

Here is some code to show what I'm doing. The table some_data is "create table some_data (id integer, clob_data clob)" which has clobs of various lengths.

(defn add? [m k func newkey]
  (if (m k)
    (conj m [newkey (func (m k))])
    m))

(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))))

(defentity some_data
           (entity-fields :id :clob_data)
           (transform #(-> %
                           (add? :clob_data clob-to-string :clob_data_str))))

(select some_data)
; SQLRecoverableException Closed Connection  oracle.sql.CLOB.getDBAccess (CLOB.java:1714)

(transaction (select some_data))

; SQLRecoverableException Closed Connection  oracle.sql.CLOB.getDBAccess (CLOB.java:1714)

I am able to read clobs with clojure.java.jdbc

(j/query db/orcldap ["select id, clob_data from some_data"]
         :row-fn #(add? % :clob_data clob-to-string :clob_data_str))

This is with Clojure 1.6, korma 0.3.2, java.jdbc 0.3.6, Oracle 11.2.0.3.0, and jdbc 12.1.0.1.0.

It seems that the transaction the clobs are read with has closed before the transform function is called.

I'm hoping I'm just doing something wrong and can get Korma to work as I really like using it in the REPL.

Thanks.

[Immo Heikkinen]

The reason is that Korma applies transform fn after the connection is closed. You can work around it by wrapping the call with `with-db` or `transaction` and walking through the result forcing side effects:

(with-db your-db 

  (doall (select some_data)))

--
You received this message because you are subscribed to the Google Groups "Korma" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlkorma+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Tom Wigington]

In my original post I showed that wrapping it in 'transaction' resulted in the same error. I'll give 'with-db' a shot when I get back to this problem.

That said. Would it make sense to enhance Korma to either pass transformers to java.jdbc using :row-fn or create another classification for functions that should be passed as :row-fn?

Thanks for the reply.

[Immo Heikkinen]

The key here is to use `doall` so that result seq is realised before connection is closed. Works with both `with-transaction` and `with-db`.

Thanks for the suggestion, it would make sense to investigate if Korma could make use of java.jdbc's row-fn for the transform fs. I'm guessing that it wasn't available in the java.jdbc version Korma was initially build on.