Fork me on GitHub
#sql
<
2020-07-21
>
sofra04:07:12

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?

sofra04:07:48

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

seancorfield05:07:16

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

sofra05:07:28

:thumbsup:

seancorfield05:07:51

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

sofra05:07:13

Thanks @seancorfield, makes sense

seancorfield05:07:08

(hence all the caveats on that page 🙂 )

seancorfield05:07:30

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

seancorfield05:07:44

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).

sofra05:07:28

@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.

seancorfield05:07:42

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

sofra05:07:11

yeah I was specifying it

seancorfield05:07:58

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.

seancorfield05:07:32

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

sofra05:07:01

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.

sofra05:07:06

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

g7s08:07:25

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

seancorfield16:07:01

Can you provide a concrete example so we can a) see exactly what code you are writing b) verify the behavior @g7s? The content of the SQL has no bearing on whether :builder-fn is used so I suspect you have a bug in your code.

g7s16:07:52

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]}
The options I pass to the query include a :builder-fn with value the following function
(defn- as-entities [rs opts]
  (as-modified-maps rs (assoc opts :qualifier-fn entity-namespace :label-fn kebab)))
Now when I execute the query I am certain that the :qualifier-fn is not getting called while the :label-fn is getting called.

seancorfield16:07:15

What database are you using?

g7s16:07:23

PostgreSQL

g7s16:07:59

For other queries works as expected though

seancorfield16:07:58

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

seancorfield16:07:39

(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))))))
^ if .getTableName() returns nil, we have no table name to process.

g7s16:07:49

Ah OK it makes sense!

seancorfield16:07:11

Computed columns often have no table name associated with them.

g7s16:07:25

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

g7s16:07:08

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

seancorfield16:07:30

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?

seancorfield16:07:06

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...

g7s16:07:46

Lemme check for the driver version I am running

seancorfield16:07:04

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.

g7s16:07:08

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

g7s16:07:27

Yes I can see that it should return ""

seancorfield16:07:35

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...

seancorfield16:07:10

I test against 42.2.10 just FYI.

g7s16:07:20

will do just a sec

g7s16:07:59

Should I test against the latest 42.2.14?

g7s16:07:57

Hey it worked!

seancorfield16:07:59

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?

seancorfield16:07:11

Ah! So they fixed their bug 🙂

g7s16:07:21

haha seems like it!

g7s16:07:34

Thanks a lot Sean

seancorfield17:07:37

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...

g7s17:07:36

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

g7s17:07:20

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

seancorfield17:07:42

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

g7s17:07:56

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

seancorfield17:07:19

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

g7s17:07:59

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)

g7s17:07:28

(maybe as metadata to the honeysql datastructure)

g7s17:07:01

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

Eric Ihli15:07:20

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}]
I see that next.jdbc lets you extend the ReadableColumn protocol for different types. That lead me to get the following code that I works for at least a simple example.
(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)))
But I see that there's also a read-column-by-label that doesn't receive a ResultSetMetaData object which is what I need in order to get the column type name and check to see if it's boolean. I haven't been able to determine how/where read-column-by-label is used and how/if this solution is insufficient. I imagine there's a better solution than keeping a set of which column names refer to boolean columns and doing something like (read-column-by-label [x name] (if (set-of-boolean-columns name) ,,,)

seancorfield16:07:01

Can you provide a concrete example so we can a) see exactly what code you are writing b) verify the behavior @g7s? The content of the SQL has no bearing on whether :builder-fn is used so I suspect you have a bug in your code.

seancorfield16:07:44

@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.

seancorfield16:07:10

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

Eric Ihli23:07:18

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|

Eric Ihli13:07:34

Ah. This was it.

SELECT p.name, p.type, m.name
FROM sqlite_master AS m
JOIN pragma_table_info(m.name) AS p;