Fork me on GitHub
#sql
<
2021-11-16
>
Jakub Holý (HolyJak)08:11:34

I have been quite surprised to see that (next.jdbc.sql/insert! conn "mytable" {:val "A" :num 7}) (which creates a prepared statement and uses, I asssume, execute on it) returns #:mytable{:val "A", :num 7} - I expected it to return a count or the primary key, not the whole thing I inserted. The docstring of the jdbc methods are not very clear to me on this. So the question is: What does insert return? Is it the same across DBs? Do you know? Thank you!

dharrigan08:11:25

I assume you're using postgresql?

1
dharrigan08:11:51

If so, that is the expected behaviour with insert!

dharrigan08:11:07

if you want just the update count, use execute! or execute-one!

seancorfield16:11:42

@holyjak The docstring says "Given a connectable object, a table name, and a data hash map, inserts the data as a single row in the database and attempts to return a map of generated keys." (my emphasis). What you're seeing is what the JDBC driver returns when it asked to return generated keys. For most databases, it's just the generated key(s) under a DB-specific name. MySQL uses :GENERATED_KEY for example. But PostgreSQL returns the entire inserted row. Because PostgreSQL.

seancorfield16:11:44

It says "attempts" because not all databases will return keys properly and different databases return different items in the ResultSet. There's at least one database I've had to debug that says "yes, I have a ResultSet for those generated keys!" but when you actually try to get the ResultSet, you get a fake one that is "empty" but doesn't support all the methods you expect 😞

Jakub Holý (HolyJak)16:11:55

OMG, it is almost as bad as the inconsistent SQL implementations themselves 😞 Thank you! The key takeaway is: test your database because no one can know what its developers decided to do 😉

😅 1
seancorfield17:11:04

Yup, the next.jdbc test suite is full of convenience functions that know how to extract generated keys from different databases, as well as several other DB-specific weirdness.

seancorfield17:11:56

Most of my maintenance work over the years on clojure.java.jdbc, next.jdbc, and honeysql has been due to PostgreSQL and Oracle "weirdness" -- and I don't even use either of those DBs!

thumbnail18:11:37

Thanks for the effort though! The supported edge cases in next.jdbc (for Postgres at least) are appreciated

💯 2
1
dcj21:11:45

+1, as a Postgres user, I really appreciate @U04V70XH6's enormous efforts to support Postgres

3
💯 3