Clojurians Log v2

Hi, I am using next.jdbc and am doing a batch insert using next.jdbc.prepare/execute-batch! It is working as expected but then following the instructions here https://cljdoc.org/d/seancorfield/next.jdbc/1.1.569/doc/getting-started/prepared-statements#batched-parameters I and trying to use .getGeneratedKeys to get the results set back and it only returns the last batch to be inserted not the entire set. Is this expected behaviour?

I sort of understand why, it is stateful and .getGeneratedKeys would need to be called in between each call to .executeBatch?

@sofra Yeah, it's a bit of an edge case. Different databases behave differently on this, unfortunately.

:thumbsup:

Some databases will return all generated keys (from all batches executed), some don't return any keys at all.

Thanks @seancorfield, makes sense

(hence all the caveats on that page 🙂 )

How big is the batch you're inserting @sofra?

If it's large enough to cause multiple .executeBatch calls, I can understand you only getting one set of keys back. I guess I could modify execute-batch! so that if :return-keys is truthy, it could attempt to call .getGeneratedKeys each time internally and return the result sets joined together. It seems like a reasonable enhancement, although I'd need to ensure it doesn't break existing behavior (so, probably another option as well as :return-keys I suspect).

https://github.com/seancorfield/next-jdbc/issues/133

@seancorfield it is a few thousand rows. Thanks for the info. I can work around it now I understand. Sounds like a reasonable enhancement to me too.

@sofra Are you specifying the :batch-size option to execute-batch! or just letting it do its thing?

yeah I was specifying it

If you don't specify it, .executeBatch will only be called once and .getGeneratedKeys should return all the keys -- but that may too much data for a single batch.

Anyway, I'll add that enhancement probably this week and put out a new release by the end of next weekend at the latest.

Thanks @seancorfield I was batching since it was too much for a regular mutli-insert! and the db was throwing errors. That is why I figured I would need to set :batch-size but I didn’t actually test that assumption.

Thanks for your help, much appreciated! Happy to give it a try if you need when you have something ready.

I don't know if this has been asked before but here it goes. Is there a way to make next.jdbc 's :builder-fn work with CTEs? For some context: I am using WITH RECURSIVE to build a result set but it seems that the :builder-fn configuration option is not used

I'm trying to get Clojure booleans from SQLite booleans (which are stored as integers). I'm close, but there's at least one thing I don't understand and I'm curious if there's something I don't know and a better way to do this.

