Clojure / SQLServer: How to Call Stored Procedure with C3P0 Connection Pool
c3p0, clojure, sql-server
Solution
For basic stored procedures that don't require OUT parameters, you can use `db-do-prepared`.
(require '[clojure.java.jdbc :as j])
(j/db-do-prepared (connection) "EXEC YourStoredProc ?" [COLUMN_NAME])
It calls your `connection` function, which is the same one that the documentation says to call.
I've started working on adding full callable statement support to JDBC, but I haven't had the time to finish the work. It is issue JDBC-48 in Clojue's JIRA, and my progress is in the sprocs branch of my fork.
Problem
Following this example https://github.com/clojure/java.jdbc/blob/master/doc/clojure/java/jdbc/ConnectionPooling.md of jdbc connection pooling, I have set up a connection pool in a Clojure app to a SQLServer as follows ``` ;; Database Connection Handling. (ns myapp.db (:import [com.mchange.v2.c3p0 ComboPooledDataSource])) ;; ### specification ;; Defines the database connection parameters. (def specification { :classname "com.microsoft.sqlserver.jdbc.SQLServerDriver" :subprotocol "sqlserver" :subname "//some;info;here" }) ;; ### pooled-data-source ;; Creates a database connection pool using the ;; <a href="https://github.com/swaldman/c3p0">c3p0</a> JDBC ;; connection pooling library. (defn pooled-data-source [specification] (let [datasource (ComboPooledDataSource.)] (.setDriverClass datasource (:classname specification)) (.setJdbcUrl datasource (str "jdbc:" (:subprotocol specification) ":" (:subname specification))) (.setUser datasource (:user specification)) (.setPassword datasource (:password specification)) (.setMaxIdleTimeExcessConnections datasource (* 30 60)) (.setMaxIdleTime datasource (* 3 60 60)) {:datasource datasource})) ;; ### connection-pool ;; Creates the connection pool when first called. (def connection-pool (delay (pooled-data-source specification))) ;; ### connection ;; Get a connection from the connection pool. (defn connection [] @connection-pool) ``` I get how to use the connection to make select and insert statements etc, my question is how would I use it to call a stored procedure and collect the output, which may be records of various shapes and sizes? ``` ;; ### Definitions of queries. (ns myapp.query (:require [myapp.db])) ;; HOW DO I CALL THIS PROC THROUGH A POOLED CONNECTION? (defn call-the-stored-proc [] (str "{ call someStoredProcForMyApp("...")}")) ```