CODEWITHSUNDEEP
postgresqljdbcclojure
Damon Snyder
Damon Snyder

Reputation: 1372

Is it possible to patch load SQL statements from a file using clojure.java.jdbc?

I need to be able to load and execute a file with multiple SQL statements in clojure. For example, lets say I have a file with statements like:

ALTER TABLE bla...;
ALTER TABLE foo...;
UPDATE bla SET ...;
UPDATE foo SET ...;
ALTER TABLE bla DROP...;
ALTER TABLE foo DROP...;

You get the idea-- a file with many statements that are semicolon terminated.

I'm currently getting the following error when trying to use do-commands:

PSQLException org.postgresql.util.PSQLException: Too many update results were returned.

Upvotes: 3

Views: 1678

Answers (2)

Binita Bharati
Binita Bharati

Reputation: 5908

I am using [org.clojure/java.jdbc "0.7.9"]. The accepted answer did not work for me. This is how I achieved the same feat :

My file content :

["insert into user (email , password, name, type) values ('[email protected]', 'password', 'Carlos Frank', 'admin')"
"insert into user (email , password, name, type) values ('[email protected]', 'password', 'Alice Williams', 'customer')"
"insert into user (email , password, name, type) values ('[email protected]', 'password', 'Piyush Goyal', 'customer')"]

Note that entire file content is enclosed within a vector, and each sql is double quoted.

The code that reads this file and populates DB :

(defn exec-sql-file  
   [file]  
     (jdbc/db-do-commands db-spec  (read-string (slurp file))))

Upvotes: 0

Damon Snyder
Damon Snyder

Reputation: 1372

The way I ended up solving this was like so:

(ns myns.db
  (:require [clojure.java.jdbc :as sql]            
            [clojure.java.io :refer [resource]]))

(defn db-conn [] ...)

(defn exec-sql-file  
   [file]  
   (sql/with-connection (db-conn)
    (sql/do-prepared
      (slurp (resource file)))))

...

; from your lein project where src/sql/some-statements.sql is the file you want to load
(exec-sql-file "sql/some-statements.sql")

I would be interested to hear how others have handled this problem. Is there a better way?

Upvotes: 6

Related Questions