Fork me on GitHub
#sql
<
2019-02-01
>
leontalbot19:02:40

@seancorfield So using update fn I ran into maximum query limit

leontalbot19:02:39

Caused by java.io.IOException Tried to send an out-of-range integer as a 2-byte value: 59540

seancorfield19:02:30

Need more context to be able to help.

seancorfield19:02:41

What SQL, what params, what code πŸ™‚

seancorfield19:02:03

"Psql" doesn't mean anything to me...

leontalbot19:02:52

I use postgreSQL

leontalbot19:02:22

I had this query in hugsql:

--- :name update-item :returning-execute :*
-update items as i set
-    a = t.a
-    , b = t.b
-    , c = t.c
-    , d = t.d
-from (values :t*:updated_items)
-as t(id, a, b, c, d)
-where i.id = t.id
-returning i.id, i.a, i.b, i.c, i.d

seancorfield19:02:33

I've no idea what that SQL does, sorry. I don't use PostgreSQL. But that doesn't look like it should generate lots of bind variables to me.

leontalbot19:02:26

Ok np. Then found this:

seancorfield19:02:16

I don't see any result for that link (it's in French and seems to say I don't have permission to read it?).

seancorfield19:02:55

Wow, that's terrible advice! That's not how the java.jdbc API should be used at all 😞

seancorfield19:02:03

db-do-commands is for DDL, not SQL. db-do-prepared is a low-level routine (that's intended to be called by other parts of the library).

seancorfield19:02:10

It might help me if you explained what you are trying to do and what your question actually is?

leontalbot19:02:54

Haha, my initial question was simply how can I return maps subsets after an UPDATE

seancorfield19:02:33

Well, :return-keys has nothing to do with updates, only inserts. So that explains that confusion πŸ™‚

seancorfield19:02:16

If you're doing an insert, you can ask the JDBC driver to return specific generated keys/columns on some databases.

seancorfield19:02:34

(otherwise it will return a result set based on whatever the JDBC driver returns)

seancorfield19:02:02

Most DBs, when you do an update, just return the number of rows updated.

leontalbot19:02:17

yeah, that’s what I got too

leontalbot19:02:39

Thanks you for your time @seancorfield

seancorfield19:02:12

I've no idea what that exception was caused by since I don't know a) what SQL would be generated from that HugSQL file and b) how HugSQL would try to run that SQL -- both matter here.

seancorfield19:02:37

execute! supports :multi? true for batched operations BTW (which I think might address what those StackOverflow Q&A seem to be about)

seancorfield19:02:28

There are examples in the java.jdbc tests of that, e.g.,

(let [counts (sql/execute!
                  db
                  ["INSERT INTO fruit (name,appearance) VALUES (?,?)"
                   ["Apple" "rosy"]
                   ["Pear" "yellow"]
                   ["Orange" "round"]]
                  {:multi? true})
I suspect an UPDATE ... RETURNING would also work there.

seancorfield19:02:49

(in next.jdbc, there's a single, high-level execute! function for handling everything -- it's a much, much simpler API than the current java.jdbc offers -- but that's a ways off yet as I'm still spending a lot of "hammock time" on it)

leontalbot19:02:27

re: next.jdbc that is promising! That was I nice read: https://clojureverse.org/t/next-jdbc-early-notes/3480/15

leontalbot19:02:41

thank you Sean

leontalbot19:02:46

Have a nice day!