Details
-
Type:
Enhancement
-
Status:
Reopened
-
Priority:
Major
-
Resolution: Unresolved
-
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
Fixed in 0.3.0-SNAPSHOT. May need to be revisited for performance tweaks later. Uncomfortable with MySQL being a special case here... 
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?