(require '[next.jdbc :as jdbc])

  (def db-spec {:dbtype "sqlite" :dbname "example-db"})

  (jdbc/execute!
   db-spec
   ["create table if not exists users (name text, is_active bool)"])

  (jdbc/execute!
   db-spec
   ["insert into users (name, is_active) values (?, ?)" "alice" true])

  (jdbc/execute!
   db-spec
   ["select * from users"])
  ;; => [#:users{:name "alice", :is_active 1}]

(extend-protocol result-set/ReadableColumn
  Integer
  (read-column-by-index [x mrs i]
    (if (re-find #"(?i)bool" (.getColumnTypeName mrs i))
      (if (= 1 x) true false)
      x)))

@ericihli Can you create an issue on next.jdbc with details of that? I use MySQL and it stores booleans as BIT(1) and those do convert into true/`false` as expected. I haven't specifically tried SQLite in that situation.

Sure thing.

next.jdbc tries to canonicalize Boolean values if it sees them but that assumes the JDBC driver actually produces Boolean values 🙂

Sure. I am using honeysql so this is the query (as data) that I am running

{:with-recursive [[:starting-record
                   {:select [:*]
                    :from   [:records]
                    :limit  1}]
                  [:records-path
                   {:union-all [{:select [:*]
                                 :from   [:starting-record]}
                                {:select [:recs.*]
                                 :from   [[:records-path :rp]]
                                 :join   [[:records :r]
                                          [:= :rp.previous-id :recs.id]]}]}]]
 :select         [:*]
 :from           [:records-path]}

(defn- as-entities [rs opts]
  (as-modified-maps rs (assoc opts :qualifier-fn entity-namespace :label-fn kebab)))

What database are you using?

PostgreSQL

For other queries works as expected though

:qualifier-fn is not called if a given column has no associated table name -- and that's up to the JDBC driver.

(mapv (fn [^Integer i]
            (if-let [q (some-> (.getTableName rsmeta i) (qf) (not-empty))]
              (keyword q (-> (.getColumnLabel rsmeta i) (lf)))
              (keyword (-> (.getColumnLabel rsmeta i) (lf)))))
          (range 1 (inc (.getColumnCount rsmeta))))))

Ah OK it makes sense!

Computed columns often have no table name associated with them.

I see. Is there anything I can do to pass somehow a function that even with nil table name to return something? (constantly "foo")

Right now I am mapping the keys manually which is a bit ugly

At some point, I could have sworn I'd made a change to always call :qualifier-fn so that you could get behavior compatible with clojure.java.jdbc... I'll have to go digging. Can you open an issue on next.jdbc with details of the above, so I don't forget?

Sure!

Hmm, interesting. Normally, .getTableName() would return "" for a table name not being available and there's actually a test for this and it does work. So this is a case where .getTableName() seems to be returning nil instead which is somewhat unexpected...

Lemme check for the driver version I am running

See https://docs.oracle.com/en/java/javase/11/docs/api/java.sql/java/sql/ResultSetMetaData.html#getTableName(int) -- it is explicitly supposed to return "" if the table name is not available. Not nil.

I am using [org.postgresql/postgresql "42.2.9"]

Yes I can see that it should return ""

I can certainly workaround that by adding (or "") into that threading expression above but I'd certainly like to confirm that PG really is returning nil in this case...

I test against 42.2.10 just FYI.

will do just a sec

Should I test against the latest 42.2.14?

Hey it worked!

Sure. Add a note into the issue you create saying what versions you tested against. I just looked over the PG (developers) mailing list and it appears they had it hardcoded to return "" at one point and then changed it to return the base table name... so I'm wondering if this is essentially a bug in PG that the base table name is returning null instead of "" in this case?

Ah! So they fixed their bug 🙂

haha seems like it!

Thanks a lot Sean

I don't see that change listed anywhere here https://jdbc.postgresql.org/documentation/changelog.html#version_42.2.14 but I will run my test suite with 42.2.9 and up to see if the test case I have is affected by any of those versions...

I checked again with 42.2.9 and it seems that it is getting called with "" but I guess that is what you get when you debug with println 😒 I somehow missed all the empty strings printed in my REPL. Sorry for wasting your time

I was expecting to see a table name there (didn't know the computed columns do not have a table name attached)

OK, so :qualifier-fn (constantly "foo") should work, even on 42.2.9.

Yes it should.. Now I have to figure out how I can pass it from the query definition 🙂

Well, you have :qualifier-fn entity-namespace so that function is called 🙂

Yeah this entity-namespace actually maps a table name to an entity namespace and the mapping is pretty hardcoded. Since I am defining my queries separately from where I am executing them I have to find a way to pass to the query data the table name to be used (for queries that lack the table name)

(maybe as metadata to the honeysql datastructure)

Anyway I don't want to waste more of your time 😳

Trying to accomplish the below in SQLite. Curious how to do this in all SQL rather than writing Clojure code to do the looping and conjing. Anyone have a pointer, even it it's just a hint, like "Look into CTE and recursive" or something.

WITH table_names AS (SELECT name FROM sqlite_master) SELECT * FROM table_names;
-- For each table_name above, join it to each of its column names and types, like in the select below.
SELECT name, type FROM pragma_table_info("<table_name>");
-- Union everything so the end result is a table of all tables, columns, and column types.
-- |table_name|column_name|column_type|

Maybe the second answer here: https://stackoverflow.com/questions/604939/how-can-i-get-the-list-of-a-columns-in-a-table-for-a-sqlite-database ?