Clojure how to insert a blob in database?

How to insert a blob in database using the clojure.contrib.sql?

I've tried the following reading from a file but I'm getting this exception:

SQLExce开发者_如何转开发ption: Message: Invalid column type SQLState: 99999 Error Code: 17004 java.lang.Exception: transaction rolled back: Invalid column type (repl-1:125)

(clojure.contrib.sql/with-connection
   db
   (clojure.contrib.sql/transaction
    (clojure.contrib.sql/insert-values :test_blob [:blob_id :a_blob] [3   (FileInputStream. "c:/somefile.xls")]) ))

Thanks.

---

I was able to solve this by converting the FileInputStream into a ByteArray.

(clojure.contrib.sql/with-connection 
   db 
   (clojure.contrib.sql/transaction 
    (clojure.contrib.sql/insert-values :test_blob [:blob_id :a_blob] [3   (to-byte-array(FileInputStream. "c:/somefile.xls"))]) )) 

---

In theory, you can use any of the clojure.contrib.sql/insert-* methods to insert a blob, passing the blob as either a byte array, java.sql.Blob or a java.io.InputStream object. In practice, it is driver-dependent.

For many JDBC implementations, all of the above work as expected, but if you're using sqlitejdbc 0.5.6 from Clojars, you'll find your blob coerced to a string via toString(). All the clojure.contrib.sql/insert-* commands are issued via clojure.contrib.sql/do-prepared, which calls setObject() on a java.sql.PreparedStatement. The sqlitejdbc implementation does not handle setObject() for any of the blob data types, but defaults to coercing them to a string. Here's a work-around that enables you to store blobs in SQLite:

  (use '[clojure.contrib.io :only (input-stream to-byte-array)])
  (require '[clojure.contrib.sql :as sql])

  (defn my-do-prepared
   "Executes an (optionally parameterized) SQL prepared statement on the
   open database connection. Each param-group is a seq of values for all of
   the parameters. This is a modified version of clojure.contrib.sql/do-prepared
   with special handling of byte arrays."
   [sql & param-groups]
   (with-open [stmt (.prepareStatement (sql/connection) sql)]
     (doseq [param-group param-groups]
       (doseq [[index value] (map vector (iterate inc 1) param-group)]
         (if (= (class value) (class (to-byte-array "")))
           (.setBytes stmt index value)
           (.setObject stmt index value)))
       (.addBatch stmt))
     (sql/transaction
      (seq (.executeBatch stmt)))))

 (defn my-load-blob [filename]
   (let [blob (to-byte-array (input-stream filename))]
     (sql/with-connection db
           (my-do-prepared "insert into mytable (blob_column) values (?)" [blob]))))

---

A more recent reply to this thread with the code to read the data as well :

(ns com.my-items.polypheme.db.demo
  (:require
   [clojure.java.io :as io]
   [clojure.java.jdbc :as sql]))

(def db {:dbtype "postgresql"
         :dbname "my_db_name"
         :host "my_server"
         :user "user",
         :password "user"})

(defn file->bytes [file]
  (with-open [xin (io/input-stream file)
              xout (java.io.ByteArrayOutputStream.)]
    (io/copy xin xout)
    (.toByteArray xout)))

(defn insert-image [db-config file]
  (let [bytes (file->bytes file)]
    (sql/with-db-transaction [conn db-config]
      (sql/insert! conn :image {:name (.getName file) :data bytes}))))

(insert-image db (io/file "resources" "my_nice_picture.JPG"))

;;read data
(defn read-image [db-config id]
  (-> (sql/get-by-id db-config :image id)
      :data
      (#(new java.io.ByteArrayInputStream %))))

---

I believe it's just the same way you'd insert any other value: use one of insert-records, insert-rows or insert-values. E.g.:

(insert-values :mytable [:id :blobcolumn] [42 blob])

More examples: http://github.com/richhickey/clojure-contrib/blob/master/src/test/clojure/clojure/contrib/test_sql.clj