Fork me on GitHub
Slack Archive

This page is not created by, affiliated with, or supported by Slack Technologies, Inc.

2021-03-22

Channels

#sql
<
2021-03-22
>
Aviv Kotek15:03:52

Is there anyway to get the "updated-rows" keys/columns from an update query (with next.jdbc 1.1.582 & MySQL) 

(next.jdbc/execute!
  ds
  ["UPDATE table SET col1=1 WHERE col2=test"]
  {:return-keys ["col1" "col2"]})
=> []
although an update occurred..

seancorfield16:03:21

@aviv The JDBC driver doesn’t provide that information (otherwise the above would work).

Aviv Kotek16:03:43

so no way to escape 2x db-operations

seancorfield16:03:28

You mean an update followed by a select in a single transaction?

seancorfield16:03:09

(or even sending multiple statements in a single operation — that’s documented for MySQL but not advised because it’s easier to succumb to a SQL injection attack that way)

Aviv Kotek16:03:44

Update without a followed select (for simplicity), but I guess this is not possible

Aviv Kotek16:03:02

update&select in a single transaction is possible by wrapping with the transaction macro

seancorfield16:03:42

Per the link above you can do update ...; select ... if you add that JDBC connection setting. Like I say, not recommended in general.

Aviv Kotek16:03:02

doesn't it support .getGeneratedKeys() or this won't do in MYSQL, I remember from Java days this is usually supported

seancorfield16:03:04

There is no ResultSet from the update — next.jdbc tries to return a ResultSet first in all cases and only if one isn’t available does it ask for the update count. If you ask for :return-keys, it will also try .getGeneratedKeys() (after asking for a ResultSet but before falling back to update counts).

seancorfield16:03:45

next.jdbc is pretty close to the JDBC layer: when you execute! or execute-one!, it does this: 

(if (.execute stmt)
    (.getResultSet stmt)
    (when (:return-keys opts)
      (try
        (.getGeneratedKeys stmt)
        (catch Exception _))))