Fork me on GitHub
#sql
<
2021-06-28
>
mbarillier01:06:13

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?

seancorfield02:06:04

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.

seancorfield02:06:40

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

mbarillier12:06:18

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?

seancorfield02:06:14

@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

seancorfield02:06:08

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.

seancorfield02:06:38

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.

seancorfield02:06:31

Show us your code 🙂

Dongwook Kim02:06:42

(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()"})))

Dongwook Kim02:06:46

this is the one

seancorfield02:06:32

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

seancorfield02:06:45

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

seancorfield02:06:14

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

3
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?

seancorfield02:06:13

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!

seancorfield02:06:03

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

seancorfield02:06:18

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

seancorfield02:06:06

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 🙂

seancorfield02:06:08

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 🙏

seancorfield03:06:49

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

seancorfield03:06:26

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. https://dev.mysql.com/doc/c-api/8.0/en/mysql-affected-rows.html

seancorfield16:06:25

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