Clojurians Log v2

Q: Embedded PostgreSQL -- https://github.com/opentable/otj-pg-embedded -- I'm trying to find documentation that indicates what features are not available in this. Anyone have suggestions/pointers?

OK, I think I finally have my answer! Embedded PostgreSQL is equivalent to 10.11 and stored procs didn't arrive until 11.x so it doesn't support them.

I switched to a fork of OpenTable's version that has support for later versions of the database (currently testing with 12.2.0!). This is all about stored procedure support in next.jdbc and multiple result sets.

And this has now allowed me to test next.jdbc against PostgreSQL 12.2.0 on macOS, Windows, and Linux. Yay!

good to know!

Would it be worthwhile to explore TestContainers? I use it in my Kotlin Projects. It uses Docker to pull down an instance of Postgresql, launches it, then you can run whatever (tests) against it?

It works really well on a CI pipeline, and you're guaranteed to be using officially released docker versions of popular databases

https://www.testcontainers.org/

@dharrigan I use a Docker container for testing SQL Server and Percona (MYSQL) but I don't have that facility on all of my machines (for complicated reasons) and most folks seem to use PostgreSQL so it's important to test that on every platform.

absolutely, but test containers also runs on Windows too (and Mac)

It was a suggestion 🙂 Does the embedded postgresql add to the size of the repo?

Nope it is just a test dependency.

And this is for my local development convenience so I can test against PG when I'm traveling / offline, with needing to actually install it and set it up.

But I will look at test containers (it was just the middle of the night when I responded to your previous message 🙃

Just checked @dharrigan -- test containers requires Docker to run so it won't be suitable for me, for the same reasons Docker already isn't: I can't run it on some of my machines.

(but thanks for the pointer -- I'll read up on it in more detail another time)

No problemo 🙂

OK, I have multiple result sets coming back from stored procedures in HSQLDB, MySQL, and MS SQL Server. I cannot figure out how to do it in PostgreSQL 😞 PG accepts the CREATE PROCEDURE statement with two SELECT statements in it, but only returns an update count (of zero). My Bing results for this seem to suggest that, as of late 2018 at least, the PG team were not planning to add dynamic result sets (which I've had to use for HSQLDB) but considered returning (a fix number of) multiple result sets to be a "possible future feature".

For MySQL/SQL Server, a simple body with two SELECT statements is enough.

For HSQLDB, I had to DECLARE result1 CURSOR WITH RETURN FOR SELECT ... for each query (`result1`, result1, etc) and then end the procedure with OPEN result1; OPEN result2; -- does PostgreSQL require something similar?

(feel free to pull branch issue-117 and have a play with it -- right now, only execute! on a DataSource supports this, via :multi-rs true)

My current thinking on this is to return a flat continuous result set data structure with {:next.jdbc/result-set N} as a delimiter "record" showing the end of each result set (omitted at the end so they are interleaved, technically speaking).

This followed from the fact that you can get a mix of update counts and result sets interleaved so having an explicit, detectable row between each sequence of result set rows seemed reasonable. Another possibility is to return a full sequence of results which might be easier to deal with. I'd be happy to support multiple formats if folks think they might be useful. Since it's an opt-in feature, multiple result formats can be added over time without impacting existing code (`:multi-rs` would then have a set of possible keyword option values).

Hi everyone, it is possible to get columns as kebab case in plan!? I did pass an opts parameter with a :builder-fn that converts to kebab-case , if I call (keys row) in the reducing function and print it, it is shown in kebab-case, but when trying to accumulate the value into a collection using kebab-case lookup doesn't work.

(def plan-opts {:builder-fn as-kebab-maps})

(defn plan! [ctx stmt]
  (let [q (sql/format stmt)]
    (jdbc/plan (get-connection ctx) q plan-opts)))

(reduce (fn [acc cur]
                          (prn cur) ;; => {:product-attribute/id-product-attribute "a"}
                          (conj acc (:product-attribute/id-product-attribute cur))
                          ) [] (plan! mysql-ctx {:select [:*]
                                                 :from [:product-attribute]}))

=> [nil]

@vachichng I think you're misunderstanding what the docs say about plan. If you call keys on a row inside plan you are going to fully realize that row -- which will use the result set builder (specifically the row builder portion of it). The whole point of plan is to avoid realizing rows, so you should work with native column labels inside the reduction.

That's why it emphasizes that you can use raw column labels to access individual column values inside the reducing function -- or qualified keywords (but the qualifier is deliberately ignored).

The above code could also be simplified to (into [] (map :id_product_attribute) (plan! ...)) I believe.

@seancorfield oh, thank you! yeah, I forgot that it's not realized so the builder-fn is not called until realization

Also, calling prn will realize a row which, again, you want to avoid.

Yeah, there are actually tests for plan that deliberately pass :builder-fn nil to ensure that the builder isn't used 🙂

yeah, it was for debugging

thanks Sean!

Feel free to open an issue (or PR) with suggestions to improvement the documentation if you think it can be clarified.

(I just realized that the new InspectableMapifiedResultSet protocol documentation doesn't make it clear which functions cause realization -- I need to update that! https://cljdoc.org/d/seancorfield/next.jdbc/1.0.462/api/next.jdbc.result-set#InspectableMapifiedResultSet )