clojure: how to convert jdbc4array into clojure's seq?

Question

I tried to query data from database with jdbc. The problem is some column is array type.

;get that particular column
(def jdbc-array (with-connection *db*                                                                    
                  (with-query-results rs ["select * from refgene limit 5"]                                        
                    (:exonstarts (first rs)))))

;Check if it has a value
(print jdbc-array)
;#<Jdbc4Array {67075873,67078739,67085754,67100417,67109640,67113051,67129424,67131499,67143471,67162932}>nil

;check class
(class jdbc-array)
;org.postgresql.jdbc4.Jdbc4Array

How to convert this array to seq/vector in clojure ? I tried (seq jdbc-array) and (seq (.getArray jdbc-array) but both doesn't work...

Answer 1

If the with-connection option seems clunky to you (it does to me), you can extend the IResultSetReadColumn protocol to convert Jdbc4Array objects into regular or vectors:

Here's one way to do that:

(extend-protocol clojure.java.jdbc/IResultSetReadColumn
  org.postgresql.jdbc4.Jdbc4Array
  (result-set-read-column [pgobj metadata i]
    (vec (.getArray pgobj))))

this will convert all array types into vectors when reading

this approach can also help with the JSON datatype as in this example

Answer 2

Ok, I got it. I need to called getArray before clojure close the connection, or it'll give a nil.

Not sure why... My guess is clojure's laziness.

;work
(with-connection *db*
  (with-query-results rs ["select * from refgene limit ?" 5]
    (seq (.getArray (:exonends (first rs))))))