Fork me on GitHub
#sql
<
2020-03-28
>
pdmct05:03:55

Hi, I am trying to update a table in a postgres db with next-jdbc using the update! fn as follows:

(defn ->table [s]
  (let [_ (debug (str "->table:" s))]
  (str/replace s #"-" "_")))

(defn ->col [s]
  (let [_ (debug (str "->col:" s))]
    (str/replace
     (str/replace s #"-" "")
     #"\."
     "_")))

(defn update-tasks [db tasks]
  (let [ds (jdbc/get-datasource db)
        results (f/try*
                 (doseq
                     [t tasks]
                   (sql/update! ds :my-tasks t  {:table-fn ->table
                                                 :column-fn ->col})))]
    (if (f/failed? results)
      results
      tasks)))
but get this exception:
Can't infer the SQL type to use for an instance of api.resources.db$__GT_table. Use setObject() with an explicit Types value to specify the type to use.
org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of vms_api.resources.db$__GT_table. Use setObject() with an explicit Types value to specify the type to use.
 at org.postgresql.jdbc.PgPreparedStatement.setObject (PgPreparedStatement.java:978)
I'm not sure, how the :table-fn is getting down into the postgres jdbc driver as a value. I am using this successfully with an insert! fn that looks almost identical. Am I doing something wrong here?

seancorfield05:03:57

@pdmct You have the wrong arguments to update!

seancorfield05:03:08

It takes a connectable (`ds`), a table (`:my-tasks`), a map of updates (`t`), a where map or clause -- which is missing -- and then then options.

pdmct05:03:44

ok, I see

seancorfield05:03:19

update! takes one more argument than insert! 🙂

seancorfield05:03:59

(I must admit, I was confused by that error message at first!)

pdmct05:03:27

yes, that isn't obvious , I have been scratching my head for while on this. so some more like this:

(sql/update! ds :analytics-tasks t {:id (:id t)}
                                {:table-fn ->table
                                 :column-fn ->col}))

pdmct05:03:00

that's much better

seancorfield05:03:19

Yup, that will perform the update WHERE id = ? ... (:id t)

pdmct05:03:34

cool, thanks

seancorfield05:03:09

The problem is that it couldn't tell the difference between the map {:id (:id t)} and the map {:table-fn ->table :column-fn ->col} since both are maps and options are ... optional.

seancorfield05:03:26

So even next.jdbc.specs couldn't have helped you here.

pdmct05:03:30

yes, I see that now. thanks for the quick response