Clojurians Log v2
Clojure programming
Channels
Apps
sql
All things SQL and JDBC...
fabrao 2021-03-09T04:04:23.001600Z
hello all, I have a sequence in postgresql, is there any way to use this sequence with next/jdbc to do insert! without using insert as string?
fabrao 2021-03-09T08:06:19.005600Z
yes, that´s I want, like nextval
fabrao 2021-03-09T08:06:25.005800Z
thank you
fabrao 2021-03-09T09:01:09.006Z
Hello Sean, your example is not working
fabrao 2021-03-09T09:11:00.006200Z
this is working
(->
(sfmt-help/insert-into :smtb_flow)
(sfmt-help/values [{:id_flow (sfmt/call :nextval "seq_fluxo") :nm_flow "Teste" :id_company 1 :ds_flow_title "Teste" :dt_created (sfmt/call :NOW)}])
(sfmt/format {:parameterizer :none})
jb recluse 2021-03-09T14:52:30.007Z
@fabrao are you using v2 as indicated? it worked for me:
(require '[honey.sql :as sql])
(require '[honey.sql.helpers :refer :all :as h])
(-> (insert-into :my_tbl)
(values [{:id [:nextval "my_seq"] :my_col "foo"}])
(sql/format {:inline true}))["INSERT INTO my_tbl (id, my_col) VALUES (NEXTVAL('my_seq'), 'foo')"]seancorfield/honeysql {:mvn/version "2.0.0-alpha2"}fabrao 2021-03-09T21:20:53.007200Z
Ho, I did not check about version 2
seancorfield 2021-03-09T21:46:32.007400Z
It's worth noting that the nextval('seq') form works like that only because it is evaluated inline, but if you are not inlining all your parameters (and you probably should not do that -- b/c of SQL injection dangers), then you can just choose to line part of the expression:
(values [{:id [:nextval [:inline "my_seq"]] :my_col "foo"}]){:inline true} in the sql/format call)seancorfield 2021-03-09T21:47:38.007600Z
What will happen here is that "foo" will be lifted out as a parameter, but the sequence name will be inlined:
["INSERT INTO my_tbl (id, my_col) VALUES (NEXTVAL('my_seq'), ?)" "foo"]jb recluse 2021-03-09T21:49:26.008400Z
definitely worth noting, thanks! i was not aware that was possible
seancorfield 2021-03-09T21:53:40.008600Z
V2 syntax is a lot more composable than V1.
seancorfield 2021-03-09T04:14:28.002300Z
Can you explain what you mean by "a sequence in postgresql"?
seancorfield 2021-03-09T04:15:30.002700Z
Are you talking about arrays? There's a section in the docs about that: https://cljdoc.org/d/seancorfield/next.jdbc/1.1.613/doc/getting-started/tips-tricks#working-with-arrays
seancorfield 2021-03-09T04:22:12.004100Z
And, in general, if you don't want to work with strings -- and the next.jdbc.sql functions don't do what you need -- the recommendation is always to use HoneySQL to produce the vector of SQL string and parameters.
jb recluse 2021-03-09T05:29:25.004200Z
(jdbc/execute! ds ["create sequence my_seq start 1 increment 2"])
=> [#:next.jdbc{:update-count 0}]
(jdbc/execute! ds ["create table my_tbl (id int primary key, my_col varchar)"])
=> [#:next.jdbc{:update-count 0}]
(jdbc/execute! ds ["insert into my_tbl values (nextval('my_seq'), ?)" "foo"])
=> [#:next.jdbc{:update-count 1}]
(jdbc/execute! ds ["select * from my_tbl"])
=> [#:my_tbl{:id 1, :my_col "foo"}]
(jdbc/execute! ds ["insert into my_tbl values (nextval('my_seq'), ?)" "bar"])
=> [#:next.jdbc{:update-count 1}]
(jdbc/execute! ds ["select * from my_tbl"])
=> [#:my_tbl{:id 1, :my_col "foo"} #:my_tbl{:id 3, :my_col "bar"}]jb recluse 2021-03-09T05:29:58.004400Z
oh, not even close, you wanted to use insert!
jb recluse 2021-03-09T05:30:01.004600Z
one sec
jb recluse 2021-03-09T05:41:17.004800Z
it appears that insert! expects all the values to be sent as ?-params, which is not how using seqs in postgres works afaik. so i dont think you'll be able to do this. however a suitably designed table doesnt need to explicitly use a sequence:
(jdbc/execute! ds ["create table my_serial_tbl (id serial primary key, my_col varchar)"])
=> [#:next.jdbc{:update-count 0}]
(require '[next.jdbc.sql :as sql])
=> nil
(sql/insert! ds :my_serial_tbl {:my_col "foo"})
=> #:my_serial_tbl{:id 1, :my_col "foo"}
(jdbc/execute! ds ["select * from my_serial_tbl"])
=> [#:my_serial_tbl{:id 1, :my_col "foo"}]jb recluse 2021-03-09T06:01:40.005Z
(def my-insert (-> (insert-into :my_tbl)
(values [{:id "nextval('my_seq')" :my_col "'foo'"}])
(sql/format :parameterizer :none)))
=> #'user/my-insert
(jdbc/execute! ds my-insert)
=> [#:next.jdbc{:update-count 1}]
(jdbc/execute! ds ["select * from my_tbl"])
=> [#:my_tbl{:id 1, :my_col "foo"} #:my_tbl{:id 3, :my_col "bar"} #:my_tbl{:id 5, :my_col "foo"}]seancorfield 2021-03-09T06:20:47.005200Z
@chetlind_clojure That's with V1. Not much of that applies with V2 which handles string inlining correctly, as well as also supporting nextval out of the box.
seancorfield 2021-03-09T06:24:18.005400Z
(-> (insert-into :my_tbl)
(values [{:id [:nextval "my_seq"] :my_col "foo"}])
(sql/format {:inline true}))