<< Back to previous view

[JDBC-40] insert nulls using .setNull (e.g. for Teradata) Created: 12/Oct/12  Updated: 07/Nov/13  Resolved: 03/Nov/13

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

Type: Enhancement Priority: Major
Reporter: Felix Andrews Assignee: Sean Corfield
Resolution: Completed Votes: 0
Labels: None
Environment:

Teradata 14


Attachments: Text File JDBC-40.patch    
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.



 Comments   
Comment by Sean Corfield [ 07/Apr/13 1:07 AM ]

This was a tricky one. Both .getParameterMetadataData and .getParameterType can throw exceptions. On MS SQL Server, if there is a syntax error in the SQL, the former throws an Exception. On MySQL, the latter throws an exception (every time as far as I can tell, even tho' .getParameterMetaData succeeds). That introduces quite a performance penalty for MySQL (about 10% in my tests).

However, if I treated MySQL as a special case and swallowed all exceptions and had a fallback to the original .setObject call, I could get everything to pass the test suite without a noticeable performance overhead (still measuring the latest MySQL performance as I write this).

Although it will probably slow everyone down a little, having to make these calls, it should be more robust and may make the .setObject calls faster in some situations?

Comment by Sean Corfield [ 07/Apr/13 1:24 AM ]

Fixed in 0.3.0-SNAPSHOT. May need to be revisited for performance tweaks later. Uncomfortable with MySQL being a special case here...

Comment by Sean Corfield [ 17/Apr/13 6:48 PM ]

This fix breaks PostgreSQL and already has MySQL as an exception so I'm going to roll it back and we'll have to find another way to support Teradata I'm afraid!).

Comment by Paudi Moriarty [ 22/Apr/13 4:26 AM ]

PostgreSQL also complains about use of setObject with a null object and no parameter type when using protocolVersion 3.

Seems to work ok with protocolVersion 2 though.

Comment by Felix Andrews [ 25/Jun/13 9:15 PM ]

How about some kind of option to activate the .setNull behaviour?
e.g. binding [*jdbc-set-null* true]?

Comment by Gary Fredericks [ 02/Aug/13 10:00 AM ]

Attached a patch that adds an ISQLParameter protocol that handles the .setObject call but can be overridden for custom types.

The most awkward part of this is that I had to implement the protocol for Object and nil (in order to cover everything), but the teradata case would require re-implementing for nil. As far as I can tell this would work fine, but overriding a protocol implementation feels sketchy.

I suppose an alternative would be to not provide any impls, and then check (satisfies? ISQLParameter x) and do the default behavior if not. Not sure if that would perform worse though.

Comment by Paudi Moriarty [ 02/Aug/13 10:09 AM ]

Beware that satisfies? is slow in the negative case. I'd avoid it here if possible.

Comment by Gary Fredericks [ 08/Oct/13 9:49 PM ]

Revisiting this since it keeps coming up at work.

I feel like the main downside to my patch's approach is that it precludes interacting with two different kinds of databases in the same process – if I want to connect to postgres in one function and have it write nulls one way, while writing nulls a different way to teradata, this isn't really possible.

The only other idea I have is passing a "setter" function as an option, which would do basically the same thing the protocol does in my patch.

Any ideas?

Comment by Sean Corfield [ 03/Nov/13 11:07 PM ]

Since this really is a per-db issue - and restricted to a handful of DBs (or fewer) - my sense is that we should do something with the db-spec itself to offer a way to override how set-parameters works. Having looked at the code, here's my proposal:

Wherever the code calls set-parameters in the context of a db-spec, it will check for a :set-parameters key in the db-spec and use that if present, else default to the normal set-parameters call.

This would allow you to add :set-parameters to your db-spec with a function that mimicked the built-in set-parameters except handled null values in a different way.

I'm going to make this change for 0.3.0-beta1 (coming real soon) and see how it works for you...

Comment by Sean Corfield [ 03/Nov/13 11:17 PM ]

Asserting "fixed" by virtue of allowing set-parameters to be overridden per-db-spec.

Comment by Felix Andrews [ 07/Nov/13 10:58 PM ]

Good solution. Many thanks!

Generated at Tue Sep 02 02:20:30 CDT 2014 using JIRA 4.4#649-r158309.