Search code examples
sqlclojuresequence

How to call next() on a resultset in clojure.contrib.sql?

Originally I was going to ask why I was having problems calling (seq) on a result set as a test for emptiness, but a bit of research showed that it's apparently because the jdbc cursor hasn't moved anywhere. Fine and dandy. Is there a way to call next() on the resultset if I don't know its name? I can bind it to a symbol in clojure, but I haven't been able to figure out how to call the method from there.

edit: in case it's not clear, I'm referring to the java resultSet method next(), not the clojure (next)

edit#2 here's a code snippet:

(defn user-exists? [email]
  (with-connection db
   (with-query-results res ["SELECT guid from users WHERE email=?" email]
    (.next res)
    (seq res))))

(Thanks for the help on .next, btw...haven't done much java interop yet)

Still, though, using (seq) throws a NullPointerException if the query didn't return anything. I'm wondering if there's a cleaner, idiomatic way to do this?

Solution

  • res will be bound to a Clojure sequence based on the ResultSet where each item is a map of a result record with output column names as keywords in keys. You can't get to the underlying actual ResultSet object.

    Typically you don't call next at all. To be idiomatic Clojure, you would utilize the vast library of functions that operate on sequences (map, filter, reduce, etc) to produce your output. Here you might say something like:

    (with-query-results res ["SELECT guid from users WHERE email=?" email]
  (map :guid res))

    which would apply :guid as a function over the res seqeuence and retrieve the column value of guid in each record, returning you a new sequence of all the guids. You could then wrap that map in a filter or whatever else you needed.

    In this case, seq should be what you want but I think the abstraction is leaking a bit. When I tried it I got java.sql.SQLRecoverableException: Closed Resultset: next which implies to me the ResultSet is being closed too early in the abstraction. However, I found empty? worked ok for this use case. 

    (defn user-exists? [email]
  (with-connection db
   (with-query-results res ["SELECT guid from users WHERE email=?" email]
    (.next res)
    (not (empty? res)))))

    This seems like a bug in clojure.core/resultset-seq to me and I reported it here as CLJ-676.