Fork me on GitHub
#sql
<
2020-02-19
>
orestis13:02:19

I have a Postgres query that should return a java.time.LocalDate since my column is DATE: https://jdbc.postgresql.org/documentation/head/8-date-time.html -- but instead I'm seeing java.sql.Date as the class. I wonder if I have to do something special with next.jdbc?

orestis13:02:13

(-> (jdbc/execute-one! DS ["select current_timestamp::date"])
      :current_timestamp
      class)
;=> java.sql.Date

orestis13:02:30

Doesn't help that Clojure prints this as an #inst "..."

orestis13:02:00

Ah, a java.sql.Date is actually a java.util.Date.

orestis13:02:09

Anyway, doing this:

(extend-protocol next.jdbc.result-set/ReadableColumn
  java.sql.Date
  (read-column-by-label [^java.sql.Date v label]
    (.toLocalDate v))
  (read-column-by-index [^java.sql.Date v rs-meta idx]
    (.toLocalDate v)))

orestis13:02:32

does the trick. But I'm confused on the whole casting of values that JDBC / next.jdbc does.

seancorfield18:02:45

@orestis next.jdbc doesn't do any casting by default -- it relies entirely on the JDBC driver. Whatever the driver gives you back (as an Object) is what you get.

seancorfield18:02:21

If you require the (optional) next.jdbc.date-time namespace, then you get a bunch of coercions around date/time -- particularly useful for PostgreSQL since it sometimes doesn't do certain basic coercions. But bear in mind, loading those protocol extensions means you "buy in" for your whole application.

orestis18:02:47

I see that JDBC has versions, is that a historical artifact and everyone is at the latest?

seancorfield18:02:11

(and that ns is for inbound -- Clojure to JDBC -- coercions, not outbound)

seancorfield18:02:21

Not sure what you mean about "JDBC has versions"?

orestis18:02:29

> The PostgreSQL™ JDBC driver implements native support for the Java 8 Date and Time API (JSR-310) using JDBC 4.2.

orestis18:02:44

From the page I linked above

orestis18:02:55

(On mobile sorry if replies are terse)

seancorfield18:02:36

No idea about PG -- it def. does some weird stuff. I added next.jdbc.date-time because it would not do those coercions automatically.

orestis18:02:53

LocalDate localDate = rs.getObject(1, LocalDate.class));

seancorfield18:02:57

Either way, next.jdbc just passes objects back and forth to JDBC.

orestis18:02:04

This is the example they give

seancorfield18:02:16

Right, which is not the call next.jdbc makes.

seancorfield18:02:08

next.jdbc uses the single argument call because it has to be generic.

orestis18:02:19

Ah, so that’s an overload to get a specific class if you already know what to expect

seancorfield18:02:40

Right, which next.jdbc cannot know in advance -- since it is a type from "user code" and not from the database schema.

orestis18:02:38

Ok that’s clearer now, thanks

orestis18:02:53

Last question, on the ReadbleColunn protocol, when is the function with the column name called? In some light testing I only saw the version with the column Index called.

orestis18:02:44

And also since it looks to be a global protocol, I can’t see how the name or index are useful. Unless you can customize this per call and I haven’t found out yet

seancorfield18:02:13

That's explained in the docs somewhere...

seancorfield18:02:28

(I'm eating breakfast so I can't look right now)

seancorfield18:02:46

plan uses just the column label. execute! uses the column index and passes the result set metadata so you can look in the metadata for more information about the (JDBC) type of that column, as well as get its name etc.

seancorfield18:02:17

(`plan` is deliberately restrictive so that it can be as fast as possible, so it avoids fetching result set metadata if possible)

orestis18:02:57

Thanks, sorry for interrupting your breakfast!

seancorfield18:02:06

NP. I looked in the docs once I'd finished (eggs over easy and maple pork links!).

orestis18:02:44

Yum! Gotta love the options in California. I miss being in a Mediterranean climate. (Greek living in Denmark)

seancorfield18:02:01

That was home-cooked 🙂

seancorfield18:02:26

But, yeah, California has some awesome food options.

orestis18:02:38

I will try to write a JDBC 101 because frankly the situation was really confusing to me when I started dealing with all those things :)

seancorfield18:02:54

Happy to extend the docs with that, if you feel like contributing via a PR!

orestis18:02:43

Yeah I will try! Thanks for all the work you’re doing!

orestis18:02:07

Gotta put the baby to sleep... good night/day!