This page is not created by, affiliated with, or supported by Slack Technologies, Inc.
2018-01-08
Channels
- # aleph (1)
- # architecture (4)
- # aws (5)
- # beginners (105)
- # boot (1)
- # boot-dev (72)
- # cider (5)
- # clara (15)
- # cljs-dev (51)
- # cljsrn (5)
- # clojure (155)
- # clojure-austin (3)
- # clojure-dusseldorf (2)
- # clojure-finland (1)
- # clojure-greece (37)
- # clojure-italy (17)
- # clojure-nl (1)
- # clojure-russia (6)
- # clojure-spec (23)
- # clojure-uk (6)
- # clojurescript (7)
- # community-development (1)
- # css (10)
- # cursive (15)
- # datomic (45)
- # defnpodcast (1)
- # duct (97)
- # emacs (5)
- # fulcro (46)
- # hoplon (8)
- # instaparse (25)
- # keechma (11)
- # leiningen (16)
- # off-topic (2)
- # onyx (9)
- # planck (2)
- # re-frame (5)
- # reagent (3)
- # reitit (2)
- # ring (6)
- # shadow-cljs (35)
- # spacemacs (9)
- # specter (9)
- # sql (18)
- # uncomplicate (4)
Got an interesting issue I was wondering if anyone in the community might be able to help me with. I'm trying to use clojure.jdbc to call a custom function in H2 with a user-defined variable. It doesn't work. Here's the code:
(for [q ["CALL ECHO('1')" ;Works
["CALL ECHO(?1)" "2" ] ;Works
"CALL ECHO(@X)"]] ;Fails
(let [db-spec {:classname "org.h2.Driver" :subprotocol "h2:mem" :subname "demo;" :user "sa" :password ""}
alias "CREATE ALIAS ECHO AS $$ String echo (String s) { return s; } $$"
;Need to keep a local connection handle due to how I spec the db.
c (j/get-connection db-spec)]
(try
;Create a local user variable
(j/db-do-commands db-spec [alias "SET @X='3'"])
;Call my custom function. Note that using the user variable fails. Why?
(j/query db-spec q)
(catch Throwable t {:error {:message (.getMessage t)}})
(finally (.close c)))))
Result:
=> (({:public.echo('1') "1"}) ({:public.echo(?1) "2"}) ({:public.echo(@x) nil}))
Any ideas why? TIA!
I realize this is Clojure and not Java, but the basic idea should be apparent --- I am trying to call a user-defined Java function with a user local variable via SET. Should this work in H2?im not an expert on jdbc but it seems to me you could simplify (if you’re really just trying to call that function and get a result) and do something like
(j/q db-spec ["select from echo(?)" param])
Yeah, the specific thing I'm trying to do is SET something and then use that thing as an argument in a user defined function.
To eliminate any confusion, this is just the failure mode:
(let [db-spec {:classname "org.h2.Driver" :subprotocol "h2:mem" :subname "demo;" :user "sa" :password ""}
alias "CREATE ALIAS ECHO AS $$ String echo (String s) { return s; } $$"
;Need to keep a local connection handle due to how I spec the db.
c (j/get-connection db-spec)]
(try
;Create a local user variable
(j/db-do-commands db-spec [alias "SET @X='3'"])
;Call my custom function. Note that using the user variable fails. Why?
(j/query db-spec "CALL ECHO(@X)")
(catch Throwable t {:error {:message (.getMessage t)}})
(finally (.close c))))
You may want to use @X somewhere else.
The super-simple case here removes all other statements, but in a more complicated example you might want to use the user defined var elsewhere.
Someone knows of a good batching library like dataloader
? https://github.com/facebook/dataloader
@itaied there's https://github.com/xsc/claro which is used as the backbone of https://github.com/alumbra
@markbastian You say you're using clojure.jdbc
-- do you mean clojure.java.jdbc
which is the Clojure Contrib JDBC wrapper or are you using another one?
Looking at the code above, I would not expect it to work unless you used the same connection across the set
and the call
(which you are not, as far as I can tell).
I'm using [org.clojure/java.jdbc "0.7.3"].
Let me take a closer look.
So, I was taking my direction from http://makble.com/using-h2-in-memory-database-in-clojure. If I make my example more like theirs, I still get the same issue:
(for [q ["CALL ECHO('1')" ;Works
["CALL ECHO(?1)" "2" ] ;Works
"CALL ECHO(@X)"]] ;Fails
(let [db-spec {:classname "org.h2.Driver"
:subprotocol "h2:mem"
:subname "demo;DB_CLOSE_DELAY=-1"
:user "sa"
:password ""}
alias "CREATE ALIAS ECHO AS $$ String echo (String s) { return s; } $$"]
;Create a local user variable
(j/db-do-commands db-spec [
;Only do this once since the db is persistent in the VM
;alias
"SET @X='3'"])
;Call my custom function. Note that using the user variable fails. Why?
(j/query db-spec q)))
The above gives the result:
=> (({:public.echo('1') "1"}) ({:public.echo(?1) "2"}) ({:public.echo(@x) nil}))
It sure seems like you can't use user defined variables in a custom Java function.I would try wrapping the commands with (j/with-db-connection [c db-spec] ... )
and use c
instead of db-spec
in the db-do-commands
and query
calls.
I'll try that.
(as a first step to eliminate whether multiple connections are the issue)