Fork me on GitHub

I'm missing something basic about consuming next.jdbc results lazily. I've got multiple tables containing similar data, and I can hack up a transform that converts rows from each of the tables into a normalized result. what I'd like to do is create a function for each table/data source that returns a lazy sequence of the results from the source mapped through the appropriate normalization function, something like:

(defn normalized-results-from-table-x
  (map normalize-row-for-table-x
       (jdbc/plan (jdbc/get-datasource *spec*) ["select ... from table x ..."])))
... however plan returns an IResultSet that can't be used as a sequence. execute! isn't the answer as it realizes the result of the query. what's the proper incantation?


You can't get a lazy sequence out of next.jdbc because of resource management (connection handling) but you can lazily stream results from the DB by reducing (or transducing) the result of plan.


The only laziness available is in terms of (large) result sets being read from the database.


yes, makes sense -- wasn't quite the answer I was hoping for. 🙂 I'll change the level of abstraction, will require a slight re-design but it's do-able. thanks!

Dongwook Kim01:06:09

Hi, I'm quite new at clojure and next.jdbc. I'd like to get updated row from insert ... on duplicated key update using next.jdbc I've tried {:return-keys true} but no luck(`next.jdbc` always give me nil ). I don't know how to do this. is there anybody help me out?


@forestkeep21 Which database are you using? Not all of them will return a full row. Most only return the generated keys.

Dongwook Kim02:06:08

mysql. on duplicated key will return just integer not row. I think this is reason I get only nil


PostgreSQL returns full rows, but it's the only database I know that does that. At least based on all the databases I test next.jdbc against.


MySQL should give you {:GENERATED_KEY <newkey>} in the results from an insert.

Dongwook Kim02:06:23

hm.. I don't know why I've seen only nil then. probably I do something wrong.


Show us your code 🙂

Dongwook Kim02:06:42

(defn create-device-token!
  [db user-id platform token-type token]
  (prn (sql/insert! db
                  {:user_id user-id
                   :platform (name platform)
                   :type (name token-type)
                   :token token}
                  {:suffix "ON DUPLICATE KEY UPDATE created_at=now()"})))

Dongwook Kim02:06:46

this is the one


And that just prints an integer? That'll be the updated row count then, because that's all the JDBC driver is giving us back.

Dongwook Kim02:06:03

no, It prints only nil I wish it prints an integer


dev=> (sql/insert! ds :status {:id 1 :name "foo"} {:suffix "on duplicate key update name = 'double'"})
That's with the update triggering.


Are you sure that you're not just checking the result of create-device-token!? That will be nil because prn returns nil.

Dongwook Kim02:06:23

Interesting, I might miss something. I'll check more and If any progress, I'll share the result on this channel. 🙏

Dongwook Kim02:06:14

@seancorfield How about in the case of no auto increment on primary key? In that case can I get a generated key as return value also?


Since it wouldn't be generating a key in that case, I would not expect you to get one back.

Dongwook Kim02:06:34

ohh... that's why I get only nil. Thank you!



dev=> (sql/insert! ds :photocropgallery {:id 1 :x 1 :y 1 :width 1 :height 1} {:suffix "on duplicate key update x = 13"})
dev=> (sql/insert! ds :photocropgallery {:id 1 :x 1 :y 1 :width 1 :height 1} {:suffix "on duplicate key update x = 13"})
id in that table is the PK but has no auto_increment so there's no "generated key" to return.

👏 3
Dongwook Kim02:06:59

is there no option to get updated key result then?


Not unless you know some SQL you could invoke that will do that.


For comparison, without trying to return keys:

dev=> (jdbc/execute-one! ds ["insert into photocropgallery (id,x,y,width,height) values (2,2,2,2,2) on duplicate key update x = 42"])
#:next.jdbc{:update-count 1}
dev=> (jdbc/execute-one! ds ["insert into photocropgallery (id,x,y,width,height) values (2,2,2,2,2) on duplicate key update x = 42"])
#:next.jdbc{:update-count 2}
Note how the update count is 1 for an insertion, but 2 for the case where the update is triggered. Still no data returned 🙂


FWIW, MariaDB which is a fork on MySQL I believe does support insert .. returning, like PostgreSQL.

Dongwook Kim02:06:52

:update-count is what I want, I'll give it try!

Dongwook Kim02:06:42

{:next.jdbc/update-count 2} finally! thank you @seancorfield you've saved my day 🙏


@forestkeep21 Just to be clear: that is the number of rows updated -- it isn't any of the key values.


dev=> (jdbc/execute-one! ds ["insert into photocropgallery (id,x,y,width,height) values (4,4,4,4,4) on duplicate key update x = 42"])
#:next.jdbc{:update-count 1}
dev=> (jdbc/execute-one! ds ["insert into photocropgallery (id,x,y,width,height) values (4,4,4,4,4) on duplicate key update x = 42"])
#:next.jdbc{:update-count 2}
1 row updated in the first insert (which actually inserted the row), 2 rows updated in the second insert (which triggered the update). I've no idea why MySQL thinks two rows were updated in the second case though...

🙆‍♀️ 3
Dongwook Kim07:06:59

FYI > For INSERT > ... ON DUPLICATE KEY UPDATE statements, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values.


Oh, cool. TIL! Nice to know that is a well-defined value for such a statement.