Clojure read Oracle Blob

Question

I need to read an geometry as a WKB string in Clojure, for that I try to use clojure/java.jdbc

(require '[clojure.java.jdbc :as j])    
(->> (j/query db "select SDO_UTIL.TO_WKBGEOMETRY(geometry) wkb from t where idf = 1") 
  (map #(-> % :wkb .getBinaryStream .readAllBytes)))

unfortunately I got:

Exception thrown: java.sql.SQLRecoverableException (Closed Connection)

getDBAccess - (BLOB.java:1122)
getBinaryStream - (BLOB.java:265)
invoke0 - (NativeMethodAccessorImpl.java:-2)
invoke - (NativeMethodAccessorImpl.java:62)
invoke - (DelegatingMethodAccessorImpl.java:43)
invoke - (Method.java:498)
invokeMatchingMethod - (Reflector.java:93)
invokeNoArgInstanceMember - (Reflector.java:313)
eval16213/fn - user - (form-init2938139155321903837.clj:3)
map/fn - clojure.core - (core.clj:2646)

so I could read the number bytes in blob using oracle.sql.Blob/length

(->> (j/query db "select SDO_UTIL.TO_WKBGEOMETRY(geometry) wkb t where idf = 1") 
(map #(-> % :wkb .length))
)
(42241)

Possible duplicate of https://stackoverflow.com/questions/6785340/oracle-db-java-sql-sqlexception-closed-connection — Venantius, Apr 18 '18 at 15:14
I don't think that's a duplicate. This looks like it is about laziness: trying to lazily map over the results of a database query, but the connection is closed before the map is performed. — amalloy, Apr 18 '18 at 21:45

Arthur Ulfeldt · Answer 1 · 2018-04-19T19:16:38.177

this looks like what I call "the lazy bug".

the connection is opened and the query is sent then it returns a sequence, containing some code, that will fetch the responses from the DB when the next part of the program reads from the sequence. in this case the DB connection is closed before that happens.

Fortunatly it's easy to fix, just put a doall around the map.

(require '[clojure.java.jdbc :as j])    
(->> (j/query db 
              "select SDO_UTIL.TO_WKBGEOMETRY(geometry)
               wkb t where idf = 1") 
  (map #(-> % :wkb .getBinaryStream .readAllBytes))
  doall)

score 0 · Answer 2 · answered Apr 26 '18 at 08:05

this code works for me, suggested by @Arthur doall hasn't helped in my case, what I've added is a transaction:

(require '[clojure.java.jdbc :as j])
(j/with-db-transaction [t-con db ] 
 (->> (j/query t-con  
          "select SDO_UTIL.TO_WKBGEOMETRY(geometry)
           wkb from  t where idf = 1  ")                                          
    (map #(-> % :wkb .getBinaryStream))
    (map #(java.util.Scanner. %))
    (map #(.next %))
    doall
   )
)

Clojure read Oracle Blob

2 Answers2