java.jdbc

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

Details

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

Description

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)]
    (dorun
     (map-indexed
      (fn [ix value]
        (let [typ (.getParameterType pmd (inc ix))]
          (if (nil? value)
            (.setNull stmt (inc ix) typ)
            (.setObject stmt (inc ix) value typ))))
      params))))

I am not sure whether the use of .getParameterMetaData is a generally appropriate because the docs http://docs.oracle.com/javase/7/docs/api/java/sql/ParameterMetaData.html 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.

Activity

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

People

Vote (0)
Watch (2)

Dates

  • Created:
    Updated:
    Resolved: