5

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"]
Verbery
  • 157
  • 7

1 Answers1

4

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.

Svante
  • 50,694
  • 11
  • 78
  • 122
  • 2
    I'm promoting the term "lazybug" for this class of bugs in clojure. – Arthur Ulfeldt Oct 23 '17 at 17:18
  • 4
    I 100% agree with this answer, and would add that the *reason* `with-db-transaction` makes use of dynamic scope is that you can't/shouldn't be lazy with database connections. It costs real, exhaustible resources to keep one open. You don't get the luxury of laziness in that context. Decide what you want to do with the results, and do it all inside of that `with-` scope. – amalloy Oct 23 '17 at 18:27