commons-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dan Fabulich <...@fabulich.com>
Subject [dbutils] QueryRunner.fillStatement and the null problem (long)
Date Mon, 09 Feb 2009 17:15:44 GMT

This email is long, so here's the executive summary:

Summary: The Oracle JDBC driver is broken in ways that will require some 
Oracle-specific workarounds.  I think I have a good workaround in mind, 
but I don't actually have an Oracle instance available to test.  Can 
somebody tell me if I'm doing something stupid here?

INTRO

QueryRunner has a nice helper method, "fillStatement(PreparedStatement 
stmt, Object[] params)".  It helps you turn calls like this:

   stmt.setString(1, "foo");
   stmt.setInteger(2, 42);
   stmt.setDate(3, myDate);

into calls like this:

   qr.fillStatement(stmt, new Object[] { "foo", new Integer(42), myDate});

In the new "java5" branch, we can use varargs and auto-boxing to make it 
even nicer:

   qr.fillStatement(stmt, "foo", 42, myDate);

Pretty cool, huh?  But it has a problem when you try to pass in a null 
parameter in the object array.

THE PROBLEM WITH NULLS (ORACLE SUCKS)

Under the hood, fillStatement actually does this:

   stmt.setObject(1, "foo");
   stmt.setObject(2, 42);
   stmt.setObject(3, myDate);

But this doesn't work on some databases, including Oracle, when using a 
null parameter.

The Sun documentation says:
http://java.sun.com/javase/6/docs/api/java/sql/PreparedStatement.html#setObject(int,%20java.lang.Object)
> Note: Not all databases allow for a non-typed Null to be sent to the 
> backend. For maximum portability, the setNull or the setObject(int 
> parameterIndex, Object x, int sqlType) method should be used instead of 
> setObject(int parameterIndex, Object x).

That is, you're supposed to pass in a sqlType integer, matching one of the 
defined constants in java.sql.Types.  Types.NULL is a legal constant, but 
it doesn't work with Oracle drivers.

dgraham tried to fix this, using code like this:
http://svn.apache.org/viewvc?view=rev&revision=141728

     for (int i = 0; i < params.length; i++) {
         if (params[i] != null) {
             stmt.setObject(i + 1, params[i]);
         } else {
             // 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);
         }
     }

It may well work with "many drivers," but unfortunately it doesn't work 
with PostgreSQL. :-( http://issues.apache.org/jira/browse/DBUTILS-39

Here's a few proposed solutions to this problem:
http://issues.apache.org/jira/browse/DBUTILS-14
http://issues.apache.org/jira/browse/DBUTILS-31
http://issues.apache.org/jira/browse/DBUTILS-41
http://issues.apache.org/jira/browse/DBUTILS-44


PARAMETER META DATA WON'T WORK (ORACLE SUCKS)

DBUTILS-31 suggests an ideal workaround for this problem.  In JDBC 3.0 
(Java 1.4) compliant drivers, you can just ask the PreparedStament what 
the SQL type of any given column is, using the getParameterMetaData() 
statement, like this:

     for (int i = 0; i < params.length; i++) {
         if (params[i] != null) {
             stmt.setObject(i + 1, params[i]);
         } else {
             int sqlType = stmt.getParameterMetaData().getParameterType(i+1);
             stmt.setNull(i + 1, sqlType);
         }
     }

This looks like a pretty good solution to me!  Easy as pie!  But 
unfortunately, it won't work on Oracle!
http://forums.oracle.com/forums/thread.jspa?threadID=585880

You have to register to read that link (it's free but it's a hassle). But 
the most recent post is confirming that it's still broken in Oct 2008. If 
you call getParameterType, you get error ORA-17023 "Unsupported feature." 
Lame!

MY PROPOSED SOLUTION

Francis Townsend replied to DBUTILS-31, suggesting:
> Unfortunately, it does not look as if there is no method that can 
> determine this before calling the getParameterType method. Which means 
> we would need to catch the exception and role back to the previous code, 
> namely use the VARCHAR type when setting null. This would always be 
> thrown by the Oracle driver, severely slowing it down.

That would maybe look something like this:

     for (int i = 0; i < params.length; i++) {
         if (params[i] != null) {
             stmt.setObject(i + 1, params[i]);
         } else {
             int sqlType = Types.VARCHAR;
             try {
                 sqlType = stmt.getParameterMetaData().getParameterType(i+1);
             } catch (Exception e) {}
             stmt.setNull(i + 1, sqlType);
         }
     }

... but that would indeed severely impact Oracle performance.

I think the fix in that case would be to cache the result:

     int sqlType = Types.VARCHAR;
     if (!pmdKnownBroken) {
         try {
             sqlType = stmt.getParameterMetaData().getParameterType(i+1);
         } catch (Exception e) { pmdKnownBroken = true; }
     }
     stmt.setNull(i + 1, sqlType);

Possibly we'd add a new argument to the QueryRunner constructor, allowing 
you to set pmdKnownBroken to true right away.

OPEN QUESTIONS

1) Will caching this result work?  Will it impact thread-safety in some 
negative way?

2) How do we distinguish between getParameterType failing due to an 
out-of-bounds index, vs. failing due to a broken JDBC driver?  Normally we 
could use ParameterMetaData.getParameterCount() ... does that work on 
Oracle?  I don't have an instance available to test.

Thanks for reading all this!  If anyone there has a convenient Oracle 
instance, I'd appreciate help with these questions.

-Dan

---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@commons.apache.org
For additional commands, e-mail: dev-help@commons.apache.org


Mime
View raw message