This page is not created by, affiliated with, or supported by Slack Technologies, Inc.
2018-05-04
Channels
- # architecture (27)
- # bangalore-clj (4)
- # beginners (22)
- # boot (35)
- # cider (26)
- # cljs-dev (2)
- # cljsrn (3)
- # clojure (156)
- # clojure-austria (3)
- # clojure-dev (9)
- # clojure-italy (25)
- # clojure-nl (10)
- # clojure-poland (5)
- # clojure-sanfrancisco (1)
- # clojure-spec (6)
- # clojure-uk (64)
- # clojurescript (169)
- # core-async (13)
- # cursive (13)
- # datomic (63)
- # dirac (50)
- # duct (21)
- # editors (1)
- # emacs (6)
- # events (1)
- # fulcro (1)
- # java (22)
- # keechma (14)
- # leiningen (2)
- # luminus (4)
- # off-topic (23)
- # onyx (4)
- # parinfer (5)
- # pedestal (4)
- # re-frame (6)
- # reagent (4)
- # ring-swagger (7)
- # rum (4)
- # shadow-cljs (84)
- # specter (5)
- # sql (36)
- # tools-deps (76)
- # uncomplicate (3)
- # yada (4)
I've written a thing to generate CREATE INDEX statements using honeysql. The final SQL list destined for jdbc looks sane to me...
["CREATE UNIQUE INDEX IF NOT EXISTS dfidx_docufant_email__account__uniq ON docufant ((_data -> ?)) WHERE _type = ?"
"email"
"account"]
Two ?
, two values after the query in the list.
But I get the error
org.postgresql.util.PSQLException: ERROR: there is no parameter $2
Position: 113
java.sql.BatchUpdateException: Batch entry 0 CREATE UNIQUE INDEX IF NOT EXISTS dfidx_docufant_email__account__uniq ON docufant ((_data -> 'email')) WHERE _type = 'account' was aborted: ERROR: there is no parameter $2
Position: 113 Call getNextException to see other errors in the batch.
when executing it.
If I run the query directly unparametrized it's fine.@xlevus How are you actually executing that generated SQL?
(and what does that weird-looking syntax ((_data -> 'email'))
actually mean??)
@seancorfield it's a postgres operator for getting an item out of a json object/array https://www.postgresql.org/docs/current/static/functions-json.html
@tanzoniteblack use the plain function instead of syntatic sugar https://stackoverflow.com/questions/38370972/how-do-i-use-postgresql-jsonb-operators-containing-a-question-mark-via-jdb
@myguidingstar 🙂 I'm not the one with an issue. And I don't think he's trying to use the ?
operator. He's using the ->
operator: (_data -> ?)
sorry I misread your question
and the ?
is supposed to be a parameter insertion
I suspect the problem here is in how @xlevus actually executes the SQL. It's using BatchUpdateException which suggests he's using execute!
but I don't think all drivers allow DDL to be executed that way. I think db-do-commands
is needed here.
Ah, but db-do-commands
doesn't accept SQL parameters, just SQL statements (for DDL).
and the index object in question would look like {:type :account :path [:email] :unique true}
@xlevus Can you share enough of the stacktrace so I can tell exactly what in java.jdbc
is throwing that exception? Also, which version of java.jdbc
are you using?
(jdbc/with-db-transaction [conn (config/db)]
(jdbc/execute! conn ["CREATE TABLE test_table (json_column jsonb, data_type text);"])
(jdbc/execute! conn ["CREATE UNIQUE INDEX IF NOT EXISTS test_uniq ON test_table((json_column -> 'field')) WHERE (data_type = 'dog');"]))
so for me this worksbut with the param:
the code that generated that exception:
(jdbc/with-db-transaction [conn (config/db)]
(jdbc/execute! conn ["CREATE TABLE test_table (json_column jsonb, data_type text);"])
(jdbc/execute! conn ["CREATE UNIQUE INDEX IF NOT EXISTS test_uniq ON test_table((json_column -> 'field')) WHERE (data_type = ?);" "dog"]))
the second message came from a slightly different query, hence the complaint about $2
(jdbc/with-db-transaction [conn (config/db)]
(jdbc/execute! conn ["CREATE TABLE test_table (json_column jsonb, data_type text);"])
(jdbc/execute! conn ["CREATE UNIQUE INDEX IF NOT EXISTS test_uniq ON test_table((json_column -> ?)) WHERE (data_type = ?);" "dog" "cat"]))
I wonder if there's something about the Hikari proxy causing trouble?
OK, good to eliminate that as the source of a problem.
And which pg driver are you using?
https://github.com/xlevus/docufant-clj/blob/master/project.clj
[org.clojure/clojure "1.9.0"]
[org.clojure/java.jdbc "0.7.3"]
[org.postgresql/postgresql "42.1.4"]
removing another source of confusion, it's not related to the json stuff:
(jdbc/with-db-transaction [conn (config/db)]
(jdbc/execute! conn ["CREATE TABLE test_table (json_column text, data_type text);"])
(jdbc/execute! conn ["CREATE UNIQUE INDEX IF NOT EXISTS test_uniq ON test_table(json_column) where data_type = ?;" "data_type"]))
has the same errorand I'm testing this with [org.postgresql/postgresql "42.2.0"]
maybe it's postgres.
docufant_test> PREPARE dfidx(a, b) AS
CREATE UNIQUE INDEX IF NOT EXISTS dfidx_docufant_email__account__uniq ON docufant ((_data -> $1)) WHERE _type = $2;
syntax error at or near "CREATE"
LINE 2: CREATE UNIQUE INDEX IF NOT EXISTS dfidx_docufant_email__ac...
^
Time: 0.005s
My gut instinct says you can't run DDL with parameters through a PreparedStatement but I don't know if that's true.
@seancorfield that's the same thing I'm thinking. Can't find anything either way on the topic so far
I think that's the origin of why db-do-commands
doesn't use a prepared statement -- it just creates a basic statement based on the SQL (only, no parameters).