insert nulls using .setNull (e.g. for Teradata)


  • Type: Enhancement Enhancement
  • Status: Closed Closed
  • Priority: Major Major
  • Resolution: Completed
  • Affects Version/s: None
  • Fix Version/s: None
  • Component/s: None
  • Labels:
  • Environment:
    Teradata 14
  • Patch:


Hi, I am using this nice package with a large (government) Teradata machine. I found that I can not insert nil values using insert-rows: the Teradata JDBC driver tells me to use .setNull or the form of .setObject including an SQL type code.

The following replacement definition of set-parameters was the only change needed to get this to work.

(defn- set-parameters
  "Add the parameters to the given statement."
  [^PreparedStatement stmt params]
  (let [pmd (.getParameterMetaData stmt)]
      (fn [ix value]
        (let [typ (.getParameterType pmd (inc ix))]
          (if (nil? value)
            (.setNull stmt (inc ix) typ)
            (.setObject stmt (inc ix) value typ))))

I am not sure whether the use of .getParameterMetaData is a generally appropriate because the docs say:

For some queries and driver implementations, the data that would be returned by a ParameterMetaData object may not be available until the PreparedStatement has been executed.

Anyway it does work for Teradata. Maybe it could be an option?

It just occurred to me that it is probably inefficient to retrieve the parameter metadata on every call to set-parameters. It could be done in insert-rows instead. On the other hand, I didn't actually notice any performance hit when inserting 40,000 rows.


Sean Corfield made changes -
Field Original Value New Value
Status Open [ 1 ] Resolved [ 5 ]
Resolution Completed [ 1 ]
Sean Corfield made changes -
Resolution Completed [ 1 ]
Status Resolved [ 5 ] Reopened [ 4 ]
Gary Fredericks made changes -
Attachment JDBC-40.patch [ 12087 ]
Sean Corfield made changes -
Status Reopened [ 4 ] Resolved [ 5 ]
Resolution Completed [ 1 ]
Sean Corfield made changes -
Status Resolved [ 5 ] Closed [ 6 ]


Vote (0)
Watch (2)


  • Created: