commons-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Henri Yandell <bay...@generationjava.com>
Subject Re: [DBUtils] Oracle null/"Invalid Column Type"
Date Thu, 03 Jun 2004 01:11:46 GMT

I'm no JDBC expert, but I regularly seem to hit this with Oracle JDBC
drivers. Things that don't live up to the intention of the spec.

How about having a test-suite for DBUtils which allows a user to configure
a database and run against it? This would not be the Unit Tests, but an
additional suite that we could all go off and run against the DBs at our
disposal and figure out who is working where.

Same idea would probably be of use to DBCP and SQL too.

Hen

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


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