Author: dfabulich Date: Tue Feb 10 06:41:05 2009 New Revision: 742865 URL: http://svn.apache.org/viewvc?rev=742865&view=rev Log: [DBUTILS-14] [DBUTILS-31] [DBUTILS-39] [DBUTILS-41] [DBUTILS-44] setNull problems with Postgres and Oracle.
With this change, we now attempt to use ParameterMetaData.getParameterType to determine the SQL type of a null parameter. On Oracle, this will fail, so we'll try it once (at most), and record the failure in a volatile boolean so we won't try it again unnecessarily. Modified: commons/sandbox/dbutils/bugfixing/src/java/org/apache/commons/dbutils/QueryRunner.java Modified: commons/sandbox/dbutils/bugfixing/src/java/org/apache/commons/dbutils/QueryRunner.java URL: http://svn.apache.org/viewvc/commons/sandbox/dbutils/bugfixing/src/java/org/apache/commons/dbutils/QueryRunner.java?rev=742865&r1=742864&r2=742865&view=diff ============================================================================== --- commons/sandbox/dbutils/bugfixing/src/java/org/apache/commons/dbutils/QueryRunner.java (original) +++ commons/sandbox/dbutils/bugfixing/src/java/org/apache/commons/dbutils/QueryRunner.java Tue Feb 10 06:41:05 2009 @@ -17,6 +17,7 @@ package org.apache.commons.dbutils; import java.sql.Connection; +import java.sql.ParameterMetaData; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; @@ -35,6 +36,11 @@ public class QueryRunner { /** + * Is {...@link ParameterMetaData#getParameterType(int)} broken (have we tried it yet)? + */ + private volatile boolean pmdKnownBroken = false; + + /** * The DataSource to retrieve connections from. */ protected DataSource ds = null; @@ -47,7 +53,18 @@ } /** - * Constructor for QueryRunner. Methods that do not take a + * Constructor for QueryRunner, allows workaround for Oracle drivers + * @param pmdKnownBroken Oracle drivers don't support {...@link ParameterMetaData#getParameterType(int) }; + * if <code>pmdKnownBroken</code> is set to true, we won't even try it; if false, we'll try it, + * and if it breaks, we'll remember not to use it again. + */ + public QueryRunner(boolean pmdKnownBroken) { + super(); + this.pmdKnownBroken = pmdKnownBroken; + } + + /** + * Constructor for QueryRunner, allows workaround for Oracle drivers. Methods that do not take a * <code>Connection</code> parameter will retrieve connections from this * <code>DataSource</code>. * @@ -59,6 +76,22 @@ } /** + * Constructor for QueryRunner, allows workaround for Oracle drivers. Methods that do not take a + * <code>Connection</code> parameter will retrieve connections from this + * <code>DataSource</code>. + * + * @param ds The <code>DataSource</code> to retrieve connections from. + * @param pmdKnownBroken Oracle drivers don't support {...@link ParameterMetaData#getParameterType(int) }; + * if <code>pmdKnownBroken</code> is set to true, we won't even try it; if false, we'll try it, + * and if it breaks, we'll remember not to use it again. + */ + public QueryRunner(DataSource ds, boolean pmdKnownBroken) { + super(); + this.pmdKnownBroken = pmdKnownBroken; + setDataSource(ds); + } + + /** * Execute a batch of SQL INSERT, UPDATE, or DELETE queries. * * @param conn The Connection to use to run the query. The caller is @@ -130,7 +163,12 @@ if (params == null) { return; } - + + ParameterMetaData pmd = stmt.getParameterMetaData(); + if (pmd.getParameterCount() < params.length) { + throw new SQLException("Too many parameters: expected " + + pmd.getParameterCount() + ", was given " + params.length); + } for (int i = 0; i < params.length; i++) { if (params[i] != null) { stmt.setObject(i + 1, params[i]); @@ -138,7 +176,15 @@ // VARCHAR works with many drivers regardless // of the actual column type. Oddly, NULL and // OTHER don't work with Oracle's drivers. - stmt.setNull(i + 1, Types.VARCHAR); + int sqlType = Types.VARCHAR; + if (!pmdKnownBroken) { + try { + sqlType = pmd.getParameterType(i + 1); + } catch (SQLException e) { + pmdKnownBroken = true; + } + } + stmt.setNull(i + 1, sqlType); } } }