Fork me on GitHub
#sql
<
2018-05-09
>
grav10:05:58

If I make a connection from a db-string (eg jdbc:h2:mem:test), I have to use prepared-statements with jdbc/query, else I get an error : actual: java.lang.IllegalArgumentException: db-spec conn8: url=jdbc:h2:mem:test user= is missing a required parameter If I use jdbc/query with the db-string directly, it works.

grav10:05:59

To re-iterate:

(let [db-string "jdbc:h2:mem:test"]
  (jdbc/query db-string "SELECT 1"))
=> ({:1 1})
(let [conn (jdbc/get-connection "jdbc:h2:mem:test")]
  (jdbc/query conn "SELECT 1"))
IllegalArgumentException db-spec conn10: url=jdbc:h2:mem:test user= is missing a required parameter  clojure.java.jdbc/get-connection (jdbc.clj:379)
(let [conn (jdbc/get-connection "jdbc:h2:mem:test")]
  (jdbc/query conn (jdbc/prepare-statement conn "SELECT 1")))
=> ({:1 1})

grav10:05:09

Same experience with a MSSQL base

grav10:05:54

(Btw: Reason I'm using db-strings and not db-specs is that I found no way to specify loginTimeout and queryTimeout with a db-spec)

seancorfield17:05:37

@grav If you're using the latest java.jdbc, the following works: {:dbtype "h2:mem" :dbname "test" :loginTimeout 10 :queryTimeout}

seancorfield17:05:41

See

user=> (def db-spec {:dbtype "h2:mem" :dbname "test" :loginTimeout 10 :queryTimeout 10})
#'user/db-spec
user=> (require '[clojure.java.jdbc :as jdbc])
nil
user=> (jdbc/query db-spec "SELECT 1")
({:1 1})

seancorfield17:05:41

(any additional elements in the map should be passed into the JDBC connection when it is created)

seancorfield17:05:03

If those extra elements are not being passed into the connection, that's a bug.

seancorfield17:05:43

You really shouldn't use get-connection directly (and you should .close each connection you get that way). The other patterns in java.jdbc manage that for you automatically.

seancorfield17:05:38

The result of get-connection is a raw JDBC connection object, which is not acceptable to query -- that's why you should use with-db-connection or the raw spec. query expects something that is a db-spec which a raw connection is not. You could wrap it in a hash map tho':

user=> (let [db-string "jdbc:h2:mem:test"
             conn (jdbc/get-connection db-string)]
  (jdbc/query (jdbc/add-connection db-string conn) "SELECT 1"))
({:1 1})
That is what happens under the hood (well, and .close is called on the connection).

seancorfield17:05:39

You can see your call is illegal by using the optional specs provided by java.jdbc:

user=> (require '[clojure.spec.alpha :as s] '[clojure.spec.test.alpha :as st])
nil
user=> (require '[clojure.java.jdbc.spec])
nil
user=> (st/instrument 'clojure.java.jdbc/query)
[clojure.java.jdbc/query]
user=> (let [conn (jdbc/get-connection "jdbc:h2:mem:test")]
  (jdbc/query conn "SELECT 1"))
ExceptionInfo Call to #'clojure.java.jdbc/query did not conform to spec:
In: [0] val: #object[org.h2.jdbc.JdbcConnection 0x37fdfb05 "conn11: url=jdbc:h2:mem:test user="] fails spec: :clojure.java.jdbc.spec/db-spec-connection at: [:args :db :connection] predicate: map?
In: [0] val: #object[org.h2.jdbc.JdbcConnection 0x37fdfb05 "conn11: url=jdbc:h2:mem:test user="] fails spec: :clojure.java.jdbc.spec/db-spec-friendly at: [:args :db :friendly] predicate: map?
In: [0] val: #object[org.h2.jdbc.JdbcConnection 0x37fdfb05 "conn11: url=jdbc:h2:mem:test user="] fails spec: :clojure.java.jdbc.spec/db-spec-raw at: [:args :db :raw] predicate: map?
In: [0] val: #object[org.h2.jdbc.JdbcConnection 0x37fdfb05 "conn11: url=jdbc:h2:mem:test user="] fails spec: :clojure.java.jdbc.spec/db-spec-driver-manager at: [:args :db :driver-mgr] predicate: map?
In: [0] val: #object[org.h2.jdbc.JdbcConnection 0x37fdfb05 "conn11: url=jdbc:h2:mem:test user="] fails spec: :clojure.java.jdbc.spec/db-spec-factory at: [:args :db :factory] predicate: map?
In: [0] val: #object[org.h2.jdbc.JdbcConnection 0x37fdfb05 "conn11: url=jdbc:h2:mem:test user="] fails spec: :clojure.java.jdbc.spec/db-spec-data-source at: [:args :db :datasource] predicate: map?
In: [0] val: #object[org.h2.jdbc.JdbcConnection 0x37fdfb05 "conn11: url=jdbc:h2:mem:test user="] fails spec: :clojure.java.jdbc.spec/db-spec-jndi at: [:args :db :jndi] predicate: map?
In: [0] val: #object[org.h2.jdbc.JdbcConnection 0x37fdfb05 "conn11: url=jdbc:h2:mem:test user="] fails spec: :clojure.java.jdbc.spec/db-spec-string at: [:args :db :uri-str] predicate: string?
In: [0] val: #object[org.h2.jdbc.JdbcConnection 0x37fdfb05 "conn11: url=jdbc:h2:mem:test user="] fails spec: :clojure.java.jdbc.spec/uri at: [:args :db :uri-obj] predicate: (instance? java.net.URI %)
  clojure.core/ex-info (core.clj:4739)
user=> 

seancorfield17:05:25

BTW @grav If you can point me at tutorials online that show use of get-connection directly, I can try to get them fixed. I'm pretty certain that nothing in the documentation encourages use of get-connection: http://clojure-doc.org/articles/ecosystem/java_jdbc/home.html

seancorfield17:05:23

I've updated the get-connection docstring so that'll be reflected in the API Reference (autodoc) stuff on http://clojure.github.io/java.jdbc at some point soon-ish.

Drew Verlee20:05:02

Any ideas why calling my function from clojure would work fine but calling it from java would result in a jdbc: where everything in the <> is filled in the the names i need. I can’t think how how it could be different. If its a typo im going to be a little sick

seancorfield21:05:00

Without seeing your code @drewverlee it's impossible for us to even begin to guess what is happening!

Drew Verlee21:05:44

@seancorfield point taken. I was hoping that knowing it works in clojure but not in the java environment would narrow the field down. Here is the code. But the deps is referencing a local uberjar. I can probably fix that though. https://github.com/drewverlee/fk-gen-java/blob/0d21eba8b2636df0a47f4e7428a382cf953cd088/src/test/java/mainTest.java#L21

seancorfield21:05:35

And what does "work fine" mean in this context?

seancorfield21:05:27

Hah, I see the problem... print out the args String in the Java program and I think you'll see the problem pretty quickly too!

seancorfield21:05:30

Hmm, maybe not. I thought it was reading the args string incorrectly due to lack of whitespace between the map string values and the following keyword, but when I tried it in the REPL it seemed to work.

seancorfield21:05:05

(I'm going to assume Clojure.read() works the exact same way as clojure.core/read-string)

seancorfield21:05:57

> I was hoping that knowing it works in clojure but not in the java environment would narrow the field down. This just "narrows it down" to "You're doing something wrong somewhere!" :rolling_on_the_floor_laughing: