sql

All things SQL and JDBC...
mbarillier 2021-06-28T01:11:13.299200Z

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?

mbarillier 2021-06-28T12:28:18.320900Z

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 Kim 2021-06-28T01:43:09.301500Z

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?

seancorfield 2021-06-28T02:07:04.301700Z

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.

seancorfield 2021-06-28T02:07:40.301900Z

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

seancorfield 2021-06-28T02:09:14.302700Z

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

seancorfield 2021-06-28T02:09:53.303300Z

Does that help @mbarillier?

Dongwook Kim 2021-06-28T02:10:08.303800Z

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

seancorfield 2021-06-28T02:11:08.304800Z

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.

seancorfield 2021-06-28T02:11:38.305400Z

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

Dongwook Kim 2021-06-28T02:13:23.306700Z

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

seancorfield 2021-06-28T02:13:31.306900Z

Show us your code 🙂

Dongwook Kim 2021-06-28T02:13:42.307400Z

(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 Kim 2021-06-28T02:13:46.307600Z

this is the one

seancorfield 2021-06-28T02:14:32.308300Z

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 Kim 2021-06-28T02:15:03.308800Z

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

seancorfield 2021-06-28T02:17:45.309400Z

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.

seancorfield 2021-06-28T02:18:14.310Z

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

Dongwook Kim 2021-06-28T02:19:23.311Z

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

1
Dongwook Kim 2021-06-28T02:31:14.312100Z

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

seancorfield 2021-06-28T02:33:13.312600Z

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

Dongwook Kim 2021-06-28T02:34:34.313600Z

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

seancorfield 2021-06-28T02:37:03.314200Z

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.

👏 1
Dongwook Kim 2021-06-28T02:37:30.314600Z

I see. 👍

Dongwook Kim 2021-06-28T02:37:59.315Z

is there no option to get updated key result then?

seancorfield 2021-06-28T02:40:18.315400Z

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

seancorfield 2021-06-28T02:41:06.316400Z

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 🙂

seancorfield 2021-06-28T02:42:04.316600Z

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

seancorfield 2021-06-28T02:43:08.317400Z

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

Dongwook Kim 2021-06-28T02:49:52.318Z

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

Dongwook Kim 2021-06-28T02:54:42.318500Z

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

seancorfield 2021-06-28T03:22:49.319100Z

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

seancorfield 2021-06-28T03:24:26.320300Z

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

🙆‍♀️ 1
Dongwook Kim 2021-06-28T07:31:59.320500Z

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

seancorfield 2021-06-28T16:53:25.321100Z

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