hi, does next.jdbc provide a way to read database metadata ? https://www.progress.com/blogs/jdbc-tutorial-extracting-database-metadata-via-jdbc-driver

I saw the datafy ns - but don't know how to use it

@eugen.stan It depends what you want to do. https://cljdoc.org/d/com.github.seancorfield/next.jdbc/1.2.761/doc/migration-from-clojure-java-jdbc#processing-database-metadata shows how to get metadata via interop but, personally, I tend to rely on the datafy/nav stuff via Portal (I used to use Cognitect's REBL for that).

thanks, interop is fine until I get more familiar with this

clj꞉calcite-clj.simple꞉> 
[{:REF_GENERATION nil,
  :SELF_REFERENCING_COL_NAME nil,
  :TABLE_CAT nil,
  :TYPE_CAT nil,
  :TYPE_SCHEM nil,
  :REMARKS nil,
  :TYPE_NAME nil,
  :TABLE_SCHEM "SALES",
  :TABLE_TYPE "TABLE",
  :TABLE_NAME "DEPTS"}
 {:REF_GENERATION nil,
  :SELF_REFERENCING_COL_NAME nil,
  :TABLE_CAT nil,
  :TYPE_CAT nil,
  :TYPE_SCHEM nil,
  :REMARKS nil,
  :TYPE_NAME nil,
  :TABLE_SCHEM "SALES",
  :TABLE_TYPE "TABLE",
  :TABLE_NAME "EMPS"}]

soooo if I wanted to use update! where one of the values is an expression... is that possible? or do I need to run execute-one! with sql building instead

where by expression I mean COALESCE(submitted_at, now())

it seems like I need to fall back on execute-one!

That's why HoneySQL exists 🙂

dev=> (sql/format (-> (h/update :table) (h/set {:foo [:coalesce :submitted_at [:now]]}) (h/where [:= :id 100])))
["UPDATE table SET foo = COALESCE(submitted_at, NOW()) WHERE id = ?" 100]

Some people prefer the symbolic form -- it's a bit more datalog-y:

dev=> (sql/format '{update table set {foo (coalesce submitted_at (now))} where (= id 100)})
["UPDATE table SET foo = COALESCE(submitted_at, NOW()) WHERE id = ?" 100]

yeah, I just switched to honeysql for this part

it works

thanks!

whoa, that symbolic form is interesting

is there a way to not hard-code values?

You could do it all with keywords and vectors.

how do you mean?

I was meaning since it's quoted you can't use bindings in the sql

dev=> (let [id 123]
 #_=>   (sql/format {:update :table :set {:foo [:coalesce :submitted_at [:now]]} :where [:= :id id]}))
["UPDATE table SET foo = COALESCE(submitted_at, NOW()) WHERE id = ?" 123]

(let [i 200]
  (hsql/format '{update table set {foo (coalesce submitted_at (now))} where (= id i)}))

ah, yeah

oh, is there a function in honeysql to convert {:id 1 :status_id 2} to [:and [:= :id 1] [:= :status_id 2]] ?

it's simple enough to write yourself but if there's something that already exists I'd rather just use that

(hsqlh/where (reduce-kv (fn [where col val]
                          (conj where [:= col val]))
                        [:and]
                        where-map))

Oh, that's an interesting idea! Feel free to create an issue.

BTW, you can mix'n'match the keyword and symbol versions:

dev=> (let [id 123] (sql/format {:update :table :set '{foo (coalesce submitted_at (now))} :where [:= :id id]}))
["UPDATE table SET foo = COALESCE(submitted_at, NOW()) WHERE id = ?" 123]

oh, interesting

that makes sense

And of course you could always do something awful like this:

dev=> (let [id 123] (sql/format (update '{update table set {foo (coalesce submitted_at (now))} where (= id)} 'where concat [id])))
["UPDATE table SET foo = COALESCE(submitted_at, NOW()) WHERE id = ?" 123]

oh dear

A more datalog-y approach is to use named parameters:

dev=> (sql/format '{update table set {foo (coalesce submitted_at (now))} where (= id ?id)} {:params {:id 123}})
["UPDATE table SET foo = COALESCE(submitted_at, NOW()) WHERE id = ?" 123]

(I kinda like that version)

huh, yeah, that's nice

is count on a vec O(1)?