<< Back to previous view

[JDBC-33] update-or-insert does not work with oracle Created: 21/May/12  Updated: 10/Jun/12  Resolved: 10/Jun/12

Status: Resolved
Project: java.jdbc
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Defect Priority: Minor
Reporter: Brian Tatnall Assignee: Sean Corfield
Resolution: Completed Votes: 0
Labels: bug
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))))))))


 Comments   
Comment by Sean Corfield [ 22/May/12 12:17 AM ]

Interesting. (.getUpdateCount stmt) is a standard part of the JDBC API so it looks reasonable to see if the result of the (.executeBatch stmt) is a single sequence with a -2 value, the code could call (.getUpdateCount stmt) and return that (single) value as a sequence. It looks like that should be completely portable but it will be hard to test since none of the other DBs return -2 as far as I know and I don't have an Oracle install to test against

Generated at Sun Jul 27 23:24:22 CDT 2014 using JIRA 4.4#649-r158309.