Question: how to extend ReadableColumn/SettableParameter for vectors?

[janusvm]

I'm using a H2 database and have a table with a column like this:

create table recurrence(
    ...
    exdate date array,
    ...
);

and would like to make it so I can pass a PersistentVector of LocalDate to exdate when inserting, and getting it back like that again when selecting, like I can with naked LocalDates/date columns.

The Tips & Tricks describe how to read them, but not how to write them, so I tried the following:

(extend-protocol next.jdbc.prepare/SettableParameter
  clojure.lang.PersistentVector
  (set-parameter [^clojure.lang.PersistentVector v ^java.sql.PreparedStatement ps ^long i]
    (.setArray ps i (into-array v))))

but when inserting with something like {:exdate [(LocalDate/of 2023 12 31)]} I just get

1. Unhandled java.lang.ClassCastException
   class [Ljava.time.LocalDate; cannot be cast to class java.sql.Array

If I explicitly pass an array with {:exdate (into-array LocalDate [(LocalDate/of 2023 12 31)]}, it works when inserting, but then the vector I get out when selecting is still of java.sql.Date.

Is there a way to extend the protocols such that I don't have to explicitly convert the input/output values in my calling functions?

[seancorfield]

Your problem is that PreparedStatement.setArray() expects a java.sql.Array -- per https://docs.oracle.com/en/java/javase/17/docs/api/java.sql/java/sql/PreparedStatement.html#setArray(int,java.sql.Array) -- and into-array does not produce that, hence the ClassCastException.

You'll have to figure out how to create java.sql.Array objects somehow (looking at the Java docs, I'm not sure how you'd do that).

When you pass (into-array LocalDate ..) it goes through the .setObject() default path.

[janusvm]

Thanks for the pointers! Using .setObject instead of .setArray was the key for writing the vecs "directly", i.e.

(extend-protocol next.jdbc.prepare/SettableParameter
  clojure.lang.PersistentVector
  (set-parameter [^clojure.lang.PersistentVector v ^java.sql.PreparedStatement ps ^long i]
    (.setObject ps i (into-array v))))

This also gave me the clue for reading them back out into vecs of certain types, as the java.sql.Array in read-column-by-index was always an array of Objects. It's not the prettiest solution, but it works:

(defn convert-sql-array [^java.sql.Array v]
  (let [x (first (.getArray v))
        f (cond (instance? java.sql.Date x) #(.toLocalDate %)
                (instance? java.sql.Time x) #(.toLocalTime %)
                (instance? java.sql.Timestamp x) #(.toLocalDateTime %)
                :else identity)]
      (vec (map f (.getArray v)))))

(extend-protocol next.jdbc.result-set/ReadableColumn
  java.sql.Array
  (read-column-by-label [^java.sql.Array v _]
    (convert-sql-array v))
  (read-column-by-index [^java.sql.Array v _2 _3]
    (convert-sql-array v)))