Fork me on GitHub
#sql
<
2018-01-08
>
markbastian16:01:45

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?

jgh16:01:38

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])

jgh16:01:53

oh i see you’re trying to do something specific

markbastian16:01:14

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.

markbastian16:01:13

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))))

jgh16:01:15

why are you trying to do it that way instead of passing it as a parameter to the query?

markbastian16:01:33

You may want to use @X somewhere else.

markbastian16:01:28

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.

itaied17:01:02

Someone knows of a good batching library like dataloader? https://github.com/facebook/dataloader

seancorfield18:01:00

@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?

seancorfield18:01:49

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).

markbastian18:01:05

I'm using [org.clojure/java.jdbc "0.7.3"].

markbastian18:01:13

Let me take a closer look.

markbastian18:01:35

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.

seancorfield18:01:00

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.

markbastian18:01:23

I'll try that.

seancorfield18:01:39

(as a first step to eliminate whether multiple connections are the issue)