Clojure read Blob from database

Question

I need to read bytes from this Blob. I'm trying the following but I'm getting this exception: oracle.sql.BLOB cannot be cast to [B

(defn select-test2[]
  (clojure.contrib.sql/with-connection db
    (with-query-results res ["SELECT my_blob from some_table"] (doall res))))

(defn obj [byte-buffer]
  (if-not (nil? byte-buffer)
    (with-open [object-in (ObjectInputStream.
                            (ByteArrayInputStream. byte-buffer))]
      (.readObject object-in))))

(obj (:my_blob (first (select-test2))))

Answer 1

[B is the "class" of a byte array:

user=> (type (byte-array 0))
[B

So there's a place in your code that is expecting a byte array, but it's being given an oracle.sql.Blob instance. My bet is that :my_blob is giving you a Blob; when you pass byte-buffer (which is the Blob) to the ByteArrayInputStream constructor, you get the exception.

Look up the javadocs for oracle.sql.Blob to see how to extract a byte array or input stream from it.

Answer 2

(ns test-jdbc
  (:use clojure.contrib.sql))

; read clob with BufferedReader
(defn clob-to-string [clob]
  (with-open [rdr (java.io.BufferedReader. (.getCharacterStream clob))]
    (apply str (line-seq rdr))))

; read first CLOB 
(defn get-meta-by-id [db id]
  "read META_COL from MY_TABLE by given id"
  (with-connection db
    (transaction ;; need to be inside a transaction
      (with-query-results rs 
        ["select META_COL from MY_TABLE where ID = ? " id]
        (clob-to-string (:meta-col (first rs))) ))))