funcool / clojure.jdbc Goto Github PK

How can I reuse the same connection to run multiple SQL commands? When I eval my (build-schema) defined below, I get the error:

java.sql.SQLException: You can't operate on a closed Connection!!!

Here is a mock schema using SQLingvo:

(def my-schema [
    (sql (create-table :users
                  (column :id :uuid :primary-key? true)
                  (column :email :text)))
    (sql (create-table :publishers
                  (column :id :uuid :primary-key? true)
                  (column :name :text :not-null? true)))])

Here is my build-schema function that doseq's over my schema:

(defn build-schema []
  (let [conn (make-connection dbspec)]
    (tx/with-transaction conn
      (with-catch
        (doseq [[cmd] my-schema]
          (execute! conn cmd))
        (.close conn)))))

Hi, I am the lead developer of HikariCP and I have a question about the documentation. The documentation section for HikariCP starts with:

This adapter does not respects the standard dbspec format, it has its own format and depends
completely of used adapter. This is happens because HikariCP works as some kind of wrapper and it
forward almost all parameters to the wrapped datasource.

I am not sure what this means? All connection pools, including C3P0 and DBCP, "wrap" datasources. HikariCP wraps datasources in exactly the same way as other pools and should be just as transparent (indistinguishable from them).

Is the difference between clojure.jdbc support for HikariCP caused by a third-party adapter that is not dbspec compliant?

Also, HikariCP adapter targets to only JDK8 version.

HikariCP has both Java 6/7 support and Java 8 support via different JAR artifacts.

Thanks.

Would be great to have a file like

;; clojure.java-time

  (:require
    [java-time]
    [jdbc.proto :refer [ISQLType ISQLResultSetReadColumn]])

; java.time.LocalDate     - java.sql.Date
; java.time.LocalDateTime - java.sql.Timestamp
; java.time.LocalTime     - java.sql.Time

(extend-protocol ISQLType
  ;
  java.time.LocalDate
  (as-sql-type [this conn]
    (java-time/sql-date this))
  (set-stmt-parameter! [this conn stmt index]
    (.setDate stmt index
      (java-time/sql-date this)))

  java.time.LocalTime
  (as-sql-type [this conn]
    (java-time/sql-time this))
  (set-stmt-parameter! [this conn stmt index]
    (.setTime stmt index
      (java-time/sql-time this)))

  java.time.LocalDateTime
  (as-sql-type [this conn]
    (java-time/sql-timestamp this))
  (set-stmt-parameter! [this conn stmt index]
    (.setTimestamp stmt index
      (java-time/sql-timestamp this))))
  ;
;

(extend-protocol ISQLResultSetReadColumn
  ;
  java.sql.Timestamp
  (from-sql-type [this conn metadata index]
    (java-time/local-date-time this))
  ;
  java.sql.Date
  (from-sql-type [this conn metadata index]
    (java-time/local-date this))
  ;
  java.sql.Time
  (from-sql-type [this conn metadata index]
    (java-time/local-time this)))
;

to make java.time support easier.

Can you please remove or create an overridable setting for the lower casing of identifiers? This behavior is unexpected and not necessarily desirable in all cases.

identifiers str/lower-case

My GMT is +2. It seems that the library is offsetting times without time zones even when I store UTC times.

Given this Postgres table,

create table schedules (
  time_of_day time without time zone not null
)

I can execute INSERT schedules (time_of_day) VALUES (time '09:00:00') and retrieve the 09:00:00 time with a select query in psql:

> select time_of_day from schedules;
time_of_day 
-------------
 09:00:00
(1 row)

But when I retrieve this query with a fetch from clojure.jdbc, this comes back: :time_of_day #inst "1970-01-01T07:00:00.000-00:00" where the time is 07:00:00.

Am I doing something wrong? Is there a way to avoid this?

With version 0.9.0, the following code gets a reflection warning:

(with-open [conn (jdbc/connection my-connection-pool)]
   (do-anything conn))

I'm able to remove the reflection warnings by changing that to:

(with-open [^java.io.Closeable conn (jdbc/connection my-connection-pool)]
   (do-anything conn))

But I'm unsure whether there's a more specific type I should be hinting. If that's the right hint, would you take a PR adding the type hint to the implementation of jdbc/connection?

[funcool/clojure.jdbc "0.9.0"]

