Mailing-List: contact commons-dev-help@jakarta.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Jakarta Commons Developers List" <commons-dev@jakarta.apache.org>
Message-ID: <20040603005831.74228.qmail@web50607.mail.yahoo.com>
Date: Wed, 2 Jun 2004 17:58:31 -0700 (PDT)
From: David Graham <grahamdavid1980@yahoo.com>
Subject: Re: [DBUtils] Oracle null/"Invalid Column Type"
To: Jakarta Commons Developers List <commons-dev@jakarta.apache.org>
In-Reply-To: <Pine.LNX.4.44.0406021626210.11351-100000@tear.pdi.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

I recently ran into this problem as well.  Using Types.OTHER and
Types.NULL don't work but using Types.INTEGER, Types.NUMERIC, etc. do
regardless of what the actual column type is.  A while back I tested this
with Postgres and OTHER did work so I'm not sure if it's an Oracle bug or
just an unclear part of the spec.

For now, you can just override the QueryRunner.fillStatement() method to
workaround the problem.

David


--- Hernan Silberman <hsilberm@pdi.com> wrote:
> 
> I've run into a JDBC issue that I haven't found good help on elsewhere
> so I 
> thought I'd bring it up.  I'm using Oracle driver v9.2.0.
> 
> QueryRunner's fillStatement deals with Java null parameters by doing a 
> setNull(idx,Types.OTHER):
> 
> protected void fillStatement(PreparedStatement stmt, Object[] params)
>     throws SQLException {
> 
>     if (params == null) {
>         return;
>     }
> 
>     for (int i = 0; i < params.length; i++) {
>         if (params[i] != null) {
>             stmt.setObject(i + 1, params[i]);
>         } else {
>             stmt.setNull(i + 1, Types.OTHER);
>         }
>     }
> }
> 
> Unfortunately, when the statement gets executed with a null parameter,
> an 
> exception is thrown in Oracle's JDBC driver which complains with the
> exception 
> message "Invalid column type".  I've written a few tests using JDBC and
> have 
> discovered that using something other than Types.OTHER (like
> Types.STRING, 
> Types.FLOAT, etc) in the setNull call fixes the problem and unconfuses
> Oracle's 
> type mapper.
> 
> I'm not familiar enough with the JDBC spec to know whether 
> setNull(idx,Types.OTHER) is guaranteed to work everywhere and whether or
> not I'm 
> dealing with a bug Oracle's driver.  Seems like a popular RDBMS and I'm 
> wondering if any of you have run into this before and have worked around
> it.
> 
> I've found the following reference, though it's not exactly the case I'm
> running 
> into doesn't provide much help.  I see this issue regardless of
> column/java 
> datatype:
> 
> http://forums.oracle.com/forums/thread.jsp?forum=99&thread=195783
> 
> Here's a stack trace:
> 
> java.sql.SQLException: Invalid column type
> at org.apache.commons.dbutils.QueryRunner.rethrow(QueryRunner.java:330)
> at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:399)
> at org.apache.commons.dbutils.QueryRunner.update(QueryRunner.java:451)
> at
>
com.dw.nile.services.sql.impl.SQLQueryServiceImpl.performUpdate(SQLQueryServiceImpl.java:262)
> at
>
com.dw.nile.services.sql.rmi.RemoteSQLServiceImpl.performUpdate(RemoteSQLServiceImpl.java:193)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
>
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
> at
>
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
> at java.lang.reflect.Method.invoke(Method.java:324)
> at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:261)
> at sun.rmi.transport.Transport$1.run(Transport.java:148)
> at java.security.AccessController.doPrivileged(Native Method)
> at sun.rmi.transport.Transport.serviceCall(Transport.java:144)
> at
> sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:460)
> at
>
sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:701)
> at java.lang.Thread.run(Thread.java:534)
> 
> Any guidance is greatly appreciated.
> 
> thanks!
> Hernan
> 
> -- 
> Hernan Silberman
> PDI/Dreamworks
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: commons-dev-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: commons-dev-help@jakarta.apache.org
> 


__________________________________
Do you Yahoo!?
Friends.  Fun.  Try the all-new Yahoo! Messenger.
http://messenger.yahoo.com/ 

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