Fork me on GitHub
#sql
<
2018-11-26
>
mping15:11:35

how do you guys log the queries in dev envs?

mping16:11:30

@seancorfield does honeysql support case when... then..?

seancorfield17:11:11

@mping As that test shows, you can use sql/call to create a case/`when` but there's nothing "built-in" beyond that.

๐Ÿ‘ 4
kulminaator18:11:18

seeking for advice here on the insert multi

kulminaator18:11:38

i'm trying to insert multiple rows into database at once but i'm failing bad ๐Ÿ˜ž

kulminaator18:11:58

my code has 4 rows as maps and i handle them like this :

(let [row-keys (keys (first rows))
          rows-values (map vals rows)]
      (println row-keys)
      (println rows-values)
      (j/insert-multi! db table row-keys rows-values {})
      )

kulminaator18:11:30

code output is as follows :

(id name nickname retired)
((1 bob the builder nil nil) (2 robby smith nil) (3 alice nil true) (4 jim jimmy jimmy nil))
which looks ok

kulminaator18:11:02

but postgresql raw log says

2018-11-26 20:48:19.010 EET [12614] shopdb_target_user@londu1_test_target_db LOG:  execute S_2: INSERT INTO public.shop_workers ( id, name, nickname, retired ) VALUES ( $1, $2, $3, $4 )
2018-11-26 20:48:19.010 EET [12614] shopdb_target_user@londu1_test_target_db DETAIL:  parameters: $1 = '1', $2 = 'bob the builder', $3 = NULL, $4 = NULL
2018-11-26 20:48:19.010 EET [12614] shopdb_target_user@londu1_test_target_db LOG:  execute S_3: INSERT INTO public.shop_workers ( id, name, nickname, retired ) VALUES ( $1, $2, $3, $4 )
2018-11-26 20:48:19.010 EET [12614] shopdb_target_user@londu1_test_target_db DETAIL:  parameters: $1 = '2', $2 = 'robby', $3 = 'smith', $4 = NULL
2018-11-26 20:48:19.011 EET [12614] shopdb_target_user@londu1_test_target_db LOG:  execute S_4: INSERT INTO public.shop_workers ( id, name, nickname, retired ) VALUES ( $1, $2, $3, $4 )
2018-11-26 20:48:19.011 EET [12614] shopdb_target_user@londu1_test_target_db DETAIL:  parameters: $1 = '3', $2 = 'alice', $3 = NULL, $4 = 't'
2018-11-26 20:48:19.011 EET [12614] shopdb_target_user@londu1_test_target_db LOG:  execute S_4: INSERT INTO public.shop_workers ( id, name, nickname, retired ) VALUES ( $1, $2, $3, $4 )
2018-11-26 20:48:19.011 EET [12614] shopdb_target_user@londu1_test_target_db DETAIL:  parameters: $1 = '4', $2 = 'jim', $3 = 'jimmy jimmy', $4 = NULL

kulminaator18:11:20

and that doesn't look like one insert statement to me ๐Ÿ˜ž

kulminaator19:11:05

can it be that postgresql jdbc driver is tricking me ?

kulminaator19:11:06

no, it actually still seems to be the clojure.java.jdbc itself ... i was expecting it to do the insert into table (cols) values (valueset1), (valueset2), (valueset3) kind of syntax , but that's not what is happening there

kulminaator19:11:21

i guess i could achieve this with the reWriteBatchedInserts option described in https://jdbc.postgresql.org/documentation/94/connect.html

kulminaator19:11:59

added this to the db connection map :

:reWriteBatchedInserts true
and it started to work as expected ๐Ÿ™‚

kulminaator19:11:35

from postgres logs

2018-11-26 21:45:11.706 EET [15239] shopdb_target_user@londu1_test_target_db LOG:  execute <unnamed>: INSERT INTO public.shop_workers ( id, name, nickname, retired ) VALUES ( $1, $2, $3, $4 ),( $5, $6, $7, $8 ),( $9, $10, $11, $12 ),( $13, $14, $15, $16 )
2018-11-26 21:45:11.706 EET [15239] shopdb_target_user@londu1_test_target_db DETAIL:  parameters: $1 = '1', $2 = 'bob the builder', $3 = NULL, $4 = NULL, $5 = '2', $6 = 'robby', $7 = 'smith', $8 = NULL, $9 = '3', $10 = 'alice', $11 = NULL, $12 = 't', $13 = '4', $14 = 'jim', $15 = 'jimmy jimmy', $16 = NULL

seancorfield19:11:17

Ah, good to know that it's a DB-specific issue there! I've opened https://dev.clojure.org/jira/browse/JDBC-174 to remind me to update either the docstring/docs page or the code to set expectations appropriately.

michaellindon23:11:06

@seancorfield what is the best way to create a db connection and reuse it? For instance, if I run (query db ["select * from table"]) multiple times, I think its creating a new connection every time right? I've looked at get-connection and with-db-connection but I can't figure out how to use it, could you please give me an example?

michaellindon23:11:21

would something like

(sql/with-db-connection [con db]
  (sql/query con ["select * from table"])
  ...
  (sql/query con ["select * from table"])
  )
reuse the connection for the multiple queries? dumb example i know

isak23:11:47

@michaellindon i think that is fine for simple cases like that. otherwise, have you seen this? https://github.com/tomekw/hikari-cp#postgresql-example

michaellindon23:11:14

I don't understand why I need it, I'd just like to persist a connection across multiple queries

hiredman23:11:48

with-db-connection will do that

hiredman23:11:58

that is what it is for

hiredman23:11:51

but most people use a connection pool like hikari or c3p0, so they never both with with-db-connection

hiredman23:11:00

there is also, I think it is just called db-connection, which will return an unscoped db connection

donaldball23:11:43

If youโ€™re interested in having a stable basis for your queries, you might want to use perform your queries on a transaction using with-db-transaction (for a definition of stable implied by the transaction isolation level).