sql

All things SQL and JDBC...
dominicm 2020-09-15T09:28:35.350200Z

Has anyone used honeysql for multiple queries? I'm quite performance sensitive in my inserts, but I've got to issue multiple and I'm not interested in the return values at all. I haven't tried it yet, but I was thinking that sending all 4 inserts in a single execution would reduce round-trip times (ideally pg would parallelize for me, but I dunno how that would work).

dominicm 2020-09-15T09:42:03.350300Z

Interestingly, this almost works, but lacks a semi-colon:

(sql/format
    [{:insert-into :foo :values [{:a 1}]} {:insert-into :bar :values [{:b 1}]}])

2020-09-15T11:10:02.350500Z

What about using a CTE?

dominicm 2020-09-15T12:22:04.350700Z

I don't know what that abbreviation is short for I'm afraid!

seancorfield 2020-09-15T16:37:28.350900Z

Common Table Expression https://www.geeksforgeeks.org/cte-in-sql/

seancorfield 2020-09-15T16:38:14.351300Z

Feel free to swing by the #honeysql channel and discussion your needs. I'm taking on board a lot of input right now for HoneySQL 2.0.

Test This 2020-09-15T19:18:03.352600Z

Hello. I posted the following message on Reddit and @seancorfield suggested that I seek help here I connected to a database I have using https://cljdoc.org/d/seancorfield/next.jdbc/1.1.582. From the awesome docs for that library, I copied and pasted the code for working with jsonb/json datatypes in Postgres. The code can be found at this link: https://cljdoc.org/d/seancorfield/next.jdbc/1.1.582/doc/getting-started/tips-tricks#working-with-json-and-jsonb This works well when the jsonb column has data or is postgres null. However, I get `Execution error (NullPointerException)` when the data is 'null' in the jsonb column (note 'null' rather than missing in the postgres db) . How can I modify the above code to deal with 'null' strings? I have figured that the exception occurs in the code shown below: (I have added the try catch by myself to catch the exception). Essentially, it appears that (with-meta (<-json value) {:pgtype type}) is unable to deal with when a postgres jsonb column if it contains a json null or string.

(defn <-pgobject
  "Transform PGobject containing `json` or `jsonb` value to Clojure
  data."
  [^org.postgresql.util.PGobject v]
  (let [type  (.getType v)
        value (.getValue v)]
    (if (#{"jsonb" "json"} type)
      (try ;; I added the try block to deal with strings and null value for now.
        (with-meta (<-json value) {:pgtype type})
        (catch Exception e (str "caught exception: " (.getMessage e))))
      value)))
The error that I get without the try catch block is Execution error (NullPointerException) at exploredb.dbconn/<-pgobject (dbconn.clj:44).` null . Is there a better solution than the try catch block? Thank you.

seancorfield 2020-09-15T19:33:49.353400Z

Which is line 44 in your code @curiouslearn?

seancorfield 2020-09-15T19:34:54.354400Z

I would assume it's (&lt;-json value) so it sounds like the &lt;-json function that someone contributed to the docs doesn't allow for value to be nil?

seancorfield 2020-09-15T19:35:38.355Z

And, indeed, it's just calling jsonista's function on it so, yeah, that will not accept nil

seancorfield 2020-09-15T19:36:19.355800Z

So you'd want this instead:

(defn &lt;-pgobject
  "Transform PGobject containing `json` or `jsonb` value to Clojure
  data."
  [^org.postgresql.util.PGobject v]
  (let [type  (.getType v)
        value (.getValue v)]
    (if (#{"jsonb" "json"} type)
      (when value
        (with-meta (&lt;-json value) {:pgtype type}))
      value)))

seancorfield 2020-09-15T19:37:33.356800Z

I suspect that whoever contributed that part of the docs makes sure they don't put JSON null in their DB, they put SQL null instead... Let me see who that was so I can ask them here...

seancorfield 2020-09-15T19:39:27.357700Z

That was @valtteri who is not around at the moment I suspect but can no doubt comment on this when they are...

seancorfield 2020-09-15T19:39:54.358300Z

I can update the example in the docs to handle JSON null but I'd rather wait for their input first...

seancorfield 2020-09-15T19:42:42.359800Z

Yeah, in fact, looking at the -&gt;pgobject code, that would never get invoked for nil being stored in the DB so the expectation is that you would never get a JSON null in the column: you'd either get a SQL null (which the code handles, as you observed) or you'd get a (non-null) JSON expression.

seancorfield 2020-09-15T19:53:02.360400Z

And BTW @curiouslearn "Welcome to Slack!" Glad you figured out the invite/sign-up process!

Test This 2020-09-15T22:12:15.363900Z

Thank you @seancorfield. Appreciate your help with the issue and the link for getting on slack. As you said, I hope @valtteri responds. Since Postgres jsonb column allows for JSON null and for JSON strings I suppose it would make sense to allow for those possibilities. What do you think? I am super new to clojure, and don't know Java, so I don't know where to start to make that change. Just to clarify: with JSON string, I get a different exception (not the NULLPointerException). I don't remember exactly, but it is an exception because of JSON string.

seancorfield 2020-09-15T22:18:33.368400Z

Much depends on where your JSON input data is coming from. The docs that Valtteri provided assume you have Clojure data and you control the process of inserting it into the database. If that's the case, you'd never convert nil to JSON null, you'd simply insert nil -- which would produce a column with a SQL NULL value, and that will read back correctly using the example code because it would produce nil and not try to decode a PGobject and therefore not try to convert it from JSON (because it wouldn't be JSON).

seancorfield 2020-09-15T22:20:33.370200Z

If the database is being populated by someone else, and they're just blindly storing JSON values (or even blindly converting incoming nil/`null` values to JSON null), then yes you need the when clause I added to the example code so that you get back nil instead of blowing up trying to convert null from JSON.

seancorfield 2020-09-15T22:21:18.370500Z

If in doubt, add that when value guard 🙂

2020-09-15T23:02:26.370600Z

Sorry @dominicm, should have written it out! Now that I'm at a laptop again, using a common table expression your code could look something like:

(sql/format
  {:with [:foo {:insert-into :foo :values [{:a 1}]}
          :bar {:insert-into :bar :values [{:b 1}]}]
   :select [1]})