:result-set-fn in clojure jdbc return error "The result set is closed." Why?

Question

Often I need load huge data size from database server. Sometimes it have million rows and more. So I try to download data lazily. That what I want to do: I want get a lazy-sequence and pull data partial from server, i.e if row count is more than 500, I want primarily get with help of that lazy-sequence first 500 elements, then by another request i want receive next 500 elements and so on until i recieve all data from server.

But I have a problem. Clojure jdbc realize entire lazy-sequence, but I want obtain data from it partially. I research that question and find good reply about similar problem: clojure.java.jdbc lazy query

I took this example and wrote this:

(defn get_data
   [arg1 arg2]
   (let [full-db-spec (get ...)
       sql_query (get ...)
       row-n (atom 0)
       prepared-statement (-> full-db-spec
                               (jdbc/get-connection)
                               (jdbc/prepare-statement sql_query {:fetch-size 3}))]
     (jdbc/with-db-transaction [tx full-db-spec]
       (jdbc/query full-db-spec [prepared-statement arg1 arg2]
           {:fetch-size 3
            :row-fn (fn [r] (do (prn "r" @row-n) (swap! row-n inc) r))
            :result-set-fn identity}))))

Here I want to get a lazy-sequence to further extract data partially from this lazy-sequence. But when :result-set-fn is contain identity or (take 500 ...) the code return error: The result set is closed. Why? But when I change :result-set-fn to first or doall or last It works fine but it realize full lazy-sequence!

I use:

• ms sql [com.microsoft.sqlserver/mssql-jdbc "6.3.3.jre8-preview"] (yet I test it on postgresql [org.postgresql/postgresql "9.4.1212.jre7"]. Same result)
• [org.clojure/java.jdbc "0.7.3"]

Answer 1

That lazy sequence is reading values from your connection, but the connection is closed outside of the with-db-transaction scope. You need to realize/do further processing inside of that scope.