How you can place a blob in database while using clojure.contrib.sql?
I have attempted the next reading through from the file but I am getting this exception:
SQLException: Message: Invalid column type SQLState: 99999 Error Code: 17004 java.lang.Exception: transaction folded 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")]) ))
I could solve this by transforming the FileInputStream right 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"))]) ))
Theoretically, you should use the clojure.contrib.sql/place-* techniques to place a blob, passing the blob as whether byte array, java.sql.Blob or perhaps a java.io.InputStream object. Used, it's driver-dependent.
For a lot of JDBC implementations, the suggestions above act as expected, but when you are using sqlitejdbc .5.6 from Clojars, you will find your blob pushed to some string via toString(). All of the clojure.contrib.sql/place-* instructions are released via clojure.contrib.sql/do-prepared, which calls setObject() on the java.sql.PreparedStatement. The sqlitejdbc implementation doesn't handle setObject() for the blob data types, but defaults to coercing these to a string. Here is a work-around that allows you to definitely 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]))))
In my opinion it's likewise way you'd place every other value: play one of
(insert-values :mytable [:id :blobcolumn] [42 blob])