commons-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Hernan Silberman <hsilb...@pdi.com>
Subject [DBUtils] Oracle null/"Invalid Column Type"
Date Wed, 02 Jun 2004 23:54:44 GMT

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


Mime
View raw message