Clojurians Log v2

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

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?

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.

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

Does that help @mbarillier?

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.

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

Show us your code 🙂

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

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.

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'"})
{:GENERATED_KEY 1}

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

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

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

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

Confirmed:

dev=> (sql/insert! ds :photocropgallery {:id 1 :x 1 :y 1 :width 1 :height 1} {:suffix "on duplicate key update x = 13"})
nil
dev=> (sql/insert! ds :photocropgallery {:id 1 :x 1 :y 1 :width 1 :height 1} {:suffix "on duplicate key update x = 13"})
nil

I see. 👍

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}

https://dev.mysql.com/doc/refman/5.7/en/insert.html

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

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

{: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}