sql 2023-10-13 | Slack Archive

tomc14:10:24

Hi All, I'm using next.jdbc to query a db and dump the results right into a csv, so I don't want keyword column names. Is this a reasonable way to implement the result set builder to get string column names?

(require '[next.jdbc.result-set :as rs])
(import '(java.sql ResultSet ResultSetMetaData))
(defn get-string-column-names
  "Given `ResultSetMetaData`, return a vector of string column names."
  [^ResultSetMetaData rsmeta _]
  (mapv (fn [^Integer i] (str (.getColumnLabel rsmeta i)))
        (range 1 (inc (if rsmeta (.getColumnCount rsmeta) 0)))))

(defn as-string-arrays
  "Given a `ResultSet` and options, return a `RowBuilder` / `ResultSetBuilder`
  that produces a vector of simple string column names followed by vectors of row
  values."
  [^ResultSet rs opts]
  (let [rsmeta (.getMetaData rs)
        cols   (get-string-column-names rsmeta opts)]
    (rs/->ArrayResultSetBuilder rs rsmeta cols)))

It's working, I just want to be sure this is the right what to go about this.

igrishaev14:10:37

The better approach would be to use CopyManager that redirects the result into an OutputStream. It's much faster than selecting the data in memory and write them to CSV manually

igrishaev15:10:21

(-> (new CopyManager conn)
    (.copyOut "copy my_table TO STDOUT WITH (FORMAT CSV)" out))

where

out = (-> "some/file.csv" io/file io/output-stream)

and

conn = (jdbc/get-connection <datasource>)

tomc15:10:57

Thanks, I will look into that option.

2023-10-13

Channels