java.jdbc

There is no way to execute a raw statement outside a transaction

Details

  • Type: Defect Defect
  • Status: Resolved Resolved
  • Priority: Minor Minor
  • Resolution: Completed
  • Affects Version/s: None
  • Fix Version/s: None
  • Component/s: None
  • Labels:
    None
  • Environment:
    Dependencies used:

    [org.clojure/clojure "1.4.0"]
    [org.clojure/java.jdbc "0.2.3"]
    [org.xerial/sqlite-jdbc "3.7.2"]

Description

To be able to enforce foreign key constraints in SQLite you need to execute the following statement:

PRAGMA foreign_keys = ON;

However, this statement can't be used from within a transaction. From the SQLite docs:

It is not possible to enable or disable foreign key constraints in the middle of a multi-statement transaction (when SQLite is not in autocommit mode). Attempting to do so does not return an error; it simply has no effect.

In clojure.java.jdbc both do-commands and do-prepared unconditionally run the supplied statements in a transaction. To me it seems like these two functions are the only ones that provide a way to execute an arbitrary SQL statement.

Example using [org.clojure/java.jdbc "0.2.3"] and [org.xerial/sqlite-jdbc "3.7.2"]:

(sql/with-connection {:classname   "org.sqlite.JDBC"
                      :subprotocol "sqlite"
                      :subname     "foo.db"}
  (sql/do-commands "PRAGMA foreign_keys = ON;")
  (sql/with-query-results res ["PRAGMA foreign_keys;"]
    (doall res)))
; => ({:foreign_keys 0})

When using this function instead of do-commands foreign keys work as expected:

(defn do-raw  [& commands]
  (with-open [^java.sql.Statement stmt (let [^java.sql.Connection con (sql/connection)] (.createStatement con))]
    (doseq [^String cmd commands]
      (.addBatch stmt cmd))
    (let [result (.executeBatch stmt)]
      (if (and (= 1 (count result)) (= -2 (first result)))
        (list (.getUpdateCount stmt))
        (seq result)))))

This function is simply do-commands with execute-batch inlined and the wrapping transaction form removed.

Activity

Hide
Rasmus Svensson added a comment -

Neat!

Show
Rasmus Svensson added a comment - Neat!
Hide
Sean Corfield added a comment -

0.2.4-SNAPSHOT contains db-do-commands which allows bare execution of commands against a specific database connection with or without a transaction wrapping the commands.

Show
Sean Corfield added a comment - 0.2.4-SNAPSHOT contains db-do-commands which allows bare execution of commands against a specific database connection with or without a transaction wrapping the commands.
Hide
Sean Corfield added a comment -

It seems like the more flexible thing to do would be to allow users to specify that a transaction not be used on the existing operations. I'm reworking the API substantially soon so I'll bear that in mind. I might not make it possible with the existing API but I'll make sure that's an option in the new API that I'm adding.

Show
Sean Corfield added a comment - It seems like the more flexible thing to do would be to allow users to specify that a transaction not be used on the existing operations. I'm reworking the API substantially soon so I'll bear that in mind. I might not make it possible with the existing API but I'll make sure that's an option in the new API that I'm adding.

People

Vote (0)
Watch (1)

Dates

  • Created:
    Updated:
    Resolved: