Fork me on GitHub
#sql
<
2019-07-29
>
dcj00:07:45

I've been experimenting wtih jdbc.next, specifically how to automatically convert between a Postgres timestamptz/java.sql.Timestamp and a java.time.ZonedDateTime. The following seems to work, but I'm not convinced this is a good implementation, specifically, I don't understand the impact/relevance of https://jdbc.postgresql.org/documentation/head/java8-date-time.html

[next.jdbc :as jdbc]
   [next.jdbc.connection :as connection]
   [next.jdbc.result-set :as result-set]
   [next.jdbc.prepare    :as prepare])
  (:import com.zaxxer.hikari.HikariDataSource
           java.sql.PreparedStatement
           java.sql.Timestamp)

(extend-protocol result-set/ReadableColumn
  java.sql.Timestamp
  (read-column-by-label ^java.time.ZonedDateTime [^java.sql.Timestamp v _]
    (time/zoned-date-time (time/instant v) "UTC"))
  (read-column-by-index ^java.time.ZonedDateTime [^java.sql.Timestamp v _2 _3]
    (time/zoned-date-time (time/instant v) "UTC")))

(extend-protocol prepare/SettableParameter
  java.time.ZonedDateTime
  (set-parameter [^java.time.Instant v ^PreparedStatement ps ^long i]
    (.setTimestamp ps i (java.sql.Timestamp/from (time/instant v)))))
Any comments/critiques/feedback welcome!

👏 8
seancorfield02:07:41

@dcj I think that's fine -- you traffic in ZDT in your app, and timestamp in the DB.

seancorfield02:07:28

You can choose whatever canonical type you want to use in your app and go in and out the DB based on that.

dcj02:07:13

Now I'm thinking I want to re-implement much of https://github.com/atsman/clj-postgresql/blob/master/src/clj_postgresql/types.clj for next.jdbc. Mostly for the JSON/JSONB stuff. For the ReadableColumn direction, I figured out how to check for a org.postgresql.util.PGobject, and then .getType out of that, if jsonb/json, convert the JSON to Clojure. I'm not sure how to implement the SettableParameter direction, how do I figure out the Postgres type of the parameter? Here is how he did it for a Clojure map and json/jsonb:

(defmethod map->parameter :json
  [m _]
  (to-pg-json m :json))

(defmethod map->parameter :jsonb
  [m _]
  (to-pg-json m :jsonb))

(extend-protocol jdbc/ISQLParameter
  clojure.lang.IPersistentMap
  (set-parameter [m ^PreparedStatement s ^long i]
    (let [meta (.getParameterMetaData s)]
      (if-let [type-name (keyword (.getParameterTypeName meta i))]
        (.setObject s i (map->parameter m type-name))
        (.setObject s i m)))))
How would I do the equivalent of the getParameterMetaData and getParameterTypeName in next.jdbc?

seancorfield03:07:04

So the SettableParameter protocol has set-parameter which takes the value, the (prepared) statement, and the index, just like the above for clojure.java.jdbc's ISQLParameter.

seancorfield03:07:22

@dcj So it's basically exactly the same with next.jdbc as it was with clojure.java.jdbc.

dcj03:07:33

@seancorfield so will

(let [meta (.getParameterMetaData s)]
      (if-let [type-name (keyword (.getParameterTypeName meta i))]
just work?

dcj03:07:18

Specifically the .getParameterMetaData and .getParameterTypeName ?

seancorfield04:07:42

@dcj They are methods on PreparedStatement and the metadata and both clojure.java.jdbc and next.jdbc make that available in set-parameter.

chetchan06:07:17

Hi folks, I have a HugSQL question, would be great if someone could point me in the right direction. Basically I am getting in data from my select statement like this:

{:remarks nil, :timeprocessing "1994-01-01 00:00:00.000", :devicetype 3M, :staffcode 0M, :timedeletion "1994-01-01 00:00:00.000", :occurred 1M, :syseventdesig "Ticket not valid", :time "2019-01-14 09:45:22.340", :syseventno 490M, :devicedesig "201 Suncorp EXIT RHS", :devicenoprocessing 0M, :component nil, :carparkabbr "MAIN", :deviceabbr "201 SUNEXR", :week_id 201903M, :carparkdesig "Public Car Park", :dss_update_time #inst "2019-01-14T15:48:46.000000000-00:00", :loaded_new_yn "N", :operatorfirstname nil, :quantity 1M, :systemeventregno 5209M, :pdi_batch_id 697410M, :carparkno 0M, :deviceno 40M, :deleted_yn "N", :centre_no "0056000", :carpark_no "0056001", :operatorsurname "Unattended", :operatornoprocessing 0M}
I am getting several tens of thousands of such messages as a list of maps(each record being a clj map from select query(HUGSQL) that selects rows off of my table. This stalls my application and even crashes it sometimes(JVM memory full?). Is there a way for me to read it in chunks within the code without having to modify my select statement and put each chunk in a separate CSV file for eg. to further load it into my choice destination.

seancorfield06:07:33

@chetan.falcon Feels like I already answered this in another channel, right?

seancorfield06:07:06

You won't be able to do it with HugSQL tho'. You'll need to work directly with clojure.java.jdbc's reducible-query or next.jdbc's plan.

chetchan06:07:15

Sorry I thought you wanted me to post it here. Yes you did answer it there.

seancorfield06:07:35

Which DB are you using?

chetchan06:07:59

OK thank you... So I ll have to get the results in which uses hugsql ATM. And use those methods to reduce it?

chetchan06:07:03

I use Oracle DB

seancorfield06:07:15

Hmm, OK, I'm not sure of the magic incantations that Oracle needs. Often, you need to ensure the connection you use is set to not auto-commit, and you need to specify :fetch-size with some particular value.

seancorfield06:07:53

But the basic approach is to reduce over the result set, and have it stream results from the DB instead of trying to read it all into memory at once.

seancorfield06:07:20

HugSQL does not help you here.

seancorfield06:07:45

You need to use the JDBC libraries directly (because HugSQL does not use the reducible result set approach).

chetchan06:07:01

Ok, got it. Just looked it up now

(jdbc/query db-spec ["SELECT * FROM fruit WHERE cost < ?" 50]
            {:result-set-fn (fn [rs]
                              (reduce (fn [total row-map]
                                        (+ total (:cost row-map)))
                              0 rs))})

seancorfield06:07:13

No, you can't use query either.

chetchan06:07:22

So what you say is I need to do all the querying bit using this approach and not use HugSql at all to query also?

seancorfield06:07:53

If your data set is too big to fit in memory, you need to use either clojure.java.jdbc's reducible-query or next.jdbc's plan.

chetchan06:07:27

Sorry pasted a wrong example. Meant to paste this

(jdbc/reducible-query db-spec
                              ["SELECT * FROM fruit WHERE cost < ?" 50]
                              {:raw? true}))

seancorfield06:07:50

Cool. Good luck!