commons-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Hernan Silberman <hsilb...@pdi.com>
Subject Re: [DBUtils] Oracle null/"Invalid Column Type"
Date Thu, 03 Jun 2004 20:27:42 GMT

>From Oracle:

"From an internal bug it does not appear that we have implemented Types.OTHER.
Apparently Types.OTHER is very loosely defined in the JDBC Spec and Oracle has
just not implemented it."

I've been studying the JDBC spec to understand what I can expect a compliant 
driver to do with setNull(idx,Types.OTHER) and I haven't come across anything 
yet that I would consider a mandate.  I'll post again if I do, but for now, it 
seems like we should beware when using DBUtils (and JDBC in general) Oracle's 
drivers.

hernan


On Wed, 2 Jun 2004, David Graham wrote:

> 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


-- 
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


Mime
View raw message