There are some reflection warnings reported:

Reflection warning, jdbc/types.clj:43:19 - reference to field getConnection on java.lang.Object can't be resolved.             
Reflection warning, jdbc/types.clj:47:5 - reference to field close on java.lang.Object can't be resolved.                      
Reflection warning, jdbc/impl.clj:322:33 - reference to field stmt can't be resolved.      

I get the subject exception when I try to execute (jdbc/execute conn "show tables;").

Here is the stack trace:

        JdbcStatement.java:  676  org.h2.jdbc.JdbcStatement/executeBatch
                  impl.clj:  136  jdbc.impl/eval55684/fn
                 proto.clj:   26  jdbc.proto/eval55316/fn/G
                  core.clj:  118  jdbc.core/execute
                  core.clj:  108  jdbc.core/execute
                  core.clj:  115  jdbc.core/execute
                  core.clj:  108  jdbc.core/execute
               db_test.clj:   19  mdg.db-test/fn
               db_test.clj:   17  mdg.db-test/fn
                  test.clj:  153  cider.nrepl.middleware.test/test-var/fn
                  test.clj:  153  cider.nrepl.middleware.test/test-var
                  test.clj:  145  cider.nrepl.middleware.test/test-var
                  test.clj:  169  cider.nrepl.middleware.test/test-vars/fn/fn
                  test.clj:  686  clojure.test/default-fixture
                  test.clj:  682  clojure.test/default-fixture
                  test.clj:  169  cider.nrepl.middleware.test/test-vars/fn
               db_test.clj:   14  mdg.db-test/eval55968/fn
                  test.clj:  693  clojure.test/compose-fixtures/fn/fn
                  test.clj:  686  clojure.test/default-fixture
                  test.clj:  682  clojure.test/default-fixture
                  test.clj:  693  clojure.test/compose-fixtures/fn
                  test.clj:  165  cider.nrepl.middleware.test/test-vars
                  test.clj:  159  cider.nrepl.middleware.test/test-vars
                  test.clj:  182  cider.nrepl.middleware.test/test-ns
                  test.clj:  173  cider.nrepl.middleware.test/test-ns
                  test.clj:  196  cider.nrepl.middleware.test/test-nss
                  test.clj:  187  cider.nrepl.middleware.test/test-nss
                  test.clj:  241  cider.nrepl.middleware.test/handle-test/fn/fn
                  AFn.java:  152  clojure.lang.AFn/applyToHelper
                  AFn.java:  144  clojure.lang.AFn/applyTo
                  core.clj:  646  clojure.core/apply
                  core.clj: 1881  clojure.core/with-bindings*
                  core.clj: 1881  clojure.core/with-bindings*
               RestFn.java:  425  clojure.lang.RestFn/invoke
                  test.clj:  238  cider.nrepl.middleware.test/handle-test/fn
    interruptible_eval.clj:  190  clojure.tools.nrepl.middleware.interruptible-eval/run-next/fn
                  AFn.java:   22  clojure.lang.AFn/run
   ThreadPoolExecutor.java: 1142  java.util.concurrent.ThreadPoolExecutor/runWorker
   ThreadPoolExecutor.java:  617  java.util.concurrent.ThreadPoolExecutor$Worker/run
               Thread.java:  745  java.lang.Thread/run

Does this library require Java 7? It seems that java.lang.AutoCloseable is Java 7 and later. If so, please add this to the docs. Thanks.

rds-test> (jdbc.core/fetch conn ["select haNetworkTimeout from InstanceGroup"])
[{:hanetworktimeout 5}]

the column name is : "haNetworkTimeout", but result key is : "hanetworktimeout"

why do this conversion ?

I'm getting this:

(with-connection {:datasource -pooled-db-spec-} db
  (query db ["SELECT 3 * 2;"]))
=>
AssertionError Assert failed: (vector? sql-with-params)  jdbc/make-query (jdbc.clj:416)

In documentation:

This is a list of supported options:

• :read-uncommited - Set read uncommited isolation level
• :read-commited - Set read committed isolation level

In code:

{
   :read-uncommitted Connection/TRANSACTION_READ_UNCOMMITTED
   :read-committed   Connection/TRANSACTION_READ_COMMITTED
}

See the difference in double “t”.

e.g.

user=> (with-open [conn (jdbc/connection "derby:/path/to/db;user=dot;password=D^1@?pw*qwK")] (jdbc/execute conn "insert into foo values ('hello')"))
URISyntaxException Illegal character in path at index 37: derby:/path/to/db;user=dot;password=D^9@?pw*qwK  java.net.URI$Parser.fail (URI.java:2848)

user=> (with-open [conn (jdbc/connection "derby:memory:derby2pg;create=true")] "create table foo(bar varchar(10))")
SQLException No suitable driver found for jdbc:derby://  java.sql.DriverManager.getConnection (DriverManager.java:689)

Both of these urls work fine e.g.

user=> (with-open [conn (DriverManager/getConnection "jdbc:derby:/path/to/db;user=dot;password=D^9@?pw*qwK")] )
nil

user=> (with-open [conn (DriverManager/getConnection "jdbc:derby:memory:foobacle;create=true")] )
nil

In the first case, it looks like the code that attempts to make the connection is passing the string to java's URI class and fails because of some character in the password. This isn't that big of a deal for me to work around, because I can create the connections with DriverManager and just pass them on to the library. However, it seems like rather than forcing users of the library to do that, the library should not try to build a URI out of the raw string and simply create the connection based on the raw string.

Hi,

Just wanted to ask - I've seen that docs are specifying to use 0.13.0 version of hikari-cp. The latest version of hikari-cp, advertised in README for it appears to be 1.1.1. Is it maybe the case that docs for clojure.jdbc should be updated or is there maybe a reason why previous version of hikari-cp is preferred?

Thanks! :)

I'm the author of HugSQL, which provides an adapter which uses clojure.jdbc, and I'd like to see support for JDBC's .getGeneratedKeys such that there is parity between clojure.java.jdbc and clojure.jdbc for inserts returning auto-generated IDs.

I noticed that .getGeneratedKeys is currently commented out with a TODO, so I'm assuming there is an intention to support this.

Are there any concerns you have in supporting this?

Thanks!

The official guide could be enhanced with some mentions of :identifiers and :as-rows?.

Specifically, I recall I spent some time before I figured out how to "kebab-case-keyword"-ize the fetched results as there's nothing about it directly in jdbc.core/fetch docstring (they are mentioned in jdbc.resultset docs):

(ns foo.bar
  (:require [camel-snake-kebab.core :as csk]
                 [jdbc.core :as jdbc]))
...
(jdbc/fetch conn sql {:identifiers csk/->kebab-case-keyword})

would you like to add :classname to db spec? I sent you PR

Would you welcome a namespace adding basic functionality for database metadata retrieval?

Using clojure.jdbc w/ Hikari CP and Postgresql 11.

When attempting to create a cursor as described in the docs the process still ends up trying to load the whole result set into memory.

(def conn (jdbc/connection pool))
(def cursor (jdbc/fetch-lazy conn "SELECT \"uuid\", data_bytes FROM visitor_data;"))
(def cursor-seq (jdbc/cursor->lazyseq cursor)) ;; hangs here

If I explicitly set auto-commit and the fetch size I don't have that problem:

(def conn (jdbc/connection pool))
(def cursor (jdbc/fetch-lazy conn "SELECT \"uuid\", data_bytes FROM visitor_data;"))
(.setFetchSize (.-stmt cursor) 50)
(.setAutoCommit (.getConnection (.-stmt cursor)) false)
(def cursor-seq (jdbc/cursor->lazyseq cursor))

is this a documentation problem or is the library expected to modify those settings for us?

javax.sql.DataSource implements the proto/IConnection protocol, but it's difficult to see how directly using it like that is anything but a misfeature. This protocol implementation lets you do the following:

(jdbc/fetch datasource "select whatever from whatever;")

If you do this, the connection that gets created will never get closed. You need to wrap the whole thing in a with-open, jdbc/connection like this:

(with-open [conn (jdbc/connection datasource)]
  (jdbc/fetch conn "select whatever from whatever;"))

Seeing as how the first snippet is always a bad idea, it shouldn't be possible to write the first and have it succeed at all. If we're supposed to go through the connection wrapper jdbc/connection, then the api should force us to do exactly that and not silently succeed when misused.

It looks like potemkin is not used anymore and there were some plan to remove it. Maybe now is the time?