clojureclojure-contrib

Clojure db-do-prepared calling with multiple parameters


I see following example in Clojure.java.jdbc

(sql/db-do-prepared db "INSERT INTO fruit2 ( name, appearance, cost, grade ) VALUES ( ?, ?, ?, ? )" ["test" "test" 1 1.0])

But how do i convert following java code into clojure. I am new to clojure and not sure how to i pass multiple vector

final int numRows = 10000;
    PreparedStatement pstmt = conn
        .prepareStatement("insert into new_order values (?, ?, ?)");
    for (int id = 1; id <= numRows; id++) {
      pstmt.setInt(1, id % 98);
      pstmt.setInt(2, id % 98);
      pstmt.setInt(3, id);
      int count;
      if ((count = pstmt.executeUpdate()) != 1) {
        System.err.println("unexpected update count for a single insert " + count);
        System.exit(2);
      }
      if ((id % 500) == 0) {
        System.out.println("Completed " + id + " inserts ...");
      }
    }

Solution

  • for multiple vectors, the function is varargs:

    (sql/db-do-prepared db "INSERT INTO fruit2 ( name, appearance, cost, grade ) VALUES ( ?, ?, ?, ? )" ["test" "test" 1 1.0] ["test" "test" 2 3.0])
    

    If you want to generate the input from a list, you can use apply:

    (apply sql/db-do-prepared db "INSERT INTO fruit2 ( name, appearance, cost, grade ) VALUES ( ?, ?, ?, ? )" (for [i (range 10)] ["test" "test" i 1.0]))
    

    For a literal reproduction of that logic, you cannot use the varargs, because it does not give you a chance to check each return value before the next operation:

    (let [num-rows 1000
          success
          (reduce
           (fn [state id]
             (let [values [(mod id 98)
                           (mod id 98)
                           id]
                   [result] (sql/do-prepared "insert into test values (?)" values)]
               (if (not= result 1)
                 {:ok id}
                 (reduced {:error result}))))
           (range 1 (inc num-rows)))]
      (if-let [id (:ok success)]
        (println "Completed" id "inserts")
        (do (println "unexpected update count for a single insert" (:error success))
            (System/exit 2))))