java.jdbc

update-or-insert does not work with oracle

Details

  • Type: Defect Defect
  • Status: Resolved Resolved
  • Priority: Minor Minor
  • Resolution: Completed
  • Affects Version/s: None
  • Fix Version/s: None
  • Component/s: None
  • Labels:
  • Environment:
    clojure 1.3.0
    clojure.java.jdbc 0.2.1
    oracle 10.2.0.4.0

Description

According to the oracle documentation

For a prepared statement batch, it is not possible to know the number of rows affected in the database by each individual statement in the batch. Therefore, all array elements have a value of -2. According to the JDBC 2.0 specification, a value of -2 indicates that the operation was successful but the number of rows affected is unknown.

As documented update-values returns (-2) which means that the (if (zero?)) check in update-or-insert-values will never return true.

Oracle does provide the number of rows updated via an the method getUpdateCount on instances of Statement.

The following change fixes this problem for oracle users, but I am not sure how it will affect other drivers.

(defn oracle-do-prepared
  "Executes an (optionally parameterized) SQL prepared statement on the                                           
  open database connection. Each param-group is a seq of values for all of                                        
  the parameters.                                                                                                 
  Return a seq of update counts (one count for each param-group)."
  [sql & param-groups]
  (with-open [^PreparedStatement stmt (prepare-statement (connection) sql)]
    (if (empty? param-groups)
      (transaction* (ƒ [] (vector (.executeUpdate stmt))))
      (do
        (doseq [param-group param-groups]
          (set-parameters stmt param-group)
          (.addBatch stmt))
        (transaction* (ƒ [] (let [result (.executeBatch stmt)
                                  count  (.getUpdateCount stmt)]
                              (vector count))))))))

Activity

Sean Corfield made changes -
Field Original Value New Value
Resolution Completed [ 1 ]
Status Open [ 1 ] Resolved [ 5 ]

People

Vote (0)
Watch (0)

Dates

  • Created:
    Updated:
    Resolved: