search

seancorfield / next-jdbc

A modern low-level Clojure wrapper for JDBC-based access to databases.
https://cljdoc.org/d/com.github.seancorfield/next.jdbc/
Eclipse Public License 1.0
755 stars 90 forks source link

Add support for returning data from execute-batch! (PostgreSQL) #198

Closed holyjak closed 2 years ago

holyjak commented 2 years ago

As described here https://stackoverflow.com/a/23691810/204205 , in PostgreSQL I can make an INSERT .... RETURNING * that will insert data and return them. That works just fine with jdbc/execute! and the data is returned. But in the case of execute-batch! the data is ignored and update counts are returned instead (which, in this case, will be all 0).

Thus of Pg it would be preferable if the logic in execute-batch! followed more closely that in execute!, namely checking whether (.getResultSet ps) returns a result set and returning its data, if it does:

(if-let [rs (stmt->result-set stmt opts)]
          (datafiable-result-set rs this opts)
          [{:next.jdbc/update-count (.getUpdateCount stmt)}])

An alternative is to either do this manually in client code or issue a select after the insert/update.

I would be happy to work on a PR for this.

seancorfield commented 2 years ago

Per the docstring for execute-batch!:

By default, returns a Clojure vector of update counts. Some databases allow batch statements to also return generated keys and you can attempt that if you ensure the PreparedStatement is created with :return-keys true and you also provide :return-generated-keys true in the options passed to execute-batch!. Some databases will only return one generated key per batch, some return all the generated keys, some will throw an exception. If that is supported, execute-batch! will return a vector of hash maps containing the generated keys as fully-realized, datafiable result sets, whose content is database-dependent.

Have you tried the :return-generated-keys option?

holyjak commented 2 years ago

Awesome, :return-keys true, :return-generated-keys true works! I have not thought about those at all because I have not realized that the underlying mechanism is just a result set and thus they would work in my case even though it has little to do with (generated) keys. Thank you!

seancorfield commented 2 years ago

JDBC is weird like that: lots of things are "just result sets" -- including database metadata etc.