Fork me on GitHub
#sql
<
2019-08-18
>
dcj21:08:24

Having a problem, any advice/help appreciated! Using next.jdbc, hikari cp, component... Just like in the next.jdbc docs, I do (connection/->pool HikariDataSource db-spec) when I start my db component, let's say this is bound to c below... In my program/system, I am doing two different kinds of writes to the database: 1) (sql/insert! c ... the first arg of insert! takes a connectable, which AFAICT the result of ->pool satisfies.... 2) (prepare/execute-batch! (jdbc/prepare c [sql-str]) rows-to-insert), which fails because c is a connectable, but not a connection, which is what prepare needs. My initial fix was to replace (prepare c with (prepare (get-connection c), and that works for a very short time, and then I get HikariPool-1 - Connection is not available, request timed out after 30000ms. Clearly I am doing something wrong between my use of the result of ->pool, and the connectable/connection that execute! and prepare need, but haven't (yet) figured it out....

dcj22:08:34

OK, more info on the above issue... My app/system does the execute-batch! (prepare (get-connection c)... first, then after that returns it does the insert! c I finally figured out how to crank up the hikaricp logs to debug, and it seems that the execute-batch (prepare (get-connection is taking connections from the pool, and doesn't appear to return them. So after a few calls, all the connections are used up, and subsequent calls timeout waiting for a connection.... So, either I am using connection pool incorrectly EVERYWHERE, or I am using it incorrectly with execute-batch/prepare/get-connection

dcj22:08:52

A HA! This seems super important and relevant!

It is your responsibility to close the prepared statement after it has been used.

dcj22:08:23

AFAICT that solved my problem, FYI, here is what I needed to do:

(with-open [conn (jdbc/get-connection c)
                     ps   (jdbc/prepare conn [sql-str])]
      (prepare/execute-batch! ps rows))
Who could have known that reading the documentation would help? 🙂 🙂 🙂