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
精彩